Автоматическое вычисление общих и промежуточных итогов.
Лабораторная работа №4.
Тема: Логические операции в Excel, построение графиков и диаграмм. Средства Excel для работы с данными списка
Средства Excel для работы с данными списка (работа с ЭТ как с базой данных).
ЭТ все строки которой содержат однородную информацию рассматривается как список или база данных. При этом: каждая строка списка рассматривается как запись БД, столбцы списков считаются полями БД, заголовки столбцов считаются именами полей БД.
Чтобы ЭТ можно было обрабатывать специальными функциями для работы со списком, данные в таблице должны быть организованы следующим образом:
· во всех строках в одинаковых столбцах должны находиться однотипные данные;
· заголовки столбцов должны находиться в первой строке списка;
· названия строк должны находиться в левом столбце списка;
· в таблице не ложно быть пустых строк и столбцов – первая пустая строка считается концом списка.
К данным организованным в виде списка (БД) можно применять специальные функции и выполнять следующие операции: добавлять, изменять и удалять записи; находить и сортировать записи; осуществлять фильтрацию и выборку данных; подводить общие и промежуточные итоги и т.д.
Сортировка данных.
Для некоторых операций автоматической обработки данных бывает необходимо расположить их в строго определенной последовательности – выполнить сортировку исходных данных.
|
|
|
Сортировать можно как строки, так столбцы. Строки можно сортировать по значениям ячеек одного столбца или нескольких столбцов. Сортировать можно как числовые так и текстовые данные. Если в сортируемом столбце содержатся и текст и числовые данные, то после сортировки числовые данные будут располагаться перед текстом.
Сортировка производится с помощью команд меню Данные→Сортировка или с помощью кнопок панели инструментов Стандартная. В диалоговом окне Сортировка диапазона указывают требуемые параметры – тип и порядок сортировки (по возрастанию или убыванию значений одного, двух или трех столбцов – ключей сортировки).
По умолчанию список сортируется по строкам. Режим сортировки по столбцам задается в диалоговом окне Параметры сортировки, которое открывается при нажатии кнопки Параметры. В этом же окне можно задать собственный (пользовательский) порядок сортировки и установить переключатель регистра.
Фильтрация (выборка) данных.
Фильтрация данных позволяет отображать только те строки, содержимое ячеек которых отвечает заданному условию или нескольким условиям. С помощью фильтра можно в удобной форме выводить или скрывать записи списка. Отобранные записи можно форматировать или удалять. Копировать, использовать для дальнейших вычислений или построения диаграмм.
|
|
|
Фильтрация может выполняться 2-мя способами: с помощью автофильтра или расширенного фильтра.
Для выполнения автофильтра нужно: установить курсор внутри таблицы, выполнить команду Данные→Фильтр→Автофильтр; щелчком мыши по кнопке со стрелкой раскрыть список столбца по которому будет производиться выборка; указать требуемые значения или выбрать строку «условие» и задать критерии выборки в диалоговом окне Пользовательский автофильтр. Условия для отбора записей в определенном столбце могут состоять из 2-х самостоятельных частей, соединенных логической связкой И/ИЛИ. Каждая часть условия может включать:
· значение, которое может выбираться из списка или шаблонные символы подстановки (*,?);
· оператор сравнения.
Для восстановления всех строк исходной таблицы нужно щелкнуть по кнопке со стрелкой и выбрать пункт «все» или выполнить команду Данные→Фильтр→Отобразить все. Для отмены режима фильтрации выполнить команду Данные→Фильтр→Автофильтр (убрать переключатель).
Расширенный фильтр позволяет формировать множественные критерии выборки и осуществлять более сложную фильтрацию данных в ЭТ с заданием набора условий отбора по нескольким столбцам. Для фильтрации записей списка расширенный фильтр обеспечивает использование 2-х типов критериев: критериев сравнения и вычисляемы критериев.
|
|
|
До выполнения команды фильтрации необходимо сформировать специальную область для задания условий фильтрации данных – диапазон условий отбора (интервал критериев). Диапазон должен содержать строку с заголовками столбцов (обычно ее копируют из исходной таблицы в другое место) и несколько строк для здания условий отбора. Между значениями условий отбора и таблицей должна находиться как минимум одна пустая строка.
Если критерии отбора вводятся в одной строке для разных столбцов, то они считаются связанными условием «И», если в разных строках, то условием «ИЛИ». Критерии сравнения могут содержать: точные значения, шаблон значения, значения, формируемые с помощью операторов сравнения.
После формирования диапазона условий, необходимо установить курсор внутри таблицы и выполнить команду Данные→Фильтр→Расширенный фильтр. В появившемся диалоговом окне указывают диапазон ячеек таблицы и адрес или имя диапазона условий. Чтобы скопировать результат фильтрации в другую область, следует установить переключатель «Скопировать результат в другое место», перейти в поле «Поместить результат в диапазон» и указать верхнюю левую ячейку области вставки данных.
|
|
|
Автоматическое вычисление общих и промежуточных итогов.
Автоматическое подведение итогов – это удобный способ быстрого обобщения и анализа данных в ЭТ.
Перед вычислением промежуточных итогов следует выполнить сортировку по тем столбцам, по которым будут подводиться итоги, чтобы все записи с одинаковыми полями попали этих столбцов одну группу.
Для одной и той же группы данных можно одновременно вычислять промежуточные итоги с помощью нескольких функций, вычислять «вложенные» и многоуровневые итоги. Значения общих и промежуточных итогов автоматически пересчитываются при каждом изменении данных. При подведении итогов автоматически могут быть вычислены: сумма, количество значений, среднее, минимум, максимум, произведение, и др.
Для подведения итогов необходимо:
· Отсортировать список по столбцу, для которого необходимо вычислять итоги;
· Выделить какую-либо ячейку таблицы или требуемый диапазон;
· Выполнить команду Данные→Итоги;
· В диалоговом окне «Промежуточные итоги» из списка «При каждом изменении в» выбрать столбец, содержащий группы по которым необходимо подвести итоги;
· Из списка «Операция», выбрать функцию для подведения итогов;
· В списке «Добавить итоги по» выбрать столбцы, содержащие значения по которым необходимо подвести итоги.
Итоги могут выводится ниже исходных данных, либо выше, если в диалоговом окне снят переключатель «Итоги под данными».
Для удаления итогов нужно выделить ячейку, в таблице содержащей итоги, выбрать команду Данные→Итоги и щелкнуть по кнопке Убрать все.
Консолидация данных.
При консолидации данных над значениями, расположенными в различных областях, могут быть выполнены те же операции, что и при подведении итогов. Источники консолидируемых данных могут находиться на одном рабочем листе, нескольких рабочих листах, в разных рабочих книгах или даже в различных местах диска. Листы, содержащие области-источники, не обязательно должны быть открыты во время консолидации. Открытые области-источники перед консолидацией рекомендуется сохранять. Консолидированная информация может быть выведена на том же рабочем листе, другом или в другой книге.
· Имеется несколько способов консолидации данных:
· По расположению – для однотипных данных, упорядоченных одинаковым образом;
· По категориям – для однотипных данных, организованных в различных областях-источниках по-разному;
· Путем создания сводной таблицы – сходен с предыдущем, но обеспечивает большую гибкость и информативность;
· С помощью формул с использованием ссылок – этот способ не накладывает ни каких ограничений на расположение данных в исходных областях;
· С использованием мастера шаблонов с функцией автоматического сбора данных.
Консолидация по расположению применяется когда все столбцы имеют одни и те еже имена, размещены в том же порядке и имеют фиксированное расположение однотипных ячеек. Диапазонам ячеек, используемых для консолидации, рекомендуется присваивать имена. Для выполнения указанного способа консолидации выполнить команду Данные→Консолидация, в появившимся диалоговом окне выбрать функцию и указать области консолидируемых данных.
Дата добавления: 2022-01-22; просмотров: 88; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!