III.3. Использование логической функции Если 2 страница
V.1. Сортировка и фильтрация данных
Сортировка данных используется для упорядочения информации. Для сортировки заданного диапазона можно воспользоваться кнопками панели инструментов 
или меню Данные, Сортировка (Рис. 34).
Рис. 34. Диалоговое окно Сортировка диапазона
Самостоятельно познакомьтесь с возможностями данного диалогового окна по справке Excel (сортировка по одному, двум, трем полям и сортировка по первому ключу).
Фильтрация (выборка) данных позволяет отобразить в таблице только те строки, содержимое ячеек которых отвечает заданному условию (или нескольким условиям). Эта операция может выполняться с помощью: Автофильтра и Расширенного фильтра.
Автофильтр применяется для простых условий отбора (Данные, Фильтр, Автофильтр)
Расширенный фильтр позволяет осуществлять более сложную выборку данных электронной таблицы с заданием одновременно нескольких условий (Данные, Фильтр, Расширенный фильтр). Расширенные условия отбора вводятся в отдельный диапазон условий листа над диапазоном ячеек или таблицей, которые требуется отфильтровать.
Упр. 17. Вычисление плотности населения стран мира
Данное задание предназначено для ознакомления с операциями сортировки и фильтрации.
Технология работы:
1. Откройте ранее созданную в Упр. 13 книгу Работа 1 в папке Excel. Создайте 8 рабочих листов и переименуйте их в Задание_ 1, Задание_ 2 и т.д. Выделите одновременно все 8-мь листов и создайте приведенную на Рис. 35 таблицу.
Вычислите сумму по столбцам ПлощадьиНаселение.Для каждой страны вычислите: Плотность населения и Долю в % от всего населения Земли.
2. На листе Задание_1 скройтедве последние строки таблицы с помощью контекстного меню, выделите всю таблицу (строки с 1 по 15) и скопируйте их ниже на этом же листееще 4 раза.
Рис. 35. Вычисление плотности населения стран мира
На листе Задание_1 во втором экземпляретаблицы выполните сортировку по данным столбца Плотность населения(по убыванию).
В третьем экземпляре таблицы расположите страны по алфавиту.
В четвертом экземпляре таблицы проведите сортировку по данным последнего столбца (%).
В последнем экземпляре таблицы расположите страны по численности населения, а затем в этом же экземпляре отсортируйте данные по первому столбцу (по номерам).
Должен получиться исходный вариант таблицы. Проанализируйте полученные результаты.
3. Произведите фильтрацию записей таблицы на листах со 2 -5 (Задание_2 - Задание_5) согласно следующим критериям:
• На листе 2 выберите страны с площадью более 5 000 тыс. км2.
• На листе 3 - страны с населением меньше 150 млн. чел.
• На листе 4 - выберите страны с плотностью населения от 100 до 300 чел./км2.
• На листе 5 - страны, население которых составляет более 2% от всего населения Земли.
На листе 2 восстановите исходный вариант таблицы и отмените режим фильтрации.
4. На листе Задание_6 раскройте список автофильтра для столбца к которому хотите применить Пользовательский автофильтр и выберите пункт Условие.
Рис. 36. Окно Пользовательский автофильтр
В диалоговом окне Пользовательский автофильтр введите два условия отбора и используйте любой из операторов сравнения Excel. Например: выведите список стран отвечающих условию фильтра площадь страны - меньше 1000 тыс.км2 и население меньше 100 000 тыс. чел.
5. Выделите на листе Задание_7 строку 1 и вставьте перед ней 3 новые строки.
На этом же листе (под таблицей) создайте рамку для текстового поля, используя инструмент панели Рисование Надпись.
Найдите в справочной системе Excel раздел Фильтры, Фильтрация списка с помощью расширенного фильтраи скопируйте содержимое этого раздела в текстовое поле.
Отредактируйте размер текстового поля и текст справки.
Ознакомьтесь с технологией выборки данных с использованием расширенного фильтра и на основе полученных знаний на листе Задание_6выберите из таблицы страны, начинающиеся с буквы "К" и имеющие численность населения более 1 млрд чел.Сохраните работу.
5. На листе Задание_8выделите в таблице столбцы с названиями стран и численностью населения. Постройте круговую диаграмму по данным выделенных столбцов.
Постройте различные гистограммы по данным остальных столбцов.
V.2. Создание сводных таблиц и сводных диаграмм
Подробный анализ данных из таблицы базы данных требует возможности получения сводной информации по записям базы данных. Фактически, речь идет о совмещении средств фильтрации и итоговых вычислений. Результаты такого анализа могут быть представлены в виде сводной таблицы или сводной диаграммы. При создании сводной диаграммы автоматически создается и сводная таблица, содержащая данные, на основе которых строится диаграмма.
Построение сводной таблицы производится с помощью Мастера сводной таблицы (необходимо выделить ячейку, относящуюся к базе данных, Данные, Сводная таблица).
На первом этапе работы мастера выбирается тип данных (для использования базы данных Excel — это пункт В списке или базе данных Microsoft Excel) и тип оформления сводных данных — Сводная таблица. Затем уточняется выбор диапазона. После очередного щелчка на кнопке Далее выбирается местоположение сводной таблицы. В большинстве случаев следует размещать ее на новом рабочем листе (переключатель Новый лист). Для выбора содержания надо щелкнуть на кнопке Макет. Сводная таблица состоит из четырех областей: Страница, Строка, Столбец и Данные (Рис. 37).
Рис. 37. Мастер сводных таблиц и диаграмм - макет
Каждой области сводной таблицы должны соответствовать одно или несколько полей базы данных. Исключение делается только для поля Страница, которое можно оставить пустым. При заполнении сводной таблицы, данные из соответствующих полей берутся автоматически. Для заполнения этих полей служат кнопки, названия которых совпадают с названиями полей базы данных. Поместить кнопку в нужное поле можно методом перетаскивания. В сводной таблице можно использовать только те поля, которые необходимы. Незадействованные поля в отборе и вычислениях не участвуют.
Область Страница играет роль внешнего фильтра. Данные в остальной части таблицы относятся к значению, выбранному в этом поле. Область Строка содержит значения, определяющие содержимое строки таблицы, и задает второй уровень сортировки. Аналогичным образом, область Столбец определяет содержимое столбцов. В отличие от области Страница, в сводную таблицу помещаются данные для всех возможных значений полей, указанных в этих областях.
Область Данные определяет собственно содержимое таблицы. В отличие от всех остальных областей, к данным, попадающим в ячейку таблицы, применяется функция для итоговых вычислений (по умолчанию — суммирование). Если необходимо изменить эту функцию, надо дважды щелкнуть на соответствующей кнопке и выбрать нужную операцию из раскрывающегося списка.
Кроме стандартного набора итоговых функций, можно использовать и дополнительные вычисления. Для этого надо щелкнуть на кнопке Дополнительно, выбрать нужное значение из раскрывающегося списка Дополнительные вычисления и, если требуется, указать необходимые параметры. После выбора и настройки данных следует щелкнуть на кнопке ОК.
Для настройки внешнего вида сводной таблицы надо щелкнуть на кнопке Параметры. Здесь можно задать имя листа сводной таблицы, включить режим суммирования данных по строкам и столбцам, разрешить доступ к информации, использованной при вычислении, выполните другие настройки. По их завершении требуется щелкнуть на кнопке ОК, а затем на кнопке Готово, и сводная таблица строится автоматически.
При создании сводной таблицы автоматически открывается и панель инструментов Сводные таблицы. В дальнейшем открывать и закрывать эту панель можно, щелкая правой кнопкой мыши на любой из открытых панелей инструментов и выбирая пункт Сводные таблицы из контекстного меню см. рисунок.
Если требуется изменить вид сводной таблицы, это можно сделать с помощью кнопок, помещенных в таблице в соответствующих областях. При щелчке на соответствующей раскрывающей кнопке появляется небольшое окно, в котором можно отключить некоторые из отображаемых показателей. Для области Страница справа отображается раскрывающийся список, позволяющий выбрать разные условия фильтрации. Кроме того, щелкнув в пределах сводной таблицы правой кнопкой мыши и выбрав в контекстном меню пункт Отобразить страницы, можно разделить единую сводную таблицу на набор таблиц, соответствующих разным значениям, которые можно выбрать в области Страница.
Сводная таблица остается динамически связанной с данными, на основании которых она была построена. Это, в частности, означает, что для любого значения, приведенного в сводной таблице, можно выяснить, как оно было получено. Для этого надо дважды щелкнуть на соответствующей ячейке таблицы. При этом автоматически создается новый рабочий лист, содержащий выборку записей, использованных при вычислении данного значения.
Динамическая связь с исходными данными проявляется и в том, что при изменении данных не требуется заново формировать сводную таблицу. Достаточно щелкнуть в пределах таблицы правой кнопкой мыши и выбрать в контекстном меню пункт Обновить данные.
Впрочем, это не относится к часто встречающейся ситуации, когда записи базы данных добавляются или удаляются. В этом случае необходимо снова запустить мастер сводных таблиц (проще всего для этого воспользоваться кнопкой Мастер сводных таблиц на панели инструментов Сводные таблицы), щелкнуть на кнопке Назад и уточнить диапазон ячеек, включающих записи базы данных. После щелчка на кнопке Готово данные сводной таблицы будут обновлены с учетом изменения числа записей.
Сводная диаграмма представляет собой диаграмму, построенную на основе сводной таблицы. При создании сводной диаграммы соответствующая ей сводная таблица создается автоматически, даже если явного запроса на ее создание не было. Для создания сводной диаграммы по уже существующей сводной таблице проще всего щелкнуть на кнопке Мастер диаграмм на панели инструментов Сводные таблицы. Чтобы создать сводную диаграмму непосредственно на основе базы данных, надо запустить Мастер сводных таблиц командой Данные, Сводная таблица и на первом этапе работы мастера установить переключатель Сводная диаграмма. Далее щелкните на кнопке Готово.
Упр. 18. Создание сводных таблиц и диаграмм для таблицы
Показатели производства
Создайте сводную таблицу и сводные диаграммы для задачи, на листе Упр_18, описанной в Упр. 9. Преобразуйте таблицу в список.
Технология работы:
1. Выделите ячейку в пределах базы данных. Выберите команду Данные, Сводная таблица.
2. Убедитесь, что установлены переключатели в списке или базе данных Microsoft Excel и сводная таблица. Щелкните на кнопке Далее.
3. Убедитесь, что диапазон базы данных выбран правильно. Щелкните на кнопке Далее.
4. Убедитесь, что установлен переключатель Новый лист. Щелкните на кнопке Макет.
5. Перетащите кнопки в соответствующие области макета сводной таблицы: кнопку Месяц — в область Строка, кнопку План выпуска — в область Столбец, кнопку Фактически выпущено — в область Данные.
6. Кнопка в области Данные будет иметь вид Сумма по полю Фактически выпущено. Нас это устраивает. Щелкните на кнопке ОК.
7. Щелкните на кнопке Параметры. В поле Имя введите текст Показатели производства. Щелкните на кнопке ОК. Щелкните на кнопке Готово.
8. На полученной сводной таблице раскрывающие кнопки рядом с именами полей таблицы позволяют выполнить сортировку по соответствующему полю. Выберите конкретный месяц в раскрывающемся списке Месяц, чтобы увидеть данные, относящиеся к этому месяцу. Посмотрите на новый вид сводной таблицы.
9. Постройте сводные диаграммы на новых листах и переименуйте их в соответствии с названием упражнения и диаграммы (Упр_18(диаграмма 1), Упр_18(диаграмма 2) и т. д.)
VI. КОНТРОЛЬНЫЕ ЗАДАНИЯ ПО EXCEL
Все задания выполняются в одной книге Контрольная работа на отдельных листах. Каждому листу дайте имя в соответствии с номером задания. Книгу сохраните в папке Excel.
Задание № 1. Работа с вложенными функциями и графиками
Задание выполняется по-вариантно на листе с именем «Математические функции». Номер варианта выдает преподаватель.
1. На листе отобразите вид функции Y= f(x), используя программу MS Equation (Вставка, Объект, MS Equation).
2. Протабулируйте функцию f(x) на отрезке [1:10] с шагом 0,5. Для получения значений Х используйте меню Правка, Заполнить, Прогрессия – арифметическая.
3. Для вычисления значений Y используйте математические функции с относительными адресами. При заполнении значений Y воспользуйтесь автозаполнением.
4. На другом листе График постройте график функции Y= f(x) по вычисленным точкам. График должен содержать заголовок диаграммы, подписи осей, подписи данных, легенду.
| 1. |  | 22. |  |
| 2. |  | 23. |  |
| 3. |  | 24. |  |
| 4. |  | 25. |  |
| 5. |  | 26. |  |
| 6. |  | 27. |  |
| 7. |  | 28. |  |
| 8. |  | 29. |  |
| 9. |  | 30. |  |
| 10. |  | 31. |  |
| 11. |  | 32. |  |
| 12. |  | 33. |  |
| 13. |  | 34. | |
| 14. |  | 35. |  |
| 15. |  | 36. |  |
| 16. |  | 37. |  |
| 17. |  | 38. |  |
| 18. |  | 39. |  |
| 19. |  | 40. | |
| 20. | | 41. | |
| 21. | | 42. |  |
Задание № 2. Работа с логическими функциями
Задание выполняется по - вариантно, лист сохранить под именем Функция Если Номер варианта выдает преподаватель.
Вариант 1
Рабочим-сдельщикам предоставлен отпуск.
Рассчитайте указанным рабочим заработную плату за отпуск в рублях. Отпускные = з/п за отпуск + премия. Среднее количество рабочих дней месяца 25,4. З/п за отпуск рассчитывается по формуле:
| з/п за отпуск | = | ср. з/п * кол-во дней отпуска ср. кол-во раб. дней месяца |
Премия рассчитывается исходя из условия: если средняя з/п меньше 150$, то премия - 50% от средней з/п, в противном случае - 100%. В таблице приведена заработная плата в долларах. В расчётах учесть курс доллара на текущую дату.
| Курс доллара | 30,00р. | ||
| Дорожина М.Л. | Петрова И.М. | Зуева Е.П. | |
| Январь | $121,50 | $142,30 | $155,20 |
| Февраль | $122,50 | $143,30 | $156,20 |
| Март | $123,50 | $144,30 | $157,20 |
| Апрель | $124,50 | $145,30 | $158,20 |
| Май | $126,50 | $146,30 | $159,20 |
| Июнь | $127,50 | $147,30 | $160,20 |
| Июль | $128,50 | $148,30 | $161,20 |
| Август | $129,50 | $149,30 | $162,20 |
| Кол-во раб. дней, предоставленных на отпуск | |||
| Отпускные в рублях |
Вариант 2
Вычислите процент отклонения фактических затрат себестоимости товарной продукции от плановых. Воспользовавшись кнопкой Автоосумм на панели инструментов, рассчитайте суммарное значение затрат в строке Итого фабрично-заводская себестоимость. Полная себестоимость рассчитывается по формуле:
| Полная себестоимость | = | Итого фабрично-заводская себестоимость | - | Внепроизвод-ые расходы |
В таблице суммы приведены в долларах. Пересчитайте Себестоимость товарной продукции по факту в рублях с учётом курса доллара на текущую дату.
Себест. товар. прод. по факту = Себест. товар. прод. по плану + сумма
Процент отклонения вычислите по формуле:
| Процент отклонения | = | Себестоимость товара по факту (р.) Полная себестоимость | * | 100% |
Если есть экономия, то строки выделите красным цветом (используя условное форматирование).
| Калькуляционные статьи затрат | Себестоимость товарной продукции по плану | Отклонение: экономия(-), перерасход(+) | Себестоимость товарной продукции по факту (руб.) | |
| сумма | % | |||
| Сырье и основные материалы | $869 | $7 | ||
| Полуфабрикаты | $197 | -$1 | ||
| Зарплата рабочих | $324 | -$15 | ||
| Дополнит. зарплата | $32 | -$1 | ||
| Цеховые расходы | $654 | -$6 | ||
| Общезаводские расходы | $258 | -$2 | ||
| Прочие расходы | $15 | $8 | ||
| Итого фабрично-заводская себестоимость | ||||
| Внепроизводственные расходы | $136 | -$15 | ||
| Полная себестоимость | ||||
| Курс доллара | ххх руб. |
Вариант 3
Рассчитайте месячную сумму амортизации основных фондов по формуле: Ам=(Кп*А)/(100*12).
Вычислите средние и суммарные показатели, добавив нужные строки. Присвойте категории фондам по следующему правилу: если месячная сумма амортизации меньше $150, то фонды первой категории, если месячная сумма амортизации меньше или равна $180, то - фонды второй категории, иначе фонды третьей категории.
В расчётах учесть курс доллара на текущую дату.
Вариант 4
В таблице приведена информация о суммах продаж (млн.руб.) товаров магазинами фирмы Изобилие.
За проданный товар магазину выплачивается премия. Размер премии приведен в таблице. Для каждого магазина рассчитайте объем продаж за три года, величины премии и общий объем продаж с учетом премии. Вычислите также для фирмы итоговые показатели.
| магазин | директор | года | итого | премия | итого | ||||
| Старт | Ванов И.И. | ||||||||
| Ива | Ларин А.А. | ||||||||
| БКК | Сидоров А.Н. | ||||||||
| БИКов | Биков К.П. | ||||||||
| Елена | Глазов А.Г. | ||||||||
| Тамара | Гурин В.В. | ||||||||
| Свет | Морозова И.Г. | ||||||||
| КиК | Силин Р.Ф. | ||||||||
| Москва | Соев А.И. | ||||||||
| Колобок | Шубина Л.А. | ||||||||
| итого |
| сумма продаж за год | премия | |
| от(вкл.) | до | |
| 5,0% | ||
| 5,5% | ||
| 6,0% |
Вариант 5
Произведите расчет коэффициента экономичности аппарата управления предприятия по формуле Кэа=(Ан/Аф)*(Рн/Рф).
| Шифр подраздел. | Категория секретности | Число структурных подразделен. по норме (Ан) | Фактическое кол-во структурных подразделен. (Аф) | Норматив по количеству сотрудников (Рн) | Фактическ. количество сотрудник. (Рф) | Коэффициент эконом. (Кэа) |
Присвойте категорию секретности 1, если шифр подразделения начинается с нуля, в противном случае категория секретности - 2.
Вариант 6
Расчет заработной платы (ус.ед).
Районный коэффициент составляет 15% от Начислено.
Премия рассчитывается исходя из условия: если Начислено не превышает 1 тыс. ус.ед., то премия составляет 15% от Начислено и если более 1 тыс., то - 10%.
Начислено всего = Начислено + Районный коэффициент + Премия.
Облагаемая сумма = Начислено всего - Районный коэффициент.