5. Используя Автофильтр, отобразите все данные списка по АО ИНВЕСТ.
6. Используя Автофильтр, отобразите все данные списка по накладным расходам, а затем только за июнь.
7. По каждому расходу подведите итог по полю Сумма, воспользовавшись командой Данные > Итоги, предварительно отменив Автофильтр и отсортировав данные списка по полю Расход.
8. Присвоите имя (например, имя Список) диапазону ячеек, содержащему все данные списка (включая заглавную строку).
9. Вставьте перед диапазоном со списком 11 пустых строк.
10. В ячейки А1:01 скопируйте шапку таблицы.
11. Используя Расширенный фильтр, отобразите все данные списка по зарплате, используя для области критериев ячейки А1:В2. Изменив область критериев, отобразите все данные списка по накладным расходам.
12. Внесите изменения в область критериев, добавив в ее шапку еще одну ячейку с названием Сумма, так чтобы отобразились накладные расходы только от $500 до $1000.
13. Используя Расширенный фильтр, поместите в любую пустую область рабочего листа все данные списка о накладных расходах и зарплате за июль, предварительно изменив область критериев, либо создав новую.
14. Используя Расширенный фильтр и новую область критериев, рядом с полученным результатом поместите данные списка только по зарплате за июнь и июль, указав дату, сумму и получателя. Для этого предварительно создайте шапку новой таблицы (3 ячейки).
15. Измените область критериев, оставив в качестве критерия только вид расхода — зарплату.
16. Для заданного критерия отбора вычислите общую сумму:
• в ячейку В6 введите формулу расчета суммы, используя Мастер функции БДСУММ. Для указания диапазона базы данных выберите из списка имен ячеек (в левой части строки формул) имя соответствующего диапазона (Список), для задания поля укажите с помощью мыши ячейку с названием поля Сумма, для задания диапазона критериев также воспользуйтесь мышью:
• в ячейку В5 введите текст «Сумма по заданному критерию».
17. Используя функцию БСЧЕТ, подсчитайте в ячейке С6 количество выданных зарплат. Имя поля, указываемого в окне Мастера функций, оставьте прежним. В ячейку С5 введите текст «Количество».
18. Подсчитайте сумму расходов и количество записей по материалам, изменив область критериев. (Область значений при этом изменится автоматически.) 19. Сформируйте таблицу для расчета суммы и количества расходов по каждому виду расхода, скопировав в ячейки А7, А8 и А9 значения поля Расходы: Материалы, Зарплата и Накладные расходы. В ячейках В6 и С6 рассчитайте общую сумму и количество расходов, очистив критерий поиска.
20. Для автоматической подстановки значений из ячеек А7, А8 и А9 в ячейку В2 области критериев и построчного получения результатов в сформированной таблице, выделите диапазон А6:С9 и выполните команду Данные ® Таблица подстановки.
21. Сформируйте еще одну таблицу для расчета количества и суммы расходов, связанных с АО ИНВЕСТ, ТОО Надежда и ЗАО БИН, а также найдите максимальные и минимальные из них, воспользовавшись возможностью автоматической подстановки значений в область критериев для получения соответствующих результатов.
22. Измените в сформированной по предыдущему пункту таблице значения получателей на другие значения (например, скопировав на их место фамилии получателей). Область значений при этом изменится автоматически.
23. Сформируйте еще одну таблицу для расчета количества каждого из расходов по АО Престиж, ЗАО БИН и Васильевой М.Ф., для этого значения получателей запишите в разных строках под ячейкой с формулой, а значения расходов — в разных столбцах, правее ячейки с формулой. Выделите сформированную таблицу и выполните команду Данные - Таблица подстановки, указав, в какую ячейку области критериев значения будут подставляться построчно, а в какую — из столбцов.
24. Создайте на новом рабочем листе сводную таблицу, позволяющую отобразить количество расходов, связанных с каждым получателем. Для этого поместите в область строк значения поля Получатель, а в область столбцов — значения поля Расход (в область данных нужно поместить поле Расход и, щелкнув на нем 2 раза указателем мыши, выбрать функцию для подсчета количества значений по этому полю).
25. Переместите поле Расход из области столбцов в область строк (для этого можно воспользоваться Мастером сводных таблиц, вызвав его либо с помощью соответствующей кнопки панели инструментов Сводные таблицы, либо выбрав соответствующую команду из контекстного меню).
26. На этом же рабочем листе, начиная с новой ячейки, создайте еще одну сводную таблицу, позволяющую отобразить в один столбец сумму расходов, связанных с каждым получателем, предусмотрев возможность фильтрации по полю Расход (поле Расход в этом случае нужно поместить в область страниц, а поле Получатель — в область строк).
• Отобразите данные по зарплате.
• Используя Мастер сводных таблиц, добавьте в область данных количество значений по полю Получатель.
• Отобразите данные по накладным расходам.
• Используя панель инструментов Сводные таблицы (кнопка Отобразить страницы), отобразите информацию по каждому расходу.
27. На этом же рабочем листе, начиная с новой ячейки, создайте еще одну сводную таблицу, позволяющую отобразить в один столбец сумму расходов по каждой дате.
28. Сгруппируйте полученные данные по месяцам, воспользовавшись контекстным меню или соответствующей кнопкой панели инструментов Сводные таблицы.
Вариант 9
1. Создайте таблицу, используя базу данных деклараций, приведённую в табл. 1.
2. Найти, на какую сумму было закуплено мужского, женского и детского трикотажа (код 61*) по отдельности и всего трикотажа ежеквартально. Для этого необходимо провести отбор записей согласно выбранному критерию и необходимые вычисления, используя методы анализа данных в списках.
3. По сводной таблице постройте диаграмму.
4. Оформите отчет с обоснованием выбора метода и описанием последовательности действий при выполнении задания.
Таблица 1
| № п/п | Дата | Наименование товара | Код товара | Количество товара | Цена за шт. | Общая цена |
| 1 | 01.01.2000 | Мужской свитер | 61 | 50 | $40,00 | |
| 2 | 08.01.2000 | Мужское трико | 61 | 20 | $56,43 | |
| 3 | 15.01.2000 | Мужской носовой платок | 35000 | $1,40 | ||
| 4 | 22.01.2000 | Детское трико | 61 | 100 | $123,80 | |
| 5 | 29.01.2000 | Детские ползунки | 61 | 200 | $79,90 | |
| 6 | 05.02.2000 | Женская кофта | 61 | 20345 | $47,80 | |
| 7 | 12.02.2000 | Детские носки | 61 | 12764 | $3,70 | |
| 8 | 19.02.2000 | Женские носки | 61 | 16344 | $3,55 | |
| 9 | 26.02.2000 | Детские сапоги | 300 | $100,00 | ||
| 10 | 04.03.2000 | Детский шарф | 61 | 500 | $13,00 | |
| 11 | 11.03.2000 | Детская шапка | 61 | 47553 | $15,00 | |
| 12 | 18.03.2000 | Женские сапоги | 7432 | $100,00 | ||
| 13 | 25.03.2000 | Женский шарф | 61 | 3357 | $30,00 | |
| 14 | 01.04.2000 | Детская рубашка | 572 | $50,00 | ||
| 15 | 08.04.2000 | Женская ночная | 61 | 627 | $100,00 | |
| 16 | 15.04.2000 | Детская юбка | 342 | $64,80 | ||
| 17 | 22.04.2000 | Женская блуза | 436 | $77,40 | ||
| 18 | 29.04.2000 | Женская куртка | 2345 | $170,00 | ||
| 19 | 06.05.2000 | Мужская куртка | 5223 | $229,90 | ||
| 20 | 13.05.2000 | Женский свитер | 61 | 446 | $42,70 | |
| 21 | 20.05.2000 | Женское трико | 61 | 425 | $62,00 | |
| 22 | 27.05.2000 | Женская юбка | 678 | $63,30 | ||
| 23 | 03.06.2000 | Детская куртка | 785 | $48,90 | ||
| 24 | 10.06.2000 | Женская шляпка | 55712 | $32,40 | ||
| 25 | 17.06.2000 | Мужская шапка | 85473 | $29,80 | ||
| 26 | 24.06.2000 | Мужской шарф | 61 | 6786 | $32,70 | |
| 27 | 01.07.2000 | Детская ночная | 61 | 434 | $45,00 | |
| 28 | 08.07.2000 | Мужская ночная | 61 | 5769 | $39,00 | |
| 29 | 15.07.2000 | Детское пальто | 3990 | $57,00 | ||
| 30 | 22.07.2000 | Детская сумка | 354435 | $23,00 | ||
| 31 | 29.07.2000 | Детские бриджи | 5636 | $61,50 | ||
| 32 | 05.08.2000 | Мужские джинсы | 6456 | $54,99 | ||
| 33 | 12.08.2000 | Женские джинсы | 3567 | $79,90 | ||
| 34 | 19.08.2000 | Детские джинсы | 355 | $75,80 | ||
| 35 | 26.08.2000 | Детский спортивный костюм | 5666 | $57,60 | ||
| 36 | 02.09.2000 | Женский спортивный костюм | 7534 | $19,90 | ||
| 37 | 09.09.2000 | Мужской спортивный костюм | 5654 | $70,00 | ||
| 38 | 16.09.2000 | Женская сумка | 667 | $69,75 | ||
| 39 | 23.09.2000 | Мужская сумка | 6758 | $38,00 | ||
| 40 | 30.09.2000 | Детские варежки | 61 | 686 | $55,00 | |
| 41 | 07.10.2000 | Детские шорты | 61 | 7885 | $56,00 | |
| 42 | 14.10.2000 | Детский платок | 43534 | $10,00 | ||
| 43 | 21.10.2000 | Женский платок | 4687 | $23,00 | ||
| 44 | 28.10.2000 | Детские перчатки | 61 | 778 | $9,90 | |
| 45 | 04.11.2000 | Детское платье | 782 | $56,00 | ||
| 46 | 11.11.2000 | Детская шуба | 2976 | $56,00 | ||
| 47 | 18.11.2000 | Детский жакет | 87078 | $76,00 | ||
| 48 | 25.11.2000 | Женское пальто | 788 | $450,00 | ||
| 49 | 02.12.2000 | Мужское пальто | 904 | $470,00 | ||
| 50 | 09.12.2000 | Женский галстук | 7879 | $56,70 | ||
| 51 | 16.12.2000 | Мужской галстук | 7345 | $59,90 | ||
| 52 | 23.12.2000 | Женский жакет | 776 | $99,90 | ||
| 53 | 30.12.2000 | Женская рубашка | 2455 | $48,70 | ||
| 54 | 06.01.2001 | Мужская рубашка | 667 | $34,00 | ||
| 55 | 13.01.2001 | Женский жилет | 676 | $53,00 | ||
| 56 | 20.01.2001 | Мужской жилет | 465 | $56,00 | ||
| 57 | 27.01.2001 | Женский топ | 557 | $34,00 | ||
| 58 | 03.02.2001 | Женские бриджи | 756 | $65,00 | ||
| 59 | 10.02.2001 | Женские шорты | 365 | $56,00 | ||
| 60 | 17.02.2001 | Мужские шорты | 565 | $54,00 | ||
| 61 | 24.02.2001 | Женские перчатки | 61 | 985 | $28,00 | |
| 62 | 03.03.2001 | Мужские перчатки | 61 | 576 | $27,00 | |
| 63 | 10.03.2001 | Женская шапка | 61 | 789 | $34,00 | |
| 64 | 17.03.2001 | Женская кепка | 3567 | $35,00 | ||
| 65 | 24.03.2001 | Мужская кепка | 3798 | $38,00 | ||
| 66 | 31.03.2001 | Женские варежки | 61 | 46778 | $20,00 | |
| 67 | 07.04.2001 | Мужские варежки | 61 | 5677 | $19,00 | |
| 68 | 14.04.2001 | Женский берет | 788 | $37,00 | ||
| 69 | 21.04.2001 | Женские туфли | 466 | $60,00 | ||
| 70 | 28.04.2001 | Мужские туфли | 757 | $68,00 | ||
| 71 | 05.05.2001 | Женские часы | 3454 | $65,00 | ||
| 72 | 12.05.2001 | Мужские часы | 5677 | $45,00 | ||
| 73 | 19.05.2001 | Женский браслет | 67748 | $13,00 | ||
| 74 | 26.05.2001 | Мужской браслет | 47668 | $15,00 | ||
| 75 | 02.06.2001 | Женское платье | 884 | $57,00 | ||
| 76 | 09.06.2001 | Женские очки | 5464 | $37,00 | ||
| 77 | 16.06.2001 | Мужские очки | 3566 | $33,00 | ||
| 78 | 23.06.2001 | Женский ремень | 67367 | $8,00 | ||
| 79 | 30.06.2001 | Мужской ремень | 5767 | $9,00 | ||
| 80 | 07.07.2001 | Женский кардиган | 755 | $89,00 | ||
| 81 | 14.07.2001 | Женский полушубок | 64 | $467,00 | ||
| 82 | 21.07.2001 | Мужская шуба | 46 | $510,00 | ||
| 83 | 28.07.2001 | Женские кеды | 75 | $354,00 | ||
| 84 | 04.08.2001 | Мужские кеды | 84 | $435,00 | ||
| 85 | 11.08.2001 | Детские кеды | 45766 | $16,00 | ||
| 86 | 18.08.2001 | Детские тапочки | 56576 | $24,00 | ||
| 87 | 25.08.2001 | Детский свитер | 61 | 5874 | $230,00 | |
| 88 | 01.09.2001 | Женское нижнее белье | 61 | 85885 | $35,00 | |
| 89 | 08.09.2001 | Мужское нижнее белье | 61 | 6658 | $29,00 | |
| 90 | 15.09.2001 | Детское нижнее белье | 61 | 6845 | $11,00 | |
| 91 | 22.09.2001 | Детская юбка | 46889 | $65,00 | ||
| 92 | 29.09.2001 | Детский топ | 61 | 76 | $400,00 | |
| 93 | 06.10.2001 | Женские колготки | 61 | 43 | $540,00 | |
| 94 | 13.10.2001 | Женские тапочки | 476 | $25,00 | ||
| 95 | 20.10.2001 | Мужские тапочки | 779 | $23,00 | ||
| 96 | 27.10.2001 | Детские колготки | 61 | 78978 | $18,00 | |
| 97 | 03.11.2001 | Женские босоножки | 76844 | $54,00 | ||
| 98 | 10.11.2001 | Детские босоножки | 768 | $34,00 | ||
| 99 | 17.11.2001 | Женский пиджак | 6879 | $37,00 | ||
| 100 | 24.11.2001 | Мужской пиджак | 5687 | $32,00 |
Вариант 10