Краткие теоретические сведения

Списком называется таблица, содержащая однородные записи.

Сортировка записей строк таблицы повышает скорость поиска информации и улучшает вид таблицы. Сортировка выполняется в два этапа: сначала выделяется сортируемая часть таблицы, затем выполняется обращение к меню Данные/Сортировка.... Для списков на первом этапе достаточно активизировать любую ячейку внутри списка. В открывшемся окне Сортировка диапазона следует указать ключи для сортировки в полях "Сортировать по...". Чаще всего применяется сортировка списков по одному ключу. Сортировка по двум или трем ключам одновременно требуется в тех случаях, когда данные, используемые в качестве первичного ключа имеют много повторяющихся значений.

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

Фильтр - это средство для отбора записей из списка по некоторому критерию. В Excel имеются два типа фильтров: фильтр и расширенный фильтр. Фильтр показывает записи, совпадающие с условиями фильтрации, и скрывает не совпадающие. Расширенный фильтр способен сформировать новую таблицу из отфильтрованных записей. Для применения фильтра необходимо выделить любую ячейку внутри списка и выполнить Данные--Фильтр - в заголовке таблицы должны появиться кнопки для раскрытия списков. Нажатие любой кнопки приводит к раскрытию списка элементов соответствующего столбца таблицы. Записи, в которых элементы столбца не совпадают с условием будут скрыты. Задание второго условия в другом заголовке приведет к дополнительной фильтрации записей и т.д.

Для применения расширенного фильтра требуется предварительная подготовка, состоящая из двух этапов:

ü подготовка таблицы (диапазона) условий;

ü планирование места для размещения результатов фильтрации.

Таблица условий состоит из строки заголовков и строк с условиями. Заголовки лучше формировать копированием из основной таблицы. Под заголовками размещаются условия, причем если несколько условий расположены в одной строке, они считаются связанными между собой логической операцией И, если в разных - ИЛИ. Если формируемый список должен содержать лишь отдельные столбцы исходного списка, заголовки этих столбцов следует скопировать в первую строку формируемого списка.

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

Сводные таблицы

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

Базами данных для сводных таблиц могут быть списки, таблицы, расположенные на рабочих листах Excel, либо внешние источники данных (например, базы данных Access).

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

Поле – это некоторая совокупность данных, собранных по одному признаку.

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

выбирать различные параметры вычислений.

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

Консолидация данных

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

ü создать таблицы одинаковой структуры на разных рабочих листах;

ü ввести данные в каждую таблицу за разные периоды;

ü объединить данные в итоговой таблице путем консолидации.

В Microsoft Excel существует два метода консолидации данных:

ü по расположению, если сводимые области расположены идентично;

ü по категориям, если сводимые области отличаются по расположению.

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

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

Задание 1 Сортировка списка.

1. Создать файл в MS Excel. Присвойте имя файлу ПР №4.

2. Переименовать Лист 1 в Сортировка. Создать таблицу вида:

3. Рассчитать столбцы Количество остатка и Сумма остатка.

4. Установите курсор внутри таблицы данных и выполнть команду меню Данные - Сортировка.

Выбрать первый ключ сортировки: в раскрывающемся списке " сортировать по " выбрать " Отдел " порядок " от А до Я ". Если же хотите, чтобы внутри отдела товары расположились по алфавиту, то выбрать второй ключ сортировки Добавить уровень в раскрывающемся списке " Затем " выбрать " Наименование товара " и установить переключатель в положение " от А до Я ".

Задание 2 Применение фильтра.

1.Скопировать исходную таблицу с Листа 1 на Лист 2, переименовать Лист 2 в Фильтр

2. Установить курсор на заголовок таблицы и выполнить команду меню Данные - Фильтр.

3.Используя фильтр установить- все записи с ненулевым количеством остатка.

4.Вставьть дату в ячейки A1:С1, которая будет автоматически меняться в соответствии с системным временем компьютера, для этого выбрать команду Формулы -- Дата и время - Сегодня.

5. Скопировать исходную таблицу с Листа 1 на Лист 2 ниже первой, например в строку 20.

6. Используя фильтр установить- все записи с Количеством остатка > 0.

7.Скрыть все столбцы, кроме Наименование товара и Количество остатка, для этого выделить столбец (скрываемый) и используя контекстное меню применить команду Скрыть.

Задание 3 Применение расширенного фильтра.

1. Перейти на Лист 3. Переименовать Лист 3 в Расширенный фильтр, скопировать исходную таблицу с Листа 1 на Лист 3.

2. Создать интервал критериев. Для этого скопировать подписи столбцов (диапазон A2:I2) в диапазон ячеек A 20: I 20.

3. Отобрать информацию о книгах, которые закуплены или в 1 -ом квар­тале или имеют тематику Экономика. Для этого:

в ячейку А21 занести значение 1 кв, в ячейку В22 занести значе­ние Экономика (т.е. при расположении условий отбора на разных строчках формируется их связь по правилу ИЛИ)

сделать текущей любую ячейку в области исходной таблицы и выполнить команду Данные — Дополнительно, в диалоговом окне Расширенный фильтр поле Исходный диа­пазон будет уже заполнено, в поле Диапазон условий либо набрать вручную (ввод осуществ­ляется с использованием абсолютных адресов ячеек), либо выделить с помощью мыши диапазон A 20: I 20 (диапазон ячеек, в кото­ром заданы условия отбора). В результате будут отобраны записи, относящиеся к первому кварталу или у которых тематика Экономика.

4. Отменить действие фильтра.

5. Отобрать информацию о книгах, которые закуплены в 1 -м квартале и имеют тематику Экономика. Для этого переместить значение Экономика из ячейки В22 в ячейку В21 (т.е. при расположении условий отбора на одной строчке формируется их связь по правилу И). Сделать текущей любую ячейку в области исходной таблицы и выполнить команду Данные — Дополнительно, выбрать поле Диапазон условий, очистить его и ввести в него диапазон $ A $20:$ I $22 (диапазон ячеек, в котором заданы усло­вия отбора).

6. Отобрать информацию о книгах, цена расхода которых больше или равна 130, но меньше 180 рублей.

7. Отобрать информацию о книгах, тематика которых начинается с бу­квы К.

8. Отобрать информацию о книгах, которые закуплены в 1, 2 и 3-м квар­талах.

9. Отобрать информацию о книгах, которые закуплены в 1 -м квартале по тематике Экономика, а во 2-м квартале по тематике Компьютеры.

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

1. Скопировать исходную таблицу с Листа 1 на Лист 4, переименовать Лист 4 в Сводная таблица.

2.Необходимо получить на листе не используя формулы Сумму количества прихода, Сумму количества расхода и сумму остатка. Для этого установить курсор в пределах таблицы и вставить сводную таб­лицу вкладка Вставить –Сводная таблица. Откроется новый лист с допол­нительным окном (Список полей сводной таблицы), в нем необходимо пометить поля: Наименование товара (в строки), Кол-во прихода, Кол-во расхода и Сумма остатка (в столбцы). Должна появиться следующая таблица вида:

Задание 5 Консолидация данных.

1. Переименовать Лист 5 в 1 квартал, Лист 6 во 2 квартал, Лист 7 в 3 квартал, Лист 8 в 4 квартал.

2. Скопировать исходную таблицу с Листа 1 на лист 1 квартал и удалить часть таблицы, оставив данные только 1 кв.

3. Скопировать исходную таблицу с Листа 1 на лист 2 квартал и удалить часть таблицы, оставив данные только 2 кв.

4. Скопировать исходную таблицу с Листа 1 на лист 3 квартал и удалить часть таблицы, оставив данные только 3 кв.

5. Скопировать исходную таблицу с Листа 1 на лист 4 квартал и удалить часть таблицы, оставив данные только 4 кв.

6. Перейти на Лист 9, переименовать Лист 9 в Консолидация. Выбрать команду Данные—Консолидация, в окне нажать , последовательно выбрать таблицы с каждого листа, нажимая кнопку Добавить,  установить ссылки на таблицы, установит все флаги и нажать кнопку ОК

Результат выполнения Консолидации.