Оглавление
Организация межтабличных связей.
Создание и обработка таблицы «Расчёт
стоимости тура»
Создание и обработка таблицы «Расчёт первоначального взноса при покупке
автомобиля в кредит
Создание и обработка таблицы
«Плата за проживание в гостинице»
I.
Создать таблицу «Ведомость поступления материалов на склад»
|
Ведомость поступления материалов на склад |
||||||||
|
за февраль 2010 г. |
||||||||
|
№ п/п |
Дата поступления |
Наимено- вание |
Единица измерения |
Номер счета |
Кол. |
Цена, руб. |
Сумма |
|
|
1 |
03.02.03 |
электр.лампы |
шт. |
10-1 |
20 |
5 |
? |
|
|
2 |
03.02.03 |
веники |
шт. |
10-9 |
3 |
60 |
? |
|
|
3 |
05.02.03 |
чистящ.ср-ва |
шт. |
10-9 |
13 |
28 |
? |
|
|
4 |
07.02.03 |
электр.лампы
-500вт. |
шт. |
10-1 |
8 |
11 |
? |
|
|
5 |
07.02.03 |
изолента |
шт. |
10-9 |
6 |
7 |
? |
|
|
6 |
08.02.03 |
шайба
Ф50 |
шт. |
10-1 |
189 |
1 |
? |
|
|
7 |
08.02.03 |
шпильки
М24 |
шт. |
10-1 |
10 |
1,2 |
? |
|
|
8 |
10.02.03 |
выключатель |
шт. |
10-1 |
3 |
58 |
? |
|
|
9 |
11.02.03 |
рукавицы |
пар |
10-9 |
30 |
67 |
? |
|
|
|
ИТОГО |
|
|
|
|
|
? |
|
1. Выполните сортировку таблицы по полю Номер счета.
2. Получитепромежуточные итоги по счетам.
·
Просмотреть все уровни детализации списка
данных, щелкнув по символу строки уровня 3.
·
Просмотреть только промежуточные и общие
итоги.
·
Просмотреть только общие итоги.
3. Постройте диаграмму (например, деревянную или
конусовидную), отражающую итоговые данные по бухгалтерскому счету 10.
4. Защитите от изменений вычисляемые ячейки.
5. Сохраните рабочую книгу под именем Оборотная
Ведомость в своей папке в папке «Мои
документы».
6. Завершите работу с Excel.
II.
Создать таблицу
«Обмен валют»
Примечание. Серым цветом в таблице выделены ячейки,
значения которых необходимо рассчитать.
Обмен валют
|
№ |
Номер банка |
Адрес банка |
Устав-ной капитал (т. руб.) |
Курс покупки валюты |
Курс продажи валют |
Разница (продажа/ покупка) |
Отклонение от среднего
(покупка) |
Отклонение от среднего
(продажа) |
Число клиен-тов |
|
1 |
Банк 1 |
Москва |
3000000 |
23,5 |
25 |
|
|
|
500 |
|
2 |
Банк 2 |
Астрахань |
3500000 |
23 |
25,5 |
|
|
|
120 |
|
3 |
Банк 3 |
Москва |
3700000 |
23,6 |
24,9 |
|
|
|
600 |
|
4 |
Банк 4 |
Ярославль |
4000000 |
23,4 |
25,3 |
|
|
|
30 |
|
5 |
Банк 5 |
Ростов/на-Дону |
2900000 |
23,5 |
25 |
|
|
|
185 |
|
6 |
Банк 6 |
Ростов/на-Дону |
3100000 |
23,5 |
25,1 |
|
|
|
60 |
|
7 |
Банк 7 |
Минск |
4000000 |
23,7 |
25,3 |
|
|
|
100 |
|
8 |
Банк 8 |
Ростов/на-Дону |
4000000 |
23,7 |
25,2 |
|
|
|
500 |
|
|
|
|
|
|
|
|
|
|
|
|
Итого |
|
|
|
|
|
|
|
|
|
|
Среднее значение |
|
|
|
|
|
|
|
|
|
|
Максимальное значение |
|
|
|
|
|
|
|
|
|
|
Минимальное значение |
|
|
|
|
|
|
|
|
|
1. Постройте диаграммы, используя «Мастер
диаграмм» (не менее трёх).
2. Защитите от изменений вычисляемые ячейки.
3. Сохраните рабочую книгу под именем Обмен
валюты в своей папке в папке «Мои
документы».
4. Завершите работу с Excel.
ОБЪЕДИНЕНИЕ ДАННЫХ.
При работе с электронными
таблицами часто возникает необходимость их объединения. Среди инструментов
объединения электронных таблицотметим:
·
организацию межтабличных связей;
·
создание сводных таблиц;
·
консолидацию электронных таблиц или их частей.
Краткие теоретические сведения.
Связи между таблицами
осуществляются путем использования внешних ссылок (адресов ячеек), содержащих
помимо имени столбца и номера строки, еще и имя файла, данные из которого
используются.
При организации межтабличных
связей учитывают возможность комплектования связанных таблиц в одну рабочую
книгу. При этом таблица, в ячейках которой есть внешние ссылки на другие
таблицы, считается основной, а эти таблицы в свою очередь рассматриваются как
дополнительные. При загрузке основной таблицы необходимо также загрузить и все
связанные с ней дополнительные таблицы, иначе в ячейках основной таблицы,
имеющих внешние ссылки, появятся сообщения об ошибке или представленные вам
результаты окажутся неверными. Между
отдельными таблицами возможны и двусторонние связи (таблица A
ссылается на таблицу B, а B в свою очередь, прямо или
через таблицу C, ссылается на A).
Практическое задание.
На листе 1 книги Excelзанести данные об объёме
продаж по группам товаров для некоторого магазина игрушек.
На
листе 2 представить данные об издержках продаж по заданным группам товаров.
На
листе 3 необходимо получить данные о величине прибыли го группам товаров.
Работу
сопроводить диаграммами.
1. Переименуйте Лист1 в Объём продаж.
2. Создайте таблицу «Объёмы продаж».
Объёмы
продаж (руб.)
|
Группы товаров |
январь |
февраль |
март |
1
квартал |
апрель |
май |
июнь |
2
квартал |
1-е полугод. |
доля, % |
|
деревянные |
|
|
|
? |
|
|
|
? |
? |
? |
|
куклы |
|
|
|
? |
|
|
|
? |
? |
? |
|
игры |
|
|
|
? |
|
|
|
? |
? |
? |
|
конструкторы |
|
|
|
? |
|
|
|
? |
? |
? |
|
мягкие |
|
|
|
? |
|
|
|
? |
? |
? |
|
итого |
|
|
|
? |
|
|
|
? |
? |
100 |
Примечание. ? - ячейки, значения которых необходимо
рассчитать.
Значения объёмов продаж для отдельных месяцев представлены на рис.
6.
Рис.6
Объёмы продаж для отдельных
месяцев
3.
Представьте
в графическом виде
доли отдельных групп товаров в общем объеме продаж за 1-е полугодие:
На практике довольно часто таблицы в
рабочих листах одной книги имеют одинаковую структуру данных и в них выполняются
однотипные вычисления. В подобных случаях целесообразно вместо повторного
ввода данных скопировать весь лист.
При копировании листа в копии дублируются
все данные, параметры форматирования и формулы. Объясним выполнение операции
копирования листа на примере.
4. Щелкните на ярлычке листа Объем
продаж правой кнопкой мыши и выберите команду Переместить/скопировать.
5. На экране будет открыто окно диалога Переместить или скопировать. В поле Перед листом щелкните на элементе Лист2.
6. Установите
флажок параметра Создавать копию.
7. Закройте окно диалога, нажав кнопку ОК.
В рабочую книгу будет вставлен лист Объем продаж (2)
Этот лист
мы будем использовать для учета издержек по отдельным группам товаров.
8. Щелкните дважды на ярлычке листа Объем
продаж(2) для присвоения листу другого имени.
9. Введите
имя Издержки.
10. После
изменения имени листа замените также название таблицы на Издержки продаж
(руб.).
11. Значения
объема продаж не идентичны значениям издержек. Поэтому их следует удалить.
Формулы удалять не следует, поскольку будем использовать их и в дальнейшем.
12. Введите значения издержек (рис.7)
Рис. 7. Значения издержек
13. Представьте
в графическом виде доли отдельных групп товаров в общем объеме издержек
за 1-е полугодие:
14. Снова скопируйте рабочий лист Объем
продаж и присвойте копии имя Прибыль. Замените название таблицы на Прибыль продаж (руб.) Удалите
в листе Прибыль исходные
(входные) данные – формулы не удалять.
15. В этом листе данные вводить не нужно —
мы определим значения прибыли, вычтя из объема продаж издержки. Поместите
указатель ячейки на ячейке В4 (деревянные-январь)и введите знак
равенства.
16.
Перейдите на лист Объем продаж и щелкните на ячейке В4
(деревянные-январь).
17. Введите знак «минус»
и перейдите теперь в рабочий лист Издержки. Щелкните в нем также
на ячейке В4 (деревянные-январь).
18.
Нажмите клавишу [Enter]
В ячейке В4 рабочего листа Прибыль
будет отображен результат — 2466. В строке формул отображена
формула:
='Объем продаж'!В4-Издержки!В4
19. Для определения результатов в других
ячейках скопируйте формулу.
20. Представьте в графическом
виде доли отдельных
групп товаров в общем объеме прибыли за 1-е полугодие.
21. Сохраните рабочую книгу под именем Магазинигрушек в своей папке в папке «Мои документы».
22.Завершите работу с Excel.
Контрольное задание.
Транспортное
агентство «Быстрее ветра» предоставляет услуги по перевозке грузов по различным
маршрутам.
Имеются справочные данные:
а) технические характеристики
каждого автомобиля
|
№ п/п |
Марка автомобиля |
Расход топлива
на 100
км (л) |
Грузоподъёмность (т) |
|
1 |
ЗИЛ |
42 |
7 |
|
2 |
КАМАЗ |
45 |
16 |
|
3 |
МАЗ |
53 |
12 |
б) маршрут (номер рейса),
протяженность рейса
|
№ п/п |
Номер рейса |
Протяжённость маршрута (км) |
|
1 |
А
112 |
420 |
|
2 |
В
447 |
310 |
|
3 |
М
023 |
225 |
|
4 |
С
431 |
250 |
в) данные о выполненных
маршрутах по каждому водителю за неделю с указанием количества рейсов
|
№ п/п |
ФИО водителя |
Марка автомобиля |
Номер рейса |
Количество выполненных рейсов |
|
1 |
Иванов И.И |
КАМАЗ |
А
112 |
4 |
|
2 |
Петров П.П |
ЗИЛ |
С
431 |
3 |
|
3 |
Сидоров С.С |
МАЗ |
А
112 |
5 |
|
4 |
Волков В.В |
МАЗ |
М
023 |
7 |
|
5 |
Михайлов М.М |
ЗИЛ |
В
447 |
2 |
|
6 |
Яковлев Я.Я |
КАМАЗ |
С
431 |
8 |
|
7 |
Зверев З.З |
КАМАЗ |
В
447 |
4 |
|
8 |
Окунев О.О |
МАЗ |
С
431 |
5 |
|
9 |
Смирнов С.С |
ЗИЛ |
А
112 |
6 |
|
10 |
Лебедев Л.Л |
КАМАЗ |
М
023 |
7 |
Рассчитать:
· количество израсходованного топлива каждым водителем
за неделю;
· всего перевезенного груза каждым водителем за неделю;
· визуализировать полученные расчеты с помощью
диаграмм.
Алгоритм
решения задачи
1. Запустить табличный процессор Excel
2. Создать книгу с именем «Быстрый
ветер»
3. Лист 1 переименовать в лист с
названием Характеристики Автомобилей (Хар.авт.).
На этом листе создать таблицу, которая
будет отражать сведения о марке автомобиля, расходе топлива на 100 км и
грузоподъемности этого автомобиля (таблица из справочных данных под буквой а)
4. Лист
2 переименовать в лист с названием Рейсы.
На этом листе создать таблицу, которая
будет содержать сведения о протяженности маршрута (в км) в зависимости от
номера рейса (таблица из справочных данных под буквой б)
5. Лист
3 переименовать в лист с названием Сведения.
На этом листе создать таблицу вида:
Данные о
выполненных маршрутах по каждому водителю за неделю
|
№ п/п |
ФИО водителя |
Марка авто- мобиля |
Номер рейса |
Коли- чество выпол- ненных рейсов |
Протя- женность маршрута (км) |
Расход топлива на 100 км (л) |
Израсхо- довано топлива за неделю (л) |
Грузо- подъемность автомашины (т) |
Вес перевезенного груза (т) |
|
1 |
Иванов И.И |
КАМАЗ |
А
112 |
4 |
|
|
|
|
|
|
2 |
Петров П.П |
ЗИЛ |
С
431 |
3 |
|
|
|
|
|
|
3 |
Сидоров С.С |
МАЗ |
А
112 |
5 |
|
|
|
|
|
|
4 |
Волков В.В |
МАЗ |
М
023 |
7 |
|
|
|
|
|
|
5 |
Михайлов М.М |
ЗИЛ |
В
447 |
2 |
|
|
|
|
|
|
6 |
Яковлев Я.Я |
КАМАЗ |
С
431 |
8 |
|
|
|
|
|
|
7 |
Зверев З.З |
КАМАЗ |
В
447 |
4 |
|
|
|
|
|
|
8 |
Окунев О.О |
МАЗ |
С
431 |
5 |
|
|
|
|
|
|
9 |
Смирнов С.С |
ЗИЛ |
А
112 |
6 |
|
|
|
|
|
|
10 |
Лебедев Л.Л |
КАМАЗ |
М
023 |
7 |
|
|
|
|
|
|
Итого |
|
|
|
|
|
|
|
|
|
|
Среднее значение |
|
|
|
|
|
|
|
|
|
Заполнить таблицу:
· в колонке Протяженность маршрута (км)
связать данные листа Рейсы в зависимости от номера рейса,
выполненного каждым водителем
· в колонке Расход топлива на 100 км
(л) связать данные листа Характеристики Автомобилей в зависимости от
марки автомобиля
· в колонке Израсходовано топлива за
неделю (л) произвести расчет в зависимости от всего пройденного расстояния
каждым водителем с учетом расхода топлива на 100 км
· в колонке Грузоподъемность автомашины
(т) связать данные листа Характеристики Автомобилей в зависимости от
марки автомобиля по каждому водителю
· в колонке Вес перевезенного груза (т)
произвести расчет в зависимости от количества выполненных рейсов каждым
водителем с учетом грузоподъемности автомашины
6. Создать Лист 4 с названием Ведомость,
который будет содержать таблицу с данными по расходу горючего и весом
перевезенного груза за отчетный период по каждому водителю, т.е. таблицу вида:
Ведомость расхода горючего и веса
перевезенного груза за неделю
|
№ п/п |
ФИО водителя |
Номер рейса |
Выполнено рейсов |
Израсходовано топлива (л) |
Вес перевезенного груза (т) |
|
1 |
Иванов И.И |
А
112 |
|
|
|
|
2 |
Петров П.П |
С
431 |
|
|
|
|
3 |
Сидоров С.С |
А
112 |
|
|
|
|
4 |
Волков В.В |
М
023 |
|
|
|
|
5 |
Михайлов М.М |
В
447 |
|
|
|
|
6 |
Яковлев Я.Я |
С
431 |
|
|
|
|
7 |
Зверев З.З |
В
447 |
|
|
|
|
8 |
Окунев О.О |
С
431 |
|
|
|
|
9 |
Смирнов С.С |
А
112 |
|
|
|
|
10 |
Лебедев Л.Л |
М
023 |
|
|
|
|
Итого |
|
|
|
|
|
Заполнить таблицу:
·
в
колонке Номер рейса связать данные с листом Сведения по каждому
водителю
·
в
колонке Выполнено рейсов связать данные с листом Сведения по
каждому водителю
·
в
колонке Израсходовано топлива (л) связать данные с листом Сведения
по каждому водителю
·
в
колонке Вес перевезенного груза (т) связать данные с листом Сведения
по каждому водителю
7. Построить две диаграммы на отдельных листах:
а) Гистограмму Расход топлива за
неделю, в которой отражаются данные колонки Израсходовано топлива (л)
по каждому водителю таблицы листа Ведомость
б) Круговую диаграмму Вес
перевезенного груза каждым водителем, в которой отражаются данные колонки Вес
перевезенного груза (т) по каждому водителю таблицы листа Ведомость
Краткие теоретические сведения.
Отчёт сводной таблицы используется для суммирования,
анализа, исследования и представления сводных данных.
Чтобы создать отчет сводной таблицы, следует задать
его исходные данные, расположение в книге и сформировать поля.
1. Выберите ячейку из диапазона ячеек или укажите точку
вставки внутри таблицы MicrosoftOfficeExcel.
Убедитесь, что в
диапазон ячеек попадает заголовок столбца.
2. Выполните одно из следующих действий.
· Чтобы создать сводную таблицу, на вкладке Вставка
в группе Таблицы выберите раздел Сводная таблица, а затем пункт Сводная
таблица.
На экран будет выведено диалоговое окно Создание
сводной таблицы.
· Чтобы создать сводную таблицу и сводную диаграмму, на
вкладке Вставкав группе Таблицы выберите раздел Сводная
таблица, а затем пункт Сводная диаграмма.
На экран будет выведено диалоговое окно Создать
сводную таблицу и сводную диаграмму.
3. Выберите источник данных.
· Выберите пункт Выбрать таблицу или диапазон.
· Введите в поле Таблица или диапазондиапазон ячеек
или ссылку на таблицу, например «=ПрибыльЗаКвартал».
·
Если перед
запуском мастера была выбрана ячейка в диапазоне ячеек или установлена точка
вставки в таблице, соответствующий диапазон ячеек или ссылка на таблицу будут
помещены в поле Таблица или диапазон.
·
Для выбора
диапазона ячеек или таблицы также можно нажать кнопкусвертывания диалогового
окна
Примечание.
Если диапазон ячеек находится на другом
листе той же книги или в другой книге, введите имя книги и листа, используя
следующий синтаксис: ([имякниги]имялиста!диапазон).
4. Определите местоположение. Выполните одно из следующих
действий.
·
Чтобы поместить
отчет сводной таблицы на новый лист, начиная с ячейки A1, щелкните пункт На
новый лист.
·
Чтобы поместить
отчет сводной таблицы на существующий лист, выберите пункт На существующий лист
и введите первую ячейку диапазона, в который следует поместить отчет сводной
таблицы.
·
Также можно
нажать кнопкусвертывания диалогового окна, чтобы временно скрыть диалоговое
окно и выделить нужную ячейку, а затем нажать кнопкуразвертывания диалогового
окна.
5. Нажмите кнопку ОК.
В указанное место будет вставлена пустая
сводная таблица, при этом на экран будет выведен список полей сводной таблицы.
Пользователь получит возможность вставлять в нее поля, создавать макет и
настраивать отчет сводной таблицы
Для того, чтобы изменить отчет сводной
таблицы или диаграммы, используйте список полей для изменения порядка полей и
их удаления. По умолчанию список полей сводной таблицы имеет две секции: секцию
полей в верхней части — для добавления и удаления полей, и секцию макета в
нижней части — для изменения порядка полей и их положения. Можно закрепить
список полей сводной таблицы у одной из сторон экрана и изменить его
горизонтальный размер. Можно также снять прикрепление списка полей сводной
таблицы к экрану, в этом случае можно будет изменить его размер как по
вертикали, так и по горизонтали.
Практическое задание
v Загрузите ранее созданный файл kadr2. xlsх
1. Получить информацию о
суммарных окладах по отделам.
1.1 Установите курсор на одну из ячеек
списка.
1.2 В меню выберитеВставка, на ленте в разделе Таблицы – Сводная
таблица.
ЗапускаетсяМастер сводных таблиц.
I шаг
– указание диапазона, содержащего исходные данные.
Установите переключатель в положение Выбрать таблицу или диапозон. Excel сам “догадался”, что это
таблица, т.к. мы предварительно выделили в ней ячейку.
II шагразмещение сводной
таблицы на новом листе или на
существующем.НажмитеОК. Выберете на новом листе.
III шаг
– определение структуры таблицы
В диалоговом окне Список полей сводной таблицы расположен
шаблон, содержащий 4 области (см. рис.8),
в которых нужно разместить название полей исходного списка. Эти поля
перечислены слева от шаблона или сверху от него.
|
Фильтр отчета |
Названия столбцов |
|
Название строк |
Значения |
рис.8
Перетащите мышью поле Отдел в
область Название строк.
Перетащите мышью поле Оклад в
область Значения ( в этой области
обязательно должно что-либо присутствовать). Обратите внимание, что вместо Оклад
в поле теперь написано Сумма по полю Оклад. Мастер сам выбрал итоговую
функцию. Щёлкните мышкой в любом свободном пространстве листа.
Получите
таблицу, представленную на рис.9.
|
Названия строк |
Сумма по полю Оклад |
|
ОТД |
23024 |
|
ОНК |
19288 |
|
АПС |
32808 |
|
ТКБ |
22504 |
|
Общий итог |
97624 |
рис.9.
Сводная таблица «Суммарные оклады по отделам»
2. Форматирование таблицы
2.1Поменяйте название в ячейке Названия строк на Отдел, а название в ячейке Сумма по полю Оклад на Суммарный
оклад.
2.2 Выровняйте по центру
содержимое столбцов.
2.3 Наложите на числа формат с двумя цифрами после точки.
В результате получится
таблица, представленная на рис.10
|
Отдел |
Суммарный оклад |
|
ОТД |
23024,00 |
|
ОНК |
19288,00 |
|
АПС |
32808,00 |
|
ТКБ |
22504,00 |
|
Общий итог |
97624,00 |
Рис.10. Сводная таблица
«Суммарные оклады по отделам»
после форматирования
2.4.
Переименуйте лист, на котором расположена сводная таблица в Сводная таблица.
3. Изменение вида структуры
сводной таблицы.
ВызовитеМастер сводных таблиц,
щёлкнув по любой ячейке сводной таблицы. Автоматически открывается 3-й шаг, и можно
изменить макет сводной таблицы: поле Отдел перетащите в область Названия столбцов. В результате получится
таблица, представленная на рис.11.
|
Названия
столбцов |
|||||
|
ОТД |
ОНК |
АПС |
ТКБ |
Общий итог |
|
|
Суммарный оклад |
23024,00 |
19288,00 |
32808,00 |
22504,00 |
97624,00 |
рис.11 Сводная таблица
«Суммарные оклады по отделам»
после изменения вида
структуры
4. Изменение
порядка значений полей строк (столбцов).
Пусть требуется установить
такой порядок расположения отделов: ОНК, АПС, ОТД, ТКБ. Можно непосредственно
переставить ячейки: курсор мыши установите на границу ячейки АПС так, чтобы он
принял форму четырехсторонней стрелки, после чего, не
отпуская левую клавишу мыши, перетащите ячейкуАПС в ячейку ОНК и т.д.
5. Обновление
сводной таблицы.
Измените один из окладов в
исходном списке на Листе kadr. Для этого просто поменяйте
Разряд для любого сотрудника. Сводная таблица не изменится. Чтобы
изменения в исходном списке отразились на сводной таблице, нужно
1) выделить одну из ячеек
сводной таблицы,вызвать контекстное меню
правой кнопкой мыши и из выпадающего пункта меню выбрать команду Обновить
или
2) выделить одну из ячеек
сводной таблицы; нажать на кнопку Работа
со сводными таблицами, расположенную в верхней строке экрана и подсвеченную
красным цветом; на ленте в области Данные
щёлкните по кнопке Обновить
Сводная таблица реализована
так. В оперативной памяти создается точная копия исходного списка, так
называемый КЭШ. Именно из КЭШа берет информацию сводная таблица. По команде Данные
– Обновить информация из списка копируется в КЭШ, а сводная таблица,
непосредственно с ним связанная, обновляется.
6. Изменение
итоговой функции.
Мастер сводных таблиц выбрал по умолчанию Суммирование
окладов по отделам. Но можно выбрать и другие итоговые функции
6.1
Выполните п.1,
расположив новую сводную таблицу под существующей.
6.2
Выделитекакую-либо
ячейку в области данных, для которой хотите изменить итоговую функцию.
6.3
Из контекстно - зависимого меню выберите пункт Параметры
полей значений. Будет выведено
диалоговое окно Параметры поля значений. В поле Пользовательское имя введите
Максимум по полю Оклад.
6.4
В поле Операция выберите Максимум.
6.5
Щёлкните по кнопке ОК.
Получите таблицу,
представленную на рис 12.
|
Названия столбцов |
|||||
|
ОТД |
ОНК |
АПС |
ТКБ |
Общий итог |
|
|
Максимум по полю Оклад |
7256 |
6536 |
8056 |
5888 |
8056 |
или
|
Отдел |
Максимум по
полю Оклад |
|
ОТД |
7256 |
|
ОНК |
6536 |
|
АПС |
8056 |
|
ТКБ |
5888 |
|
Общий итог |
8056 |
рис.12
Сводная таблица « Максимум по полю Оклад»
7. Получить
информацию о долях суммарных окладов по
отделам.
7.1 Выполните п.1, расположив новую
сводную таблицу под существующими.
7.2 Выделитекакую-либо ячейку в
области данных, для которой хотите изменить итоговую функцию.
7.3 Из контекстно - зависимого меню выберите пункт Параметры
полей значений. Будет выведено
диалоговое окно Параметры поля значений.
7.4 Щёлкните кнопку Дополнительные
вычисления
7.5 В
списке Дополнительные
вычисления выберите Доля от суммы по столбцу.
7.6 Нажмите кнопкуЧисловой формат. Появится диалоговое окно
для выбора числового формата.
Получите
таблицу, представленную на рис.13.
|
Названия
строк |
Сумма по
полю Оклад |
|
ОТД |
23,35% |
|
ОНК |
19,56% |
|
АПС |
34,28% |
|
ТКБ |
22,82% |
|
Общий итог |
100,00% |
Рис.
13 Сводная таблица «Доли суммарных окладов по отделам»
7.7 Получите таблицу,
следующего вида:
|
Названия столбцов |
|||||
|
ОТД |
ОНК |
АПС |
ТКБ |
Общий итог |
|
|
Сумма по полю Оклад |
23,35% |
19,56% |
34,28% |
22,82% |
100,00% |
1. Получить информацию о количестве
мужчин и женщин в отделах.
8.1 Установите курсор на одну из ячеек
списка.
8.2 ЗапуститеМастер
сводных таблиц. (В меню выберитеВставка, на
ленте в разделе Таблицы – Сводная таблица). Расположите создаваемую
сводную таблицу под существующими на листе Сводная таблица.
8.3
На 3-ем шаге
сформируйте макет : в область строк поместите поле Отдел, в область
столбцов – Пол, в области данных можно разместить любое текстовое поле,
например, тот же Отдел Появится
строкаКоличество по полю Отдел (если бы вы поместили числовое поле, то Мастер
предложил бы вам сумму по этому полю и пришлось бы менять “сумму” на
“количество”).
8.4 Щёлкните мышкой в любом
свободном пространстве листа.
Получите таблицу,
представленную на рис.14.
|
Количество
по полю Отдел |
Названия
столбцов |
|||
|
Названия строк |
ж |
М |
муж |
Общий итог |
|
ОТД |
3 |
2 |
5 |
|
|
ОНК |
1 |
4 |
5 |
|
|
АПС |
3 |
2 |
2 |
7 |
|
ТКБ |
1 |
4 |
5 |
|
|
Общий итог |
8 |
12 |
2 |
22 |
рис.14
Сводная таблица «Количество мужчин и женщин в отделах»
8.1
Получите таблицу, следующего вида:
|
Количество
по полю Отдел |
Названия
столбцов |
||||
|
Пол |
ОТД |
ОНК |
АПС |
ТКБ |
Общий итог |
|
ж |
3 |
1 |
3 |
1 |
8 |
|
М |
2 |
4 |
2 |
4 |
12 |
|
муж |
2 |
2 |
|||
|
Общий итог |
5 |
5 |
7 |
5 |
22 |
2. Удаление общих итогов по
строкам и столбцам (чтобы, предположим, не загромождать таблицу дополнительной
информацией).
9.1 Получите сводную таблицу, вид которой
представлен на рис. 15, расположив её под
существующими.
|
Сумма по
полю Оклад |
Названия столбцов |
||||
|
Пол |
ОТД |
ОНК |
АПС |
ТКБ |
Общий итог |
|
ж |
17704 |
2496 |
18672 |
5888 |
44760 |
|
М |
5320 |
16792 |
7704 |
16616 |
46432 |
|
муж |
7432 |
7432 |
|||
|
Общий итог |
23024 |
19288 |
33808 |
22504 |
98624 |
рис.15 Сводная таблица
«Суммарные оклады по отделам»
9.2 Выделите какую-либо ячейку в сводной таблице.
9.3 В контекстном меню выберите Параметры сводной таблицы…
9.4 В появившемся диалоговом окне Параметры сводной таблицы перейдите на вкладку Итоги и фильтры и снимите
флажки в окнах Показывать общие итоги для строк и Показывать общие
итоги для столбцов.
9.5 Нажмите кнопку ОК.
9.6 Переместите поле Отдел в Область строк.
Получите следующую таблицу:
|
Пол |
Сумма по
полю Оклад |
|
ж |
44760 |
|
М |
46432 |
|
муж |
7432 |
или
|
Пол |
Сумма по
полю Оклад |
|
ж |
44760 |
|
ОТД |
17704 |
|
ОНК |
2496 |
|
АПС |
18672 |
|
ТКБ |
5888 |
|
М |
46432 |
|
ОТД |
5320 |
|
ОНК |
16792 |
|
АПС |
7704 |
|
ТКБ |
16616 |
|
муж |
7432 |
|
АПС |
7432 |
10. Получите сведенияо количестве сотрудников мужского пола, не имеющих
детей, имеющих одного ребенка, и т. д.
10.1. Выделите ячейку в списке.
10.2. ВыберитеВставка - Таблицы – Сводная таблица. Расположите создаваемую сводную таблицу
под существующими на листе Сводная таблица.
10.3. На макете в область строк поместите поле Дети,
а в область столбцов – поля Пол и Отдел, в область данных
поместите полеДети, сделайте по нему двойной щелчок и замените
итоговую функцию с Суммы на Количество.
10.4 Получилась довольно таки громоздкая таблица. Упростите
ее внешний вид, испытав различные варианты размещения полей в области строк и
столбцов. Возможный вариант таблицы:
|
Количество по полю Дети |
Названия
столбцов |
||||||||
|
Названия
строк |
0 |
1 |
2 |
3 |
6 |
8 |
9 |
(пусто) |
Общий итог |
|
ОТД |
2 |
1 |
2 |
5 |
|||||
|
ж |
2 |
1 |
3 |
||||||
|
М |
1 |
1 |
2 |
||||||
|
ОНК |
3 |
1 |
1 |
5 |
|||||
|
ж |
1 |
1 |
|||||||
|
М |
2 |
1 |
1 |
4 |
|||||
|
АПС |
1 |
2 |
3 |
6 |
|||||
|
ж |
1 |
1 |
1 |
3 |
|||||
|
М |
1 |
1 |
2 |
||||||
|
муж |
1 |
1 |
|||||||
|
ТКБ |
2 |
1 |
1 |
1 |
5 |
||||
|
ж |
1 |
1 |
|||||||
|
М |
2 |
1 |
1 |
4 |
|||||
|
Общий итог |
4 |
7 |
5 |
2 |
1 |
1 |
1 |
21 |
10.5 Получите записи исходного списка,
удовлетворяющие критерию: мужчины, работающие в отделе ОНК и имеющие одного
ребенка. Для этого используйте фильтры.
В результате получите таблицу следующего вида:
|
Количество
по полю Дети |
Названия столбцов |
||||
|
Названия
строк |
1 |
Общий итог |
|||
|
ОНК |
1 |
1 |
|||
|
М |
1 |
1 |
|||
|
Общий итог |
1 |
1 |
10.6 Получите записи исходного списка, удовлетворяющие двум произвольным критериям.
v Сохраните рабочую книгу под именем kadr8 в своей папке в папке «Мои документы».
v Завершите работу с Excel.
Контрольные задания
Задание 1
Дана исходная таблица “Учет количества деревьев”
|
N п.п. делянки |
Порода |
Возраст, лет |
Высота,м |
Площадь
учета,м2 |
Количество
деревьев, шт. |
|
1 |
дуб |
2 |
2,25 |
25 |
35 |
|
1 |
дуб |
3 |
0,35 |
25 |
109 |
|
1 |
клен |
2 |
0,5 |
25 |
6 |
|
1 |
липа |
10 |
2,5 |
50 |
3 |
|
1 |
ель |
5 |
0,3 |
50 |
5 |
|
2 |
дуб |
3 |
0,35 |
25 |
98 |
|
2 |
дуб |
5 |
0,6 |
10 |
56 |
|
2 |
дуб |
7 |
0,78 |
10 |
22 |
|
2 |
дуб |
10 |
1,1 |
25 |
2 |
|
2 |
липа |
3 |
0,41 |
25 |
14 |
|
3 |
дуб |
10 |
1,2 |
25 |
1 |
|
3 |
ель |
7 |
0,85 |
15 |
1 |
|
3 |
клен |
15 |
3 |
25 |
2 |
|
3 |
липа |
10 |
2,5 |
10 |
4 |
|
3 |
липа |
15 |
3,1 |
25 |
4 |
|
4 |
ель |
6 |
0,54 |
25 |
3 |
|
4 |
ель |
7 |
0,9 |
25 |
2 |
|
4 |
клен |
5 |
1,55 |
25 |
1 |
|
4 |
липа |
7 |
1,7 |
25 |
5 |
|
4 |
липа |
15 |
3 |
50 |
4 |
|
5 |
дуб |
3 |
0,31 |
25 |
24 |
|
5 |
клен |
2 |
0,4 |
10 |
10 |
|
5 |
клен |
5 |
3 |
10 |
7 |
|
5 |
липа |
15 |
3,1 |
25 |
21 |
|
6 |
дуб |
2 |
0,23 |
15 |
21 |
|
6 |
ель |
7 |
0,85 |
15 |
2 |
|
6 |
липа |
7 |
2 |
30 |
5 |
|
6 |
липа |
10 |
2,5 |
25 |
4 |
|
6 |
липа |
11 |
2,4 |
25 |
14 |
Построить сводную таблицу, сгруппировав данные по породе,
№ делянки и возрасту. Подсчитать количество деревьев в указанных группах.
Задание 2
Дана исходная таблица “Объём продаж фирмы за первый
квартал 2013г.”
Объем продаж
фирмы за первый квартал 2013г.
|
Месяц |
Год |
Продавец |
Товар |
Район |
Объем |
Сбыт |
|
Январь |
1995 |
Иванов |
Детектив |
Северный |
12 |
102 |
|
Февраль |
1993 |
Петров |
Детектив |
Южный |
13 |
105 |
|
Март |
1994 |
Сидоров |
Детектив |
Западный |
11 |
200 |
|
Январь |
1994 |
Иванов |
Поэзия |
Западный |
14 |
123 |
|
Апрель |
1995 |
Иванов |
Поэзия |
Восточный |
13 |
200 |
|
Январь |
1993 |
Петров |
Детектив |
Северный |
11 |
187 |
|
Июнь |
1995 |
Сидоров |
Классика |
Южный |
10 |
156 |
|
Март |
1994 |
Иванов |
Поэзия |
Южный |
16 |
186 |
|
Июнь |
1993 |
Петров |
Классика |
Восточный |
11 |
190 |
|
Июль |
1993 |
Сидоров |
Детектив |
Западный |
20 |
122 |
|
Август |
1995 |
Иванов |
Классика |
Южный |
15 |
122 |
|
Апрель |
1993 |
Петров |
Поэзия |
Южный |
11 |
186 |
|
Май |
1994 |
Петров |
Детектив |
Западный |
10 |
145 |
|
Июнь |
1995 |
Иванов |
Детектив |
Северный |
14 |
135 |
|
Июль |
1995 |
Сидоров |
Детектив |
Северный |
15 |
164 |
|
Август |
1994 |
Иванов |
Детектив |
Восточный |
18 |
172 |
|
Сентябрь |
1993 |
Сидоров |
Классика |
Южный |
19 |
155 |
|
Октябрь |
1994 |
Иванов |
Классика |
Северный |
13 |
162 |
На новом листе (одном) создать сводные таблицы:
·
для каждого вида
товара получить итоговые данные по Объёму и Сбыту для каждого месяца;
·
для каждого вида
товара получить итоговые данные по Объёму и Сбыту для каждого района;
·
для каждого из
продавцов получить итоговые данные по Объёму и Сбыту для каждого вида товара.
Задание 3.
Дана исходная таблица “Продажи фирмы за первый квартал
2013г.”
Продажи фирмы за первый
квартал 2013г.
|
ФИО |
Продукт |
Дата |
Сумма |
|
Иванов |
Книжка |
01.01.2013 |
200 |
|
Петров |
Ручка |
16.01.2013 |
300 |
|
Сидоров |
Линейка |
28.01.2013 |
100 |
|
Иванов |
Книжка |
05.02.2013 |
250 |
|
Петров |
Книжка |
16.02.2013 |
150 |
|
Сидоров |
Ручка |
21.02.2013 |
50 |
|
Иванов |
Линейка |
28.02.2013 |
300 |
|
Петров |
Линейка |
04.03.2013 |
250 |
|
Сидоров |
Книжка |
09.03.2013 |
300 |
|
Иванов |
Ручка |
12.03.2013 |
100 |
|
Петров |
Линейка |
21.03.2013 |
150 |
|
Сидоров |
Линейка |
29.03.2013 |
300 |
На новом листе (одном) создать сводные таблицы,
отображающие:
· общую сумму продаж по каждому продавцу;
· общую сумму продаж по каждому продавцу по
месяцам
|
Сумма по полю Сумма |
Названия столбцов |
|||
|
Названия строк |
янв |
фев |
мар |
Общий итог |
|
Иванов |
200 |
550 |
100 |
850 |
|
Петров |
300 |
150 |
400 |
850 |
|
Сидоров |
100 |
50 |
600 |
750 |
|
Общий итог |
600 |
750 |
1100 |
2450 |
· общую сумму продаж по каждому продукту по
месяцам
|
Сумма по полю Сумма |
Названия столбцов |
|||
|
Названия строк |
янв |
фев |
мар |
Общий итог |
|
Книжка |
200 |
400 |
300 |
900 |
|
Линейка |
100 |
300 |
700 |
1100 |
|
Ручка |
300 |
50 |
100 |
450 |
|
Общий итог |
600 |
750 |
1100 |
2450 |
· общую сумму продаж по каждому продукту по
продавцам
|
Сумма по полю Сумма |
Названия столбцов |
|||
|
Названия строк |
Иванов |
Петров |
Сидоров |
Общий итог |
|
Книжка |
450 |
150 |
300 |
900 |
|
Линейка |
300 |
400 |
400 |
1100 |
|
Ручка |
100 |
300 |
50 |
450 |
|
Общий итог |
850 |
850 |
750 |
2450 |
Получить сводную таблицу вида:
|
Сумма по полю Сумма |
Дата |
|||
|
Продукт |
янв |
фев |
мар |
Общий итог |
|
Иванов |
200 |
550 |
100 |
850 |
|
Книжка |
200 |
250 |
450 |
|
|
Линейка |
300 |
300 |
||
|
Ручка |
100 |
100 |
||
|
Петров |
300 |
150 |
400 |
850 |
|
Книжка |
150 |
150 |
||
|
Линейка |
400 |
400 |
||
|
Ручка |
300 |
300 |
||
|
Сидоров |
100 |
50 |
600 |
750 |
|
Книжка |
300 |
300 |
||
|
Линейка |
100 |
300 |
400 |
|
|
Ручка |
50 |
50 |
||
|
Общий итог |
600 |
750 |
1100 |
2450 |
Порядок оформления отчёта по
лабораторной работе.
В
печатном виде представить алгоритмы и результаты выполнения контрольных заданий лабораторной работы.
Краткие теоретические сведения.
Другим способом получения
итоговой информации является консолидация – агрегирование согласно
выбранной функции обработки данных, представленных в исходных областях-источниках.
Результат консолидации находится в области назначения. Области-источники
могут находиться на различных листах или рабочих книгах. В консолидации может
участвовать до 255 областей-источников, а сами источники могут быть закрыты во
время консолидации.
Существуют следующие
варианты консолидации:
·
консолидация по расположению для одинаково организованных источников
(фиксированное расположение);
·
консолидация по категориям для различающихся по расположению данных;
·
консолидация внешних данных.
При
консолидации по расположению все источники имеют одинаковое расположение данных
источников. Данные имеют одинаковую структуру, фиксированное расположение ячеек
и могут быть консолидированы с определённой функцией обработки (среднее
значение, максимальное, минимальное и т.п.) по их расположению. Метки категорий
данных в выделяемые области-источники не включаются.
Пример. На отдельных листах рабочей
книги по каждой учебной группе хранятся сведения о среднем балле по
фиксированному перечню предметов в разрезе видов занятий (рис. 16). Серым
цветом показана консолидируемая область источников.
|
Номер группы |
Предметы |
|
|
Вид занятий |
Предмет 1 |
Предмет 2 |
|
Лекции |
Консолидируемая |
|
|
Пр. занятия |
область |
|
|
Лаб. занятия |
|
|
При
консолидации по категориям области-источники содержат однотипные данные, но организованные в
различных областях-источниках неодинаково. Для консолидации данных
по категориям используются метки строк или столбцов либо как строк, так
и столбцов, которые должны совпадать. Метки включаются в выделенные
области-источники. При консолидации данных по категориям указывается тип меток
– в верхней строке и левом столбце. Метки и консолидируемые данные должны
находиться в непосредственной близости друг с другом.
Пример. На рабочих листах представлена информация
областей-источников в виде структуры на рис. 17.
|
Гр.
133 |
Предметы |
|
|
|
Предмет
3 |
Предмет
2 |
|
Лекции |
|
|
|
Семинары |
|
|
|
Лаб. раб. |
|
|
|
Гр. 133 |
Предметы |
|
|
|
Предмет 1 |
Предмет 2 |
|
Лекции |
|
|
|
Пр. Лаб. раб. |
|
|
данных по категориям
Число строк - переменное, состав предметов и виды
занятий повторяются и могут рассматриваться как имена столбцов для консолидации
по категориям. Серым цветом показана консолидируемая область источников.
Для консолидации данных курсор устанавливается в
область места назначения и выполняется команда Данные - Работа с данными - Консолидация.
Для одного листа итогов консолидации набор ссылок на
области-источники постоянен, на нём можно построить несколько видов
консолидации с помощью различных функций. Курсор переставляется в новое место,
выполняется команда Данные - Работа с
данными - Консолидация, выбирается лишь другая функция для получения сводной
информации.
Практическое задание.
Ø
На рабочих листах с именами «январь», «февраль», «март» приведены
фамилии торговых агентов и количество сделок, которые они совершили в течение
месяца (рис.18). Построить сводку за 1-й квартал.
Январь
|
Февраль |
||||
|
Фамилия
И.О. |
Сделки |
Объем |
Фамилия
И.О. |
Сделки |
Объем |
|
Идрисов
И.И. |
6 |
250 |
Расулов
М.Р. |
5 |
200 |
|
Абдуллаев
А.В. |
12 |
430 |
Идрисов
И.И. |
8 |
200 |
|
Магомедов
М.Г. |
7 |
180 |
|
|
|
|
Март |
||
|
Фамилия И.О. |
Сделки |
Объем |
|
Идрисов
И.И. |
12 |
200 |
|
Расулов
М.Р. |
10 |
300 |
|
Абдуллаев
А.В. |
8 |
150 |
|
Магомедов
М.Г. |
6 |
220 |
рис.18.
v Загрузите Excel.
1.
Переименуйте
листы: дайте им названия «Январь», «Февраль», «Март»,
«1-й квартал».
2.
Выделите все
листы:
2.1 перейдите на лист «Январь»;
2.2 нажмите
клавишу Shift;
2.3 не отпуская клавишу Shift, щелкните по ярлычку листа «1-й квартал». Будут выделены все рабочие листы.
Если
нужно выделить только некоторые листы рабочей книги, то нужно удерживать
нажатой клавишу Ctrl.
3.
Введите
в ячейку А1 – Фамилия И.О.,
в ячейку В1 – Сделки,
в ячейку С1 – Объем.
4.
Щелкните
по ярлычку листа «Февраль».
(Выделения с нескольких листов будет снято).
5.
Убедитесь,
что в ранее выделенные листы внесен один и тот же текст в ячейки А1, В1, С1.
6.
Для иллюстративных целей поменяйте на листе «Февраль» содержимое ячеек: в В1 – Объем, а в С1 – Сделки.
7.
Введите
в листы с названиями месяцев информацию в соответствии с таблицей (рис.8.).
(Названия месяцев вводить не нужно, они на ярлыках листов). Обратите внимание,
что фамилии в листах будут в полном беспорядке, заголовки столбцов тоже перепутаны
(но фамилии всегда в первом столбце)
8.
Перейдите на
лист «1-й квартал» и выделите ячейку А1,
которая будет служить верхней левой ячейкой для блока с результатами
консолидации.
9.
Выберите на
вкладке Данные в группе Работа
с данными команду Консолидация. Появится диалоговое окно Консолидация.
9.1
В выпадающем списке Функция выставлено Сумма
т.е., выбранные данные будут суммироваться, но можно выбрать и другую итоговую
функцию.
9.2
Щелкните
мышью в поле Ссылка.
9.3
Щелкните
по ярлычку листа Январь:
в поле ввода появится Январь
– формируется адрес.
9.4
Выделите
блок А1:С4 (в поле ввода Январь! $A$1: $C$4)
– вокруг блока ячеек бегущая пунктирная рамка.
9.5
Щелкните
кнопку Добавить – адрес диапазона
отобразится в поле Список диапазонов.
9.6
Аналогично в список диапазонов добавьте
диапазоны Февраль! $A$1:
$C$3
и Март! $A$1:
$C$5.
Список диапазонов консолидации сформирован.
9.7 В диалоговом окне имеется
блок Использовать в качестве имен из
2-х флажков подписи верхней строки и значения левого столбца. Установите оба
флажка, потому что информация в таблице будет идентифицироваться по названиям
строк и столбцов.
9.8 Щелкните по кнопке ОК.
На рабочем
листе появится таблица:
|
|
Сделки |
Объем |
|
Идрисов И.И. |
26 |
650 |
|
Расулов М.Р. |
15 |
500 |
|
Абдуллаев А.В. |
20 |
580 |
|
Магомедов М.Г. |
13 |
400 |
рис. 19
9.9 Внесите изменения в один из
диапазонов консолидации, например, увеличьте количество сделок, записанных за
Идрисовым И.И. Таблица на листе «1-й квартал» не изменилась.
9.10 Обновление таблицы
произойдет, если выполнить команду Данные
– Консолидация. В появившемся диалоговом окне ничего не менять, только
щелкнуть ОК (Курсор установить в
ячейку А1).
9.11 Для того чтобы итоговые
данные обновлялись автоматически, необходимо установить связи с исходными
данными.
·
выделите на листе «1-й квартал»
ячейку А1;
·
откройте диалоговое окно Консолидация;
·
установите флажок Создавать связи
с исходными данными.
Таблица изменится. Появятся
символы структуры – исследуйте структуру. Если теперь изменить количество сделок на листе Март, то итоговые данные будут обновлены автоматически.
v Сохраните рабочую книгу под именем kadr9 в своей папке в папке «Мои документы».
v Завершите работу с Excel.
Контрольное задание.
-
Откройте новую книгу, назвав её Консолидация_располож.
-
Переименуйте листы в ЗП_январь,
ЗП_февр., ЗП_март, 1квартал.
-
Расположите на листе ЗП_январь
таблицу вида:
|
|
Заработная
плата за январь |
||||||||||||
|
|||||||||||||
|
мин.
зар. плата |
800 |
руб. |
|
||||||||||
|
премия (%) |
10,00% |
|
|||||||||||
|
|||||||||||||
|
№ |
Фамилия |
разр. |
разр. коэф |
оклад |
КОД |
начислено |
премия |
всего |
|
||||
|
1 |
Шапкин |
11 |
4,51 |
3608,00 |
20 |
2886,40 |
360,80 |
3247,20 |
|
||||
|
2 |
Баранов |
8 |
3,12 |
2496,00 |
18 |
1797,12 |
249,60 |
2046,72 |
|
||||
|
3 |
Алиев |
14 |
6,51 |
5208,00 |
18 |
3749,76 |
520,80 |
4270,56 |
|
||||
|
4 |
Зиялова |
16 |
8,17 |
6536,00 |
15 |
3921,60 |
653,60 |
4575,20 |
|
||||
|
5 |
Муртилова |
18 |
10,07 |
8056,00 |
17 |
5478,08 |
805,60 |
6283,68 |
|
||||
|
6 |
Сединова |
12 |
5,10 |
4080,00 |
25 |
4080,00 |
408,00 |
4488,00 |
|
||||
|
7 |
Сидоров |
9 |
3,53 |
2824,00 |
24 |
2711,04 |
282,40 |
2993,44 |
|
||||
|
8 |
Харламов |
9 |
3,53 |
2824,00 |
20 |
2259,20 |
282,40 |
2541,60 |
|
||||
|
9 |
Вайзанов |
13 |
5,76 |
4608,00 |
19 |
3502,08 |
460,80 |
3962,88 |
|
||||
|
10 |
Муравьев |
9 |
3,53 |
2824,00 |
24 |
2711,04 |
282,40 |
2993,44 |
|
||||
где КОД – количество отработанных днейза месяц
(≤ 25)
Начислено
= Оклад * КОД
Всего =
Начислено + Премия
- Расположите под рассчитанной
таблицей таблицу следующего вида (это будет первая консолидируемая область):
|
Фамилия |
Начислено |
Премия |
Всего |
|
Шапкин |
2886,40 |
360,80 |
3247,20 |
|
Баранов |
1797,12 |
249,60 |
2046,72 |
|
Алиев |
3749,76 |
520,80 |
4270,56 |
|
Зиялова |
3921,60 |
653,60 |
4575,20 |
|
Муртилова |
5478,08 |
805,60 |
6283,68 |
|
Сединова |
4080,00 |
408,00 |
4488,00 |
|
Сидоров |
2711,04 |
282,40 |
2993,44 |
|
Харламов |
2259,20 |
282,40 |
2541,60 |
|
Вайзанов |
3502,08 |
460,80 |
3962,88 |
|
Муравьев |
2711,04 |
282,40 |
2993,44 |
- Скопируйте
рассчитанную таблицу на другие листы (ЗП_февр., ЗП_март). Измените кол.отработанных дней и название
таблицы.
- Создайте
консолидируемые области на каждом из листов.
- Создайте
на листе 1квартал консолидируемую
таблицу:
Предусмотрите связь консолидируемых данных с
исходными.
- Сохраните
результаты работы.
- Откройте
новую книгу, назвав её Консолидация_категор.
- Выполните
консолидацию данных по категориям
и сравните результат с рис.20
Рис. 20 Пример консолидации данных по категориям
Каждую таблицу расположить на отдельном листе.
Предусмотрите связь консолидируемых данных с исходными.
- Сохраните
результаты работы.
Порядок оформления отчёта по
лабораторной работе.
В
печатном виде представить алгоритмы и результаты выполнения контрольного
задания лабораторной работы.
Краткие теоретические сведения.
Создание
раскрывающегося списка из диапазона ячеек
Для создания списка допустимых значений для
раскрывающегося списка
1. Введите данные в один столбец или одну строку без
пустых ячеек. Пример:
|
|
A |
|
1 |
Продажи |
|
2 |
Финансы |
|
3 |
НИОКР |
|
4 |
АСУ |
ПРИМЕЧАНИЕ. Можно выполнить сортировку данных в том
порядке, в котором они должны появляться в раскрывающемся списке.
2. Выберите ячейку, в которую требуется поместить
раскрывающийся список.
3.
На вкладке Данные в
группе Работа с данными выберите команду Проверка
данных.
Отобразится диалоговое окно Проверка вводимых
значений
4.
Откройте
вкладку Параметры.
5.
В поле Тип данных выберите Список.
6.
Для указания местоположения списка допустимых
записей укажите ссылку на список в поле Источник.
Убедитесь, что перед ссылкой стоит знак равенства (=).
Убедитесь, что флажок Раскрывающийся список установлен.
7.
Чтобы определить,
может ли ячейка оставаться пустой, установите или снимите флажок Игнорировать
пустые ячейки.
8. При необходимости можно задать отображение сообщения
для ввода при выделении ячейки.
Отображение
сообщения для ввода
8.1
Откройте вкладку Сообщение для ввода.
8.2 Установите флажок Отображать подсказку, если
ячейка является текущей.
8.3
Введите заголовок
и текст сообщения (до 225 знаков).
Практическое задание.
Для оценки работы туристической фирмы
создайте необходимые таблицы, заполните их и представьте запрашиваемую
информацию.
1. Создайте
вспомогательную таблицу, содержащую список менеджеров туристической фирмы.
|
Менеджер |
|
Ларина
Ю.М. |
|
Смирнова
И.А. |
|
Шишкин
С.М. |
|
Савченко
И.Т. |
|
Жукова
А.В. |
2.
Создайте
расчётную таблицу, представленную на рис.21
3.
Установите
нужный формат ячеек. Используйте процентный стиль для фиксированных показателей
(Процент комиссионного сбора, Процент
страхового взноса).
4.
Внесите
в таблицу необходимые данные и расчётные формулы:
·
Процент
комиссионного сбора = 3 %;
·
Процент
страхового взноса = 2%;
·
Сумма
комиссионного сбора, руб. = Цена тура, руб. * Процент комиссионного сбора, %;
Расчёт стоимости
тура
Процент комиссионного сбора ____
Процент страхового взноса ____
|
Номер
тура тура |
Цена
тура, тыс. руб. |
ФИО менеджера |
ФИО туриста |
Число путёвок |
Сумма
комиссионного сбора, руб. |
Уплаченная страховка, руб. |
Сумма, руб. |
Скидка, % |
Скидка, руб. |
Стоимость тура, руб. |
|
32 |
100,000 |
Смирнова И.А. |
Сидоров К.П. |
1 |
3000,00 |
2000,00 |
105000,00 |
5 |
5250 |
99 750 |
Рис.
21
·
Уплаченная
страховка, руб. = Цена тура, руб. * Процент страхового взноса, %;
·
Сумма,
руб. = Цена тура, руб. + Сумма комиссионного сбора, руб. + Уплаченная
страховка, руб.;
·
Скидка,
% =
1
%, если Сумма < 60 тыс. руб.,
2
%, если 60 тыс. руб. < Сумма < 80 тыс. руб.,
5 %, если
Сумма > 80 тыс. руб.
Так как Скидка, % зависит от Суммы, руб., используйте функцию ЕСЛИ.
Предельные значения Суммы сделать изменяющимися.
·
Скидка,
руб. = Сумма, руб. * Скидка, %;
·
Стоимость
тура, руб. = (Сумма, руб. — Скидка, руб.) * Число путёвок (обратите внимание на единицы измерения); Стоимость тураследует округлить до целого значения.
5.
Внесите
в таблицу данные о 15 покупателях туров:
·
столбцы Номер тура, ФИО туриста, Число путёвок, Цена тура за-
·
полните
произвольно;
·
в
столбце ФИОменеджера
создайте список, источником данных для которого является
таблица «Менеджер»;
·
для
получения расчётных данных копируйте формулы (см. п. 4), обращая внимание на
использование абсолютных и относительных ссылок.
Полученные
результаты, представлены на рисунке
6.
Выполните
сортировку данных по столбцу ФИОменеджера (по возрастанию).
7.
Постройте
график или диаграмму (на отдельном листе), отражающие по каждому менеджеру
суммы проданных туров.
8.
Произведите
фильтрацию данных:
·
выберите
из списка туристов, получивших не менее 2 % скидки;
·
выберите
из списка туристов, выбравших определённый тур;
·
выберите
из списка туристов, которые купили не менее двух путёвок, но потратили менее
140 тыс. руб.
·
Выполните
условное форматирование данных: в столбце Цена
тура выделите красным цветом значения более 60 тыс. руб.
9.
Вставьте
примечание к столбцу
Уплаченная страховка, содержащее
текст «Страховая компания „Марина”».
10.
Определите
количество туристов, купивших путёвку у Жуковой А.В.
11.
Закрепите
строку заголовка таблицы «Расчёт стоимости тура».
12.
Оформите
в текстовом процессоре MSWord результаты
выполнения задания.
Практическое задание.
Для оценки работы фирмы, продающей
автомобили в кредит при условии 50 %
оплаты рассчитанной стоимости, создайте необходимые таблицы, заполните их и
представьте запрашиваемую информацию.
1. Создайте
вспомогательную таблицу, содержащую список марок автомобилей, которыми торгует
фирма.
|
Марка автомобиля |
|
KiaRio |
|
LadaKalina |
|
LadaPriora |
|
RenaultLogan |
|
BA3-2109 |
|
BA3-2114 |
|
BA3-2115 |
|
Chevrolet
Niva |
2.
Создайте
расчётную таблицу следующего вида:
Расчёт первоначального взноса
при покупке автомобиля в кредит
Стоимость антикоррозийной обработки, руб. _____
Налог на покупку, % _____
Скидка на текущую дату, % _____
|
Марка автомобиля |
Год выпуска |
Мощность двигателя (л. с.) |
Базовая стоимость, тыс. руб. |
Налог на мощность, руб. |
Налог на покупку, руб. |
Антикоррозийная обработка (1/0) |
Итого, руб. |
Скидка, руб. |
Первоначальный взнос, руб. |
|
LadaPriora |
2011 |
98 |
400,000 |
20000 |
12000 |
1 |
436000 |
21800 |
207100 |
3. Установите нужный
формат ячеек. Используйте процентный стиль для фиксированных показателей (Налог на покупку, Скидка на текущую
дату).
4. Внесите
необходимые данные и расчётные формулы:
·
Стоимость
антикоррозийной обработки = 4 000руб.;
·
Налог
напокупку = 3 % ;
·
Скидка
на текущую дату = 5 % ;
·
Налог
на мощность, руб. =
2,5
% от Базовой стоимости, если Мощность двигателя < 100 л. с.,
3,5 % от Базовой
стоимости, если 100 л.с.<Мощность двигателя <
150 л.с.,
5
% от Базовой стоимости, если Мощность двигателя > 150 л. с.
·
Сумма
налога за покупку = Базовая стоимость, руб. * Налог на покупку, %;
·
В
столбце Антикоррозийная обработка по желанию клиента
проставьте 1 (делать обработку) или 0 (не делать обработку);
·
Итого,
руб. = Базовая стоимость, руб. + Налог на мощность, руб. + Сумма налога на
покупку, руб. + Антикоррозийная обработка * Стоимость антикоррозийной
обработки, руб.;
·
Скидка,
руб. = Итого, руб. * Скидка на текущую дату, %;
·
Первоначальный
взнос, руб. = (Итого, руб. - Скидка, руб.) / 2.
5. Внесите в таблицу
12 записей о продаже автомобилей:
·
столбцы Год выпуска,
Мощность двигателя, Базовая стоимость заполните
произвольно;
·
в
столбце Марка автомобиля создайте
список, источником данных для которого является таблица «Марка автомобиля»;
·
для
получения расчетных данных копируйте формулы (см. п. 4), обращая внимание на
использование абсолютных и относительных ссылок.
6. Постройте график
или диаграмму (на отдельном листе), содержащие - значения из столбца Мощность двигателя.
7. Выполните
сортировку данных по столбцу Год
выпуска (по возрастанию), если значения столбца совпадают, то внутри полученной группы
сортируйте по столбцу Марка
автомобиля.
8.
Произведите
фильтрацию данных:
·
выберите
из списка только те машины, у которых мощность равна заданной;
·
выберите
из списка те машины, у которых год выпуска позднее 2007, а базовая стоимость
менее 180 тыс. руб.
9. Выполните условное
форматирование данных: в столбце Первоначальный
взнос выделите красным цветом значения менее 150 тыс. руб.
10. Вставьте
примечание к ячейке Скидка на
текущую дату, содержащее указание текущей даты.
11.
Определите
количество проданных машин заданной марки.
12.
Вычислите
общую стоимость всех проданных машин заданной марки.
13.
Закрепите
строку шапки таблицы «Расчёт стоимости автомобиля».
14.
Оформите
в текстовом процессоре MSWord результаты
выполнения задания.
Практическое задание.
Для оценки работы гостиницы, предлагающей
проживающим номера трёх классов комфортности, создайте необходимые таблицы,
заполните их и представьте запрашиваемую информацию.
1. Создайте вспомогательную
таблицу, содержащую номера трёх классов комфортности.
|
Тип номера |
|
1 класс |
|
2 класс |
|
3
класс |
2.
Для
расчёта окончательного платежа за проживание создайте таблицу «Плата за
проживание в гостинице» по образцу.
Плата за проживание в гостинице
Бронь, % ______
Стоимость номера за 1 день, руб.
1 класс ______
2 класс ______
3 класс ______
|
Номер паспорта |
ФИО проживающего |
Бронь Бронь |
Тип номера |
Дата заезда |
Дата выезда |
Период проживания, дней |
К оплате, руб. |
|
28 02 494756 |
Сидоров И.П. |
+ |
1 класс |
12.03.2013 |
12.03.2013 |
1 |
4 070 |
3.
Установить
нужный формат ячеек. Используйте процентный стиль для соответствующих
показателей.
4.
Внесите
необходимые данные и расчётные формулы:
·
Стоимость бронирования (Бронь, %) = 10 % от стоимости номера;
·
Стоимость номера
за 1 день =
3700
руб. для
номера I класса,
2500
руб. для
номера 2 класса,
1800
руб. для
номера 3 класса.
·
Период
проживания, дней =
Дата выезда -
Дата заезда.
Минимальное количество дней = 1
·
К оплате, руб. = Период проживания,
дней * Стоимость номера, руб. Поскольку стоимость номера зависит от типа,
используйте функцию ЕСЛИ.
5.
Внесите
в таблицу «Плата за проживание в гостинице» 12 записей:
·
столбцы Номер
паспорта, ФИО проживающего, Наличие брони, Дата заезда, Дата выездазаполните произвольно;
·
для
заполнения столбца Тип номера используйте
список, источником данных для которого является таблица «Тип номера»;
·
для
получения расчётных данных копируйте формулы (см. п. 4), обращая внимание на
использование абсолютных и относительных ссылок.
6. Постройте
диаграмму (на отдельном листе), содержащую значения из столбца К оплате.
7.
Выполните
сортировку данных по столбцу Тип
номера (по возрастанию,
затем по столбцу ФИО
проживающего (по алфавиту).
8. Произведите фильтрацию
данных:
·
выберите
записи о проживающих, которые заселились не позднее заданного числа;
·
выберите
записи о проживающих в номерах 1 и 2 типа;
·
выберите
записи о проживающих, которые бронировали номера или стоимость проживания
которых (К оплате) больше 5 тыс. руб.
9.
Выполните
условное форматирование данных: в столбце К
оплате выделите красным цветом значения в промежутке от 5 до 10
тыс. руб.
10. Вставьте
примечание к столбцу Тип номера, содержащее
текст «Гостиница „Турист”».
11.
Определите
количество жильцов, проживающих в номере каждого типа.
12.
Закрепите
строку заголовка таблицы «Плата за проживание в гостинице».
13.
Оформите
в текстовом процессоре MSWord результаты
выполнения задания.
1. Анеликова Л.А. Упражнения по текстовому редактору
Word. – М.: СОЛОН-Пресс, 2006. – 128 с.
2.
Безручко В.Т.
Практикум по курсу «Информатика»: учеб пособие для студентов высш. учебн.
заведений. – М.: Форум:Инфра-М, 2010. – 368с.
3. Богатов Ф. Г. Практикум по информатике: Word - Excel -
Access: Учебное пособие / Ф. Г. Богатов - М.: Щит-М, 2010. - 264 с.
4. Богатов Ф. Г. Практикум по информатике: Word - Excel -
Access: Учебное пособие - 2-е изд., перераб. - М.: Щит-М, 2010. - 264 с.
5.
Златопольский
Д.М. 1700 заданий по Microsoft Excel. – СПб.: БХВ-Петербург, 2003. –
544 с.
6.
Информатика:
практикум по технологии работы на компьютере / под ред. Н.В. Макаровой. –
3-е изд., перераб. – М.: Финансы и статистика, 2003. – 256 с.
7.
Лабораторный
практикум по информатике: Учебное пособие для ВУЗов / В.С. Микшина, Г.А.
Еремеева, Н.Б. Назина и др.; Под ред. В.А. Острейковского. – М.: Высш.
Шк., 2003. – 376 с.
8.
Лавренов С.М.
Excel: Сборник примеров и задач. – М.: Финансы и статистика, 2004. –
336 с.
9.
Маликова Л.В.,
Пылькин А.Н. Практический курс по электронным таблицам MS Excel: учебное
пособие для вузов. – М.: Горячая линия – Телеком, 2004. – 244 c.
10.
Немцова
Т. И., Назарова Ю. В. Практикум по информатике. Т. 1. Практикум по информатике:
Учебное пособие. – М.: ИНФРА-М, 2011. - 320 с.