Проверка данных при вводе в таблицу

Ввод данных в ячейку
из раскрывающегося списка

Проверка содержимого ячеек
при вводе данных с клавиатуры

Поиск ячеек со встроенной проверкой значений

Отмена проверки значения ячейки

Автоматическая обработка данных в Excel

Подведение промежуточных итогов
Операция используется для автоматического подведения итогов в таблице, представленной в виде списка.
Дополнительно при выполнении операции Excel структурирует список, что позволяет отображать и скрывать строки каждого промежуточного итога.
Перед выполнением операции необходимо отсортировать список по данным столбца, для которого нужно подвести итоги. После этого можно подсчитать промежуточные итоги любого столбца, содержащего числовые значения.

Исходная таблица

Вставка промежуточных итогов

Таблица после подведения промежуточных итогов

Итоговые таблицы после редактирования

Удаление итогов
При удалении итогов также удаляется структура таблицы и все разрывы страниц, которые были вставлены в список при подведении итогов.

Консолидация данных
При консолидации данных объединяются значения из нескольких диапазонов данных.
Операция по результатам аналогична операции подведения промежуточных итогов, но в этом случае итоги могут вычисляются для данных из несмежных диапазонов, расположенных в разных областях таблицы или нескольких таблиц.
Консолидируемые данные могут находиться на одном рабочем листе, на разных листах или в разных книгах.

В Excel существует два способа консолидации:
1. Консолидация по расположению данных
Консолидацию по расположению следует использовать в случае, если данные всех исходных областей имеют одинаковую организацию данных и размещены в одинаковом порядке. Названия заголовков консолидируемых диапазонов могут не совпадать.
2. Консолидация по категориям данных
Консолидацию по категории следует использовать в случае, если требуется обобщить набор данных, имеющих одинаковые заголовки строк и столбцов, но различную организацию данных.

Выполнение консолидации
Требования к исходным данным:
Диапазоны данных должны быть представлены в виде списка.
Если консолидация выполняется по расположению, необходимо, чтобы макеты всех диапазонов совпадали.
Если консолидация выполняется по категориям, нужно, чтобы подписи столбцов или строк, которые требуется объединить, совпадали с учетом регистра букв.

Консолидация данных по расположению

Итоги консолидации после редактирования

Консолидация данных по категориям

Таблицы консолидации после редактирования

Алгоритм консолидации

Предварительно можно выделить левую верхнюю ячейку области назначения (область назначения – это диапазон, в котором будут размещены консолидированные данные).
В поле Ссылка сначала указывается первый диапазон данных для консолидации и нажимается кнопку Добавить. Необходимо повторить этот шаг для всех диапазонов.
Если консолидация выполняется по расположению, оставьте все поля в группе Использовать в качестве имен пустыми. В Excel подписи исходных строк и столбцов не копируются в консолидированные данные. Если требуется скопировать подписи в консолидированные данные, сделайте это вручную.
Если консолидация выполняется по категории, в группе Использовать в качестве имен установите флажки, соответствующие расположению подписей в исходных диапазонах: в верхней строке, в левом столбце или в верхней строке и в левом столбце одновременно. Все подписи, не совпадающие с подписями в других исходных областях, в консолидированных данных будут расположены в отдельных строках или столбцах.
Рекомендации по выполнению операции консолидации

Изменение итоговой таблицы консолидации данных
Консолидацию изменить невозможно, если был установлен флажок Создавать связи с исходными данными. Если флажок установлен, нажмите кнопку Закрыть и создайте новую консолидацию.
Для добавления диапазона данных в консолидируемые данные
В поле Ссылка выберите диапазон.
Нажмите кнопку Добавить.
Для изменения размера или формы диапазона данных
В списке Список диапазонов щелкните диапазон данных, который требуется изменить.
Внесите изменения в выбранную область в поле Ссылка .
Нажмите кнопку Добавить.
Для удаления диапазона данных и консолидации
В списке Список диапазонов щелкните диапазон данных, который требуется изменить.
Нажмите кнопку Удалить.
Для автоматического обновления консолидации
Примечание. После автоматического обновления консолидации невозможно добавить, изменить или удалить диапазон данных.
Установите флажок Создавать связи с исходными данными.
Чтобы сохранить изменения, нажмите кнопку OK.

Работа со сводными таблицами
Сводные таблицы предназначены для анализа итоговых данных из нескольких источников.
Сводная таблица представляет собой интерактивную таблицу, с помощью которой можно быстро объединять и сравнивать большие объемы данных. Можно менять местами строки и столбцы для получения различных итогов по исходным данным, а также показывать подробные данные по нужным областям.
Данные для сводной таблицы можно получать из диапазонов, списков и баз данных Excel, а также можно использовать записи баз данных из внешних источников. Можно построить сводную таблицу на основе существующей сводной таблицы.
При создании сводной таблицы необходимо построить ее макет. В общем случае макет состоит из четырех полей:
поля данных;
поля строк;
поля столбцов;
поля страниц.
В частном случае минимальное количество полей сводной таблицы – два. Обязательным является поле данных и одно из двух полей - поле строк или поле столбцов.

Поля сводной таблицы
Данные, вносимые в поля строк и столбцов, используются в качестве соответствующих заголовков сводной таблицы.
Поля страницы отображаются таблице в виде раскрывающихся списков. Они используются для фильтрации данных, т.к. позволяют отображать данные как по значениям отдельных элементов, так и по всем элементам списка. Постраничная организация является главной особенностью сводной таблицы. Каждая страница представляет собой отдельную таблицу, соответствующую отдельному элементу списка. При печати сводной таблицы, содержащей поле страниц, данные по каждому элементу списка печатаются на отдельном листе.
Поля данных содержат значения данных для подведения итогов. Обычно поля данных содержат определенные числовые значения, которые обрабатываются с помощью функций.

Создание сводной таблицы
Сводные таблицы в Excel создаются с помощью мастера сводных таблиц и диаграмм. Для создания таблицы необходимо выполнить несколько шагов мастера, каждому из которых соответствует свое диалоговое окно.
Мастер позволяет выбрать исходные данные на листе или во внешней базе данных. Затем он создает на листе область отчета и предлагает список доступных полей. При перетаскивании полей из окна списка в структурированные области выполняются подведение итогов и автоматические вычисления. На основе созданной структуры строится отчет.
После создания отчета сводной таблицы можно изменить его разметку, формат, а также развернуть его для отображения большего объема исходных данных.
После построения сводной таблицы возникает статическая связь с исходными данными. Для обновления данных в сводной таблице необходимо щелкнуть по кнопке Обновить данные на панели инструментов Сводные таблицы.

Создание отчета сводной таблицы

Создание отчета вручную

Из окна Список полей сводной таблицы перетащите поля с данными, которые требуется отобразить в строках, в область перетаскивания с надписью «Перетащите сюда поля строк». Если список полей не отображается, нажмите кнопку Отобразить список полей на панели инструментов Сводная таблица.
Поля с данными, которые должны отображаться в столбцах, перетащите в область перетаскивания с надписью «Перетащите сюда поля столбцов».
Поля, которые требуется использовать как поля страниц, перетащите в область с надписью «Перетащите сюда поля страниц».

4) Поля, содержащие данные, которые требуется обобщить, перетащите в область с надписью «Перетащите сюда элементы данных». При добавлении нескольких полей данных их можно разместить в определенном пользователем порядке: щелкните правой кнопкой мыши поле данных, в контекстном меню выберите команду Порядок и используйте команды меню Порядок для перемещения полей.

5) Для изменения расположения полей перетащите их из одной области в другую. Для того чтобы удалить поле, перетащите его за пределы отчета сводной таблицы.
6) Для того чтобы скрыть границы области перетаскивания, щелкните ячейку за пределами отчета сводной таблицы.

Создание отчета с помощью мастера
На 3 шаге мастера нажмите кнопку Макет.
Из группы кнопок полей, расположенной справа, перетащите нужные поля в области диаграммы СТРОКА и СТОЛБЕЦ.
Поля, содержащие данные, которые требуется обобщить, перетащите в область ДАННЫЕ.
Поля, которые требуется использовать как поля страниц, перетащите в область СТРАНИЦА.
Для изменения расположения полей перетащите их из одной области в другую. Некоторые поля могут использоваться только в определенных областях. Поле не появится в области, в которой оно не может быть использовано.
Чтобы удалить поле, перетащите его за пределы диаграммы.

Сводная таблица

Фильтрация данных в сводной таблице с помощью поля страниц

Фильтрация данных в сводной таблице с помощью поля столбцов

Изменение макета отчета сводной
таблицы вручную
Если структура области размещения не отображена синим цветом, отобразите список полей, нажав кнопку Показать список полей на панели инструментов Сводная таблица .
Чтобы переместить поле с текущей позиции в строку, столбец или область данных, перетащите кнопку поля в новую позицию.
Чтобы создать поле страницы, перетащите его в область страницы в верхней части отчета. Если область страницы отсутствует (поля строк или столбцов начинаются на первой строке), используйте мастер, чтобы изменить макет и создать поля страницы.
Чтобы изменить порядок элементов внутри поля строки или поля столбца, выделите подпись элемента, а затем поместите указатель на границу ячейки. Когда указатель примет вид стрелки, перетащите элемент на новое место.
Чтобы изменить порядок полей данных, щелкните правой кнопкой мыши одно поле, в контекстном меню нажмите кнопку Порядок и используйте команды меню Порядок, чтобы перетащить поле. Можно также щелкнуть поле данных, а затем навести указатель на нижнюю границу ячейки. Когда указатель примет вид стрелки, перетащите поле данных в новую позицию.
Для добавления поля перетащите поле из окна Список полей сводной таблицы в область отчета, соответствующую типу поля, которое требуется создать.
Для удаления поля перетащите кнопку поля из отчета.

Изменение формулы для вычисления поля сводной таблицы
В контекстном меню вычисляемого поля выбрать команду Параметры поля.
Измените операцию в поле Операция.

Изменение макета отчета сводной таблицы с помощью мастера
Выберите команду Сводная таблица в меню Данные.
Нажмите кнопку Макет.
Чтобы изменить расположение полей, перетащите их из одной области в другую на схеме слева.
Чтобы добавить поля, перетащите их из списка полей справа на схему.
Некоторые поля можно использовать только в определенных областях. Если перетащить поле в область, где его нельзя использовать, оно не появится в ней.
Чтобы удалить поле, перетащите его за пределы диаграммы.
Чтобы изменить функцию вычисления, дважды щелкните по кнопке в поле данных.

Диаграммы для сводных таблиц

Фильтрация данных в диаграмме
с помощью поля страниц

Фильтрация данных в диаграмме
с помощью поля столбцов

Макросы
Макрос — это последовательность команд и функций, хранящаяся в модуле Visual Basic. Ее можно выполнять всякий раз, когда необходимо выполнить данную задачу.
Запись макросов. При записи макроса Microsoft Excel сохраняет информацию о каждом шаге выполнения последовательности команд. Последующий запуск макроса вызывает повторение («воспроизведение») команд. Visual Basic хранит каждый записанный макрос в отдельном модуле, присоединенном к книге.
Упрощение запуска макроса. Макрос можно запустить, выбрав его из списка в диалоговом окне Макрос. Также можно назначить макросу команду в меню, кнопку на панели инструментов, сочетание клавиш или графический объект на листе.

Создание макроса
Задание уровня безопасности

Если макрос нужен всегда при работе в Excel, при сохранении макроса запишите его в Личной книге макросов.

Если макрос требуется выполнять относительно позиции активной ячейки, запишите его, используя относительные ссылки на ячейки.

Окончание записи макроса

Создание пользовательской панели инструментов

Назначение кнопки на панели инструментов
для запуска макроса

Перетащить настраиваемую кнопку на панель инструментов

В контекстном меню кнопки задать параметры

Добавление нового пункта меню в строку меню

Добавление команды в меню для запуска макроса

Задание параметров для команды меню

Задание на лабораторную работу №4
Первый лист «Список»
1. Создать список фамилий студентов для ввода с помощью раскрывающегося списка
2. Создать список групп для ввода с помощью раскрывающегося списка
3. Создать список предметов для ввода с помощью раскрывающегося списка
Второй лист «Итоги»
1. Создать 2 таблицы одинаковой структуры (друг под другом).
2. Фамилии студентов, названия групп и предметов вводить с помощью раскрывающихся списков
3. При вводе рейтинга выполнить проверку введенных данных
4. В одной из таблиц подвести промежуточные итоги. При этом создать 4 команды в новом меню и 4 кнопки на новой панели инструментов:
- промежуточные итоги по группам (с предварительным выделением и сортировкой);
- промежуточные итоги по студентам (с предварительным выделением и сортировкой);
- промежуточные итоги по предметам (с предварительным выделением и сортировкой);
- удаление промежуточных итогов из таблицы.

Третий лист «Консолидация» (перед консолидацией удалить промежуточные итоги!)
1. Выполнить консолидацию данных по расположению (консолидировать данные из двух таблиц).
2. Выполнить консолидацию данных по категориям (3 варианта).
3. Полученные таблицы отредактировать.
Четвертый лист «Сводные таблицы»
1. Создать сводные таблицы:
- средний рейтинг по предмету;
- средний рейтинг по группе;
- средний рейтинг по студентам;
- минимальный рейтинг по предметам и группам;
- максимальный рейтинг по предметам и студентам;
- средний рейтинг по группам, предметам и студентам.
2. Для сводных таблиц построить сводные диаграммы.
Дополнительный балл – работа со сценариями в Excel.