Подведение промежуточных итогов в смежных диапазонах.
Осуществляется через команду меню Данные/Итоги.
Порядок выполнения работы
1. Ввести список «Периферия», представленный ниже.
| Товар | Тип | Наименование | Цена | Кол- во | Сумма |
| Сканер | Листовой | Paragon Page Easy | |||
| Сканер | Планшетный | Paragon 1200 SP | |||
| Сканер | Планшетный | ScanExpress A3 P | |||
| Принтер | Струйный | Epson Stylus Photo 700 | |||
| Принтер | Лазерный | HP LaserJet 4000 | |||
| Принтер | Лазерный | HP LaserJet Color 8500 | |||
| Сканер | Листовой | Paragon Page 630 | |||
| Сканер | Планшетный | Paragon 800IIEP | |||
| Принтер | Матричный | Epson LX-1050+ | |||
| Принтер | Лазерный | HP LaserJet 5000 | |||
| Сканер | Планшетный | ScanExpress 6000 SP | |||
| Принтер | Струйный | Epson Stylus-1500 | |||
| Принтер | Матричный | Epson LQ-2170 | |||
| Принтер | Матричный | Epson LQ-100 | |||
| Принтер | Струйный | Epson Stylus-1520 |
2. Подсчитайте сумму для каждого товара по формуле : Цена*Кол-во.
3. Примените к списку следующие команды, выбранные в меню Данные: Форма, Сортировка, Фильтр, Итоги.
Ход выполнения работы:
1. В меню Данные выберите команду Форма. Определите назначение каждой кнопки в окне. Введите еще две записи, нажимая кнопку Добавить. Нажав кнопку Критерии, установите в поле Сумма значение >300, переместитесь по записям, нажимая кнопки Назад и Далее(Вперед). Закройте окноФормы.
2. Отсортируйте записи по Товару в порядке возрастания, по полю Тип в порядке возрастания командой Данные/Сортировка.
3. Используя автофильтр Данные /Фильтр/Автофильтр, отберите записи:
· По полю Тип: Лазерные принтеры. Для этого перейдите на поле Тип. Раскройте список фильтрации и выберите Лазерные. На экране остались только данные по лазерным принтерам. Отмените фильтр (Данные /Фильтр/Отобразить все).
· По полю Товар: Сканеры. Выполнить самостоятельно! Отмените использование фильтра.
· 
По полю Кол-во: больше 3. Раскройте список фильтрации и выберите Условие. Выберите из списка оператор отношения «больше». В соседнем поле задайте значение 3. ОК.
Отмените использование фильтра.
4. 
используя Расширенный фильтр Данные /Фильтр/Расширенный фильтрнайдите сканеры, которых больше 1. Для этого под таблицей запишем условия отбора Товар – Сканер, Кол – во > 1. Выполним команду Данные /Фильтр/Расширенный фильтр.Выполним обработку, как показано на рисунке ниже.
Результат:
5. Создайте промежуточные итоги командой Данные/Итоги.
5.1. Найдем суммы, затраченные отдельно на покупку всех принтеров и всех сканеров. Для этого:
1) Отсортируйте таблицу по товару.
2) В меню Данные выберите Итоги. Откроется диалоговое окно Промежуточные итоги.
3) Для того, чтобы подвести итоги по каждому товару (отдельно принтеры и отдельно сканеры), в списке При каждом изменении в выберите Товар
4) Убедитесь, что в окне Операциявыбрана Сумма
5) Для того, чтобы просуммировать показатели количества товара и сумм, затраченных на покупку, установите флажки в поле Добавить итоги по напротив строк Кол-во и Сумма.
6) Проверьте, что напротив строк Заменить текущие итоги и Итоги под данными установлены флажки и нажмите ОК.
7) Таким образом, вы получите итоговые значения количества и суммы для каждого типа товара.
8) Для удаления промежуточных итогов выберите команду Данные-Итоги и щелкните кнопку Убрать все. Таблица вернется в исходное состояние.
5.2. Найдите среднее значение цены отдельно для всех принтеров и всех сканеров, а также общее среднее значение цены. Для этого:
1) Отсортируйте таблицу по товару.
2) Данные è Итоги èПромежуточные итоги:
3) При каждом изменении è Товар
4) Операция èСреднее
5) Добавить итоги по èЦена
6) В конце не забудьте убрать промежуточные итоги.
5.3. Вычислите число разновидностей принтеров и сканеров. Для этого
1) Отсортируйте таблицу по товару.
2) Данные è Итоги
3) Промежуточные итоги:
4) При каждом изменении è Товар
5) Операция èКол-во значений
6) Добавить итоги по èНаименование
7) В конце не забудьте убрать промежуточные итоги.
5.4. Самостоятельно вычислите среднее значение сумм, потраченных на покупку всех принтеров и всех сканеров.
Покажите результат преподавателю!!!
В конце не забудьте убрать промежуточные итоги.
5.5. Вычислите суммы, потраченные на покупку каждого типа товара (каждой разновидности принтеров и сканеров).
1) Отсортируйте таблицу по типу принтеров.
2) Данные è Итоги
3) Промежуточные итоги:
4) При каждом изменении è Тип
5) Операция èСумма
6) Добавить итоги по èСумма
6. Часто при составлении итогового отчета нужно видеть только итоги, а остальные записи скрыть. Для этого в колонках с кнопками 1 2 3можно поменять знак - на +. Такое можно провести для каждого уровня.
Воспользуйтесь результатами предыдущего задания.
1) Скройте результаты второго уровня: Щелкните по кнопке 2.
2) Для возврата таблицы в исходный вид щелкните по кнопке того уровня, который вы хотите показать (кнопка 3 )
3) Попробуйте по очереди скрывать отдельные записи второго уровня (нажимайте на кнопку -)
4) Для возврата - на кнопку +.
5) Попробуйте поработать с кнопкой 1.
6) Верните таблицу в исходное состояние.
7) Уберите промежуточные итоги.
Контрольные вопросы
1. Как работать с формой?
2. Какие виды фильтров вы знаете? Как работает расширенный фильтр?
3. Для чего нужна операция Промежуточные итоги?
4. Что такое структура? Какими способами создается структура?
Лабораторная работа №6 (MSExcel)
Тема: Подведение итогов для данных, расположенных в несмежных диапазонах.
Цель: Научиться подводить итоги в несмежных диапазонах.
Теоретические сведения
Для подведения итогов данных, расположенных в несмежных диапазонах, например, на разных рабочих листах или в разных рабочих книгах используются команды Консолидация и Сводная таблица из меню Данные.
Задание
1. Введите следующие данные для создания сводной таблицы:
| Дата | Счет | Тип | Открыт | Отделение | Клиент |
| 01.09.99 | Текущий | Представитель | Центральное | Старый | |
| 01.09.99 | 15759,9 | Депозит | Кассир | Западное | Старый |
| 01.10.99 | 15367,60 | Депозит | Представитель | Северное | Старый |
| 03.10.99 | 12000,68 | Депозит | Представитель | Западное | Старый |
| 03.10.99 | 5000,00 | Срочный | Представитель | Центральное | Старый |
| 12.11.99 | 7000,00 | Текущий | Кассир | Северное | Новый |
| !3.11.99 | 90000,00 | Срочный | Представитель | Западное | Старый |
| 01.12.99 | 12000,00 | Текущий | Представитель | Северное | Старый |
| 01.12.99 | 10000,00 | Срочный | Представитель | Западное | Старый |
| 01.12.99 | 6900,00 | Депозит | Представитель | Центральное | Старый |
| 04.12.99 | 5500,00 | Срочный | Кассир | Северное | Старый |
| 04.12.99 | 3500,00 | Срочный | Представитель | Центральное | Старый |
Создайте сводные таблицы, отвечая на следующие вопросы:
· Какая общая сумма вклада для каждого из отделений и по каждому из типов счетов?
· Как распределена сумма по разным типам счетов?
· Какое отделение имеет больше Срочных счетов?
При использовании Мастера сводных таблиц (команда Данные> Сводная таблица) на первом шаге указывается источник данных. Для этой задачи нужно задать опцию «В списке или в базе данных Microsoft Excel».
На втором шаге Мастера определяется диапазон данных, данные из которого будут включены в сводную таблицу. В диапазоне данных не должно быть пустых строк или столбцов.
На третьем шаге Мастера определяется структура создаваемой таблицы. В центре окна расположена схема сводной таблицы. На схеме представлены следующие области:
· Строка. Значения поля используются в качестве заголовков строки в сводной таблице.
· Столбец. Значения поля используются в качестве заголовков столбцов в сводной таблице.
· Данные. Поле, для которого подводятся итоги в сводной таблице.
· Страница. Значения поля используются в качестве заголовков страниц в сводной таблице.
Поля базы данных (указанного диапазона ячеек) представлены в виде кнопок, которые расположены в столбце в правой части окна. В каждую из этих областей можно перетащить любое количество полей, все поля использовать необязательно. Неиспользованные поля не будут присутствовать в сводной таблице.
Например, установив указатель мыши на кнопку Счет, нажмите кнопку мыши и переместите в область Данные, затем переместите поле Тип в область Строка, поле Отделение в область Столбец.
Самостоятельно выберите размещение нужных полей на схеме. Если нужно использовать другую функцию, кроме суммирования, то нужно двойным щелчком мыши по области Данные вызвать список доступных функций и выбрать нужную.
На четвертом шаге Мастера установите параметры сводной таблицы, выделите опции Общие итоги по столбцам и Общие итоги по строкам.
Сохраните созданную таблицу на отдельном листе.
В созданной сводной таблице можно группировать определенные элементы поля. Например, в следующей задаче производится группировка дат по месяцам:
1. Сколько счетов обрабатывается по каждому из отделений за месяц?
В область Столбец поместите поле Дата,в область Строка поместите поле Отделение, в область Данные –поместите поле Счет. Для поля Счет выберите функцию Счет (Count). Затем, когда будет создана сводная таблица, сгруппируйте данные из поля Дата. Поместите указатель мыши на название поля Дата (или выделите ячейки, которые нужно сгруппировать), команда Данные/Группа и структура/Сгруппировать.В зависимости от типа выбранных данных для группировки появится соответствующее окно для выбора интервалов группировки. Например, для дат можно выбрать квартал, месяц и т.д. Выберите месяц.
2. Для работы с консолидируемыми данными:
2.1. Введите следующие данные, группируя рабочие листы .
Примечание. При необходимости добавьте в рабочую книгу рабочие листы, используя команду Вставка/ ЛистилиДобавить из контекстного меню Ярлычков (Установить указатель мыши на ярлычок рабочего листа и нажать правую кнопку мыши).
4.2. Переименуйте эти листы , дайте им соответствующие названия Магазин1, Магазин2, Магазин3, используя команду Переименовать из контекстного меню Ярлычков.
4.3.Затем объедините три листа в группу. Для этого нажмите клавишу Ctrl, и, удерживая ее нажатой, щелчком мыши выделите эти листы.
4.4.Затем введите данные. При вводе информации в группу, эта информация будет отображаться на всех листах группы. Затем разгруппируйте эти листы, выбрав в контекстном меню Ярлычков команду Разгруппировать. Немного измените данные на втором и третьем листах. Измените Код товара и Количество за любой месяц.
| Код товара | Янв. | Фев. | Мар. |
| А-145 | |||
| А-189 | |||
| А-195 | |||
| С-213 | |||
| С-415 | |||
| Е-10 | |||
| Е-400 | |||
| Е-456 | |||
| Е-790 |
3. Консолидируйте следующие данные, размещенные на трех рабочих листах с помощью команды Консолидация и с помощью команды Сводная таблица:
При использовании команды Данные>Сводная таблица на первом шаге Мастера сводных таблиц задайте опцию В нескольких диапазонах консолидации.
На втором шаге Мастера (2а) выберите опцию Создать одно поле страницы, на шаге (2b) последовательно задайте диапазоны данных, размещенные на трех рабочих листах.
Третий шаг Мастера оставьте по умолчанию, нажмите кнопку Далее, на четвертом оставьте изменения установки, сделанные по умолчанию.
При создании итоговой таблицы с помощью команды Данные >Консолидация перейдите на новый лист и выберите команду. В окне этой команды в списке Функция выберите нужную функцию, например, Сумм, затем поместите указатель мыши в поле Ссылка, затем щелкните мышью на ярлычке листа Магазин1 и выделите диапазон ячеек с данными для консолидации (выделяйте все данные , включая заголовки), вернитесь на лист , где создается консолидация, нажмите кнопку Добавить, затем укажите на данные листа Магазин2, нажмите кнопку Добавить, потом – на данные листа Магазин3 и кнопку Добавить. В окне команды Консолидация установите опции «Подписи верхней строки», «Значения левого столбца», «Создавать связи с исходными данными».
Сравнить полученные итоговые таблицы. Результаты предъявить преподавателю.
контрольные вопросы
1. Способы создания структуры данных.
2. Назначение сводной таблицы.
3. Определение консолидации.
4. Отличия функций "Сводная таблица" и "Консолидация".
Лабораторная работа №7 (MSExcel)
Тема: Исследование задач с помощью команды Таблица подстановки
Цель: Научиться использовать функции команды Таблица подстановки.