Файл: Современные информационные.pdf

104
Рис. 2.23
В строке Логическое выражение введите условие D4 > E4.
В строке Значение_если_истина задайте формулу расчета надбавки, если условие выполняется: C4*0,15.
В строке Значение_если_ложь укажите значение 0, поскольку в этом случае (невыполнение условия) надбавка не начисляется.
Нажмите ОК. Произведите автокопирование формулы из ячейки F4 на весь столбец таблицы.
Задание 2.15. С помощью функции СЧЕТ и СЧЕТЗ подсчитать общее количество сотрудников, а также число сотрудников, сдавших взносы (см. рис. 2.24).

105
Рис. 2.24
Создайте и отформатируйте таблицу по образцу.
Для выполнения подсчетов в ячейках B18 и B19 используйте следующие определения функций:
Функция СЧЕТ определяет количество ячеек в заданном диапазоне, которые содержат числа, в том числе даты и формулы, возвращающие числа. Эта функция учитывает только числовые значения в диапазоне и игнорирует пустые ячейки и ячейки, содержащие текстовые, логические или ошибочные значения.
Функция СЧЕТЗ служит для определения количества непустых ячеек в заданном диапазоне. Она подсчитывает число всех ячеек, которые содержат какие-либо данные (любого типа).
Функции СЧЕТ и СЧЕТЗ находятся в категории Статистические.
Задание 2.16.
С помощью функции СЧЁТЕСЛИ определить численность личного состава подразделения (см. рис. 2.25) по категориям.

106
Рис. 2.25
Функция СЧЁТЕСЛИ находятся в категории Статистические. Она подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию. Для работы функции необходимо указать диапазон
(интервал) проверяемых ячеек и критерий в форме числа, выражения или текста, который определяет, ячейки с каким содержанием надо подсчитывать (см. рис. 2.26).
Рис. 2.26
Для выполнения задания в ячейке B11 функция должна выглядеть следующим образом:
= СЧЁТЕСЛИ (B3:B8;«сержант»).

107
Это говорит о том, что мы будем подсчитывать, сколько раз в указанном диапазоне ячеек встретилось слово «сержант».
Аналогично в ячейке B12 создайте функцию для подсчета числа рядовых.
Задание 2.17. Создать таблицу выплат по кредитным картам трех крупнейших систем за год в млрд долларов США (см. рис. 2.27).
Рис. 2.27
Добавив соответствующие столбцы и строки, найдите:
1. для каждого вида кредитных карт:

максимальную выплату;

среднюю выплату;

минимальную выплату;

для скольких месяцев выплата составила более 50 млрд долларов;
2. для каждого месяца:

максимальную выплату;

среднюю выплату;

минимальную выплату;

108

для скольких типов карт выплата составила более 50 млрд долларов;
3. за год:

максимальную выплату;

минимальную выплату.
Задание 2.18. Создать таблицу, содержащую следующие данные
(см. рис. 2.28). Произвести расчеты в незаполненных строках и столбцах.
Рис. 2.28
Для расчета строки «Сумма за месяц»и столбца «Всего за квартал» использовать автосуммирование.
Для расчета строки «Средн. значение за месяц»и столбца
«Отчисления в СФ» использовать функции. Значения столбца
«Отчисления в СФ» должны быть рассчитаны по следующему правилу:
8 % от общей суммы за квартал, если она больше 600;
6 % от общей суммы за квартал в противном случае.
Задание 2.19. Администрация предприятия решила доплачивать
10 % надбавки к окладу работнику, если он проработал на предприятии более пяти лет. Создайте таблицу расчета надбавки.

109
Создать таблицу по образцу (см. рис. 2.29). С помощью функции рассчитать надбавку за стаж. Произвести расчет итоговой заработной платы с учетом оклада и надбавки.
Рис. 2.29
К ячейкам, содержащим результаты расчетов, применить рублевый денежный формат без десятичных знаков.
Задание 2.20. Создать таблицу результатов экзаменационной сессии
(см. рис. 20.30).
Рис. 2.30
Добавьте в таблицу данные еще 10 студентов.
С помощью функций заполните столбцы E – I.

110
Добавьте в таблицу еще один столбец: «Количество несданных предметов». Заполните его с помощью функции СЧИТАТЬПУСТОТЫ
(категория Статистические).
Задание 2.21. Создать таблицу расчета комиссионных от продажи домов по образцу (см. рис. 2.31).
Рис. 2.31
В третьем столбце таблицы составить формулу для вычисления комиссионных из расчета 1,5 % от стоимости дома.
С помощью функций в ячейках С11, С12, С13 и С14 рассчитать требуемые показатели.

111
4. Сортировка и фильтрация данных в таблице
Как правило, список состоит из столбцов (полей) и строк (записей).
Каждый столбец должен содержать однотипные данные. Список не должен содержать пустых строк или столбцов. Если в списке присутствуют заголовки, то они должны быть отформатированы иначе, чем остальные элементы списка.
Основные инструменты работы со списками – это
сортировка и
фильтрация.
Сортировка списков
Сортировка представляет собой упорядочивание списка по определенным параметрам и применяется для облегчения поиска информации. После сортировки записи списка отображаются в ином порядке, в соответствии со значениями определенных столбцов (например, можно задать упорядочивание фамилий по алфавиту, цены – по возрастанию или убыванию и т. д.).
Чтобы задать параметры сортировки, нужно выделить список, нажать кнопку Сортировка и фильтр, которая находится на вкладке меню Главная в группе Редактирование, и выбрать пункт
Настраиваемая сортировка. В открывшемся окне необходимо указать поле, по которому будет производиться сортировка списка, и выбрать ее порядок.
Можно организовать сложную сортировку, которая подразумевает упорядочивание данных по нескольким полям. Добавлять поля можно при помощи кнопки Добавить уровень в диалоговом окне настраиваемой сортировки.

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

113
В появившемся окне Расширенный фильтр задайте параметры
Исходный диапазон (это сам список) и Диапазон условий (это скопированная шапка списка с заданными под ней критериями фильтрации). При необходимости укажите диапазон ячеек, в котором должны отображаться результаты работы фильтра. Нажмите ОК.
Задание 2.22. В новой книге Excel на Листе1 создать список по образцу (см. рис. 2.33):
Рис. 2.33 1. Отсортировать список по алфавиту.
Для этого скопируйте список на Лист2. На этом листе выделите список целиком вместе с заголовками столбцов. Нажмите кнопку
Сортировка и фильтр на вкладке меню Главная в группе
Редактирование, выберите пункт Настраиваемая сортировка....
В открывшемся окне установите флажок Мои данные содержат
заголовки и установите следующие критерии сортировки: Сортировать
по: ФИО;Порядок: От А до Я (см. рис. 2.34). Нажмите ОК.

114
Рис. 2.34
В итоге список будет отсортирован по первому столбцу в алфавитном порядке (см. рис. 2.35).
Рис. 2.35 2.
Выполнить сложную сортировку по нескольким полям: 1 –
Оклад, 2 – Ф. И. О., 3 – Возраст.
Для этого скопируйте список на Лист3. На этом листе выделите список целиком вместе с заголовками столбцов. Нажмите кнопку
Сортировка и фильтр на вкладке меню Главная в группе
Редактирование, выберите пункт Настраиваемая сортировка....

115
В открывшемся окне установите флажок Мои данные содержат
заголовки. Для первого уровня сортировки установите: Сортировать по:
Оклад;Порядок: По убыванию. Затем нажмите кнопку Добавить уровень
и задайте второй уровень сортировки: Затем по: ФИО; Порядок: От А до
Я. Добавьте ещё один уровень сортировки и для него установите: Затем
по: Возраст; Порядок: По убыванию (см. рис. 2.36). Нажмите ОК.
Рис. 2.36
В итоге список будет отсортирован согласно заданным параметрам сложной сортировки (см. рис. 2.37).

116
Рис. 2.37
Перемещать уровни сортировки можно при помощи кнопок
Вверх
и Вниз
3.
Выбрать из списка работников бухгалтерии.
Для этого скопируйте исходный список с Листа1 на Лист4. На этом листе выделите список целиком вместе с заголовками столбцов. Нажмите кнопку Сортировка и фильтр на вкладке меню Главная в группе
Редактирование, выберите пункт Фильтр.
В заголовках столбцов таблицы появились кнопки со стрелками.
Нажмите такую кнопку в заголовке «Отдел», установите флажок в пункте
«бухг» (см. рис 2.38). Нажмите ОК.

117
Рис. 2.38
В результате в списке останутся только строки, соответствующие заданному условию.
Можно формировать и более сложные условия отбора, задавая различные условия и диапазоны фильтрации, в том числе и в нескольких полях.
4.
Выбрать из списка работников с окладом более 25, но менее
40 тысяч, при этом в возрасте от 20 до 30 лет включительно.
Для этого скопируйте исходный список с Листа1 на Лист5. На этом листе выделите список целиком вместе с заголовками столбцов. Нажмите кнопку Сортировка и фильтр на вкладке меню Главная в группе
Редактирование, выберите пункт Фильтр.
Откройте кнопку со стрелкой в заголовке «Оклад», выберите пункт
Числовые фильтры. В открывшемся контекстном меню выберите пункт

118
Настраиваемый фильтр, задайте условия отбора по окладу и нажмите
ОК.
Затем откройте кнопку со стрелкой в заголовке «Возраст», выберите пункт Числовые фильтры. В открывшемся контекстном меню выберите пункт Настраиваемый фильтр, задайте условия отбора по возрасту и нажмите ОК.
5.
С помощью расширенного фильтра выбрать из списка работников моложе 30 лет.
Для этого скопируйте исходный список с Листа1 на Лист6. На этом листе скопируйте шапку списка и вставьте ее ниже имеющейся таблицы, на любое свободное место. Под заголовком «Возраст» новой шапки указать условие <30 (см. рис. 2.39).
Это означает, что из исходного списка будут отобраны только те строки, в которых возраст соответствует заданному условию.
Рис. 2.39
Для применения фильтра выделите основной список целиком вместе с заголовками столбцов. Нажмите кнопку Дополнительно в группе
Сортировка и фильтр вкладки меню Данные.

119
В появившемся окне Расширенный фильтр задайте параметры
Исходный диапазон (должен совпадать с исходным списком, в нашем примере это диапазон ячеек A1:D9) и Диапазон условий (должен соответствовать строкам ниже списка, которые содержат условия отбора, в нашем случае это диапазон ячеек A12:D13 ). Пусть результаты фильтрации будут отображены не на месте исходного списка, а в другой области листа.
Для этого установите переключатель в позицию скопировать результат в
другое место, щелкните мышью в поле Поместить результат в
диапазон: и затем выделите на рабочем листе ячейку, начиная с которой должны отображаться результаты применения фильтра (см. рис. 2.40).
Нажмите ОК.
Рис. 2.40

120
Результаты фильтрации появятся в новом списке (см. рис. 2.41).
Рис. 2.41
Расширенный фильтр удобно использовать в случаях, когда результат отбора нужно поместить в другой диапазон ячеек, отдельно от основного списка.
Задание 2.23. В новой книге Excel на Листе1 создать и оформить таблицу по образцу(см. рис. 2.42).
Рис. 2.42

121 1.
Вычислите сумму по столбцу «Площадь» (в ячейке С13) и по столбцу «Население» (в ячейке D13).
2.
Для каждой страны рассчитайте значения в столбцах
«Плотность населения» и «Доля в % от всего населения Земли» следующим образом:
Плотность населения, чел/км
2
= Население / Площадь;
Доля в % от всего населения Земли = Население страны / Население
всего мира.
Для вычисления доли используйте формулу с абсолютной ссылкой.
После расчета придайте значениям этого столбца процентный формат.
3.
В ячейке E13 с помощью функции вычислите среднюю плотность населения для перечисленных в таблице стран.
4.
Уменьшите разрядность вычисленных значений до двух десятичных знаков.
5.
Добавьте в рабочую книгу листы, чтобы получилось всего 7, и скопируйте полученную таблицу с Листа1 на все остальные листы (со 2-го по 7-й).
6.
На первом рабочем листе выделите всю таблицу и скопируйте ее ниже на этом же листе еще 4 раза.
7.
Выполните сортировку списков на первом листе (сортировка не должна включать строки «Сумма» и «Весь мир»):

во втором экземпляре таблицы – по данным последнего столбца «Доля в % от всего населения Земли» по убыванию;

в третьем экземпляре таблицы расположите страны по численности населения по возрастанию;

в четвертом экземпляре таблицы выполните сортировку по данным столбца «Плотность населения» по убыванию;