Добавление в список промежуточных итогов

Функции СЧЕТЕСЛИ() и СУММЕСЛИ ().

Тема 2. Использование функций для анализа списка данных Microsoft Excel

1. Функции СЧЕТЕСЛИ() и СУММЕСЛИ ().

2. Функции Работы с базой данных.

3. Использование функций категории Ссылки и массивы для анализа списка.

Для анализа данных в списке Microsoft Excel предлагает специальные функции: функции категории Ссылки и массивы, функции Работы с базой данных, статистическая функция СЧЕТЕСЛИ() и математическая функция СУММЕСЛИ (). Каждая из этих функций возвращает информацию об элементах списка, удовлетворяющих некоторым условиям.

Функции СЧЕТЕСЛИ() и СУММЕСЛИ () проще в использовании, чем другие функции, предназначенные для работы со списками. Поскольку они позволяют задавать условия непосредственно в формуле. Недостатком данных функций является возможность использования только простых условий.

Функция СЧЕТЕСЛИ() определяет количество непустых ячеек в заданном диапазоне, удовлетворяющих критерию. Синтаксис функции:

СЧЕТЕСЛИ(диапазон; критерий),

где диапазон – диапазон (при анализе данных в списках, обычно, столбец), в котором нужно посчитать количество значений, а критерий – это текстовое значение, задающее условие.

Следует помнить, что условие, выступающее критерием подсчета, применяется только к диапазону, в котором производится подсчет.

Например, функция подсчета числа сотрудников старше 25 лет (см. рис. 1.6) будет выглядеть следующим образом:

= СЧЕТЕСЛИ(H5:H17;">25")

Функция СУММЕСЛИ() суммирует значения в ячейках, удовлетворяющих заданному критерию. Функция имеет следующий синтаксис:

СУММЕСЛИ(диапазон; критерий; диапазон_суммирования),

где аргумент диапазон задает проверяемый диапазон, критерий – условие, которое должно выполняться в каждой ячейке этого диапазона, а применяемое к диапазон_суммирования – задает диапазон суммируемых значений.

Например, функция подсчета суммарного оклада, получаемого сотрудниками старше 25 лет (см. рис. 1.6) будет выглядеть следующим образом:

=СУММЕСЛИ(H5:H17;">25";G5:G17).

2.2. Функции Работы с базой данных.

Функции Работы с базой данных (Дфункции) являются аналогом «обычных» статистических функций. Различие состоит в том, что функции баз данных оперируют только с элементами диапазона, которые удовлетворяют заданным условиям. Все Дфункции имеют следующий синтаксис:

Дфункция (База_данных; поле; критерий),

где База_данных задает диапазон всего списка; поле – задает столбец, в котором производятся вычисления (суммирование, усреднение и т. д.); третий аргумент критерий задает диапазон условий.

Второй аргумент можно задавать в виде текстового значения заголовка столбца, заключенного в кавычки или порядкового номера столбца в списке.

Третий аргумент критерий задается подобно диапазону условий в расширенных фильтрах (см. рис. 1.3), при этом диапазон условий, как и при создании расширенного фильтра, необходимо задавать до создания формулы, использующей функцию баз данных.

Функции баз данных труднее использовать, чем СУММЕСЛИ() и СЧЕТЕСЛИ(), поскольку они требуют предварительного задания диапазона условий, но при этом, как уже говорилось ранее, Дфункции () позволяют задавать более сложные условия.

Рассмотрим несколько простейших функций данной категории.

Функция БДСУММ() суммирует удовлетворяющие заданным условиям числа в столбце списка. Действия этой функции аналогичны действиям функции СЧЕТЕСЛИ().

Функция ДМАКС() – возвращает наибольшее число в столбце списка, которое удовлетворяет заданным условиям.

Функция ДМИН() – возвращает наименьшее число в столбце списка или базы данных, которое удовлетворяет заданным условиям.

Функция ДСРЗНАЧ() – возвращает среднее значение выбранных записей списка. Например, в списке, представленном на рис. 2.1, для вычисления средней заработной платы сотрудников в возрасте старше 25 лет используется формула:

=ДСРЗНАЧ(A4:H17;"Оклад";A1:A2).

Рис. 2.1. Использование функции ДСРЗНАЧ()

Функция БСЧЕТА() – подсчитывает непустые ячейки в столбце списка или базы данных, которые удовлетворяют заданным условиям.

При работе с функциями баз данных следует учесть следующие замечания:

§ если необходимо найти строки, отвечающие нескольким условиям для одного столбца, то в диапазон условий нужно включить несколько столбцов с одинаковыми заголовками;

§ если результаты вычисления функции размещаются на отдельном листа, то и диапазон условий должен быть на том же листе.

2.3. Использование функций категории Ссылки и массивы
для анализа списка.

Для анализа данных в списке могут быть использованы функции категорий Ссылки и массивы, их еще называют функциями ссылки и подстановки. Эти функции используются для различного поиска значений в списках.

Часто востребованной функцией данной категории является функции ПОИСКПОЗ(),возвращающая позицию элемента диапазона, в наибольшей степени соответствующего искомому значению. Эта функция имеет следующий синтаксис:

ПОИСКПОЗ (искомое_значение; просматриваемый_диапазон; тип_сопоставления)

Аргумент искомое_значение – это исходное искомое значение, которое ищется в диапазоне просматриваемый_массив.

Аргумент тип_сопоставления определяет принцип поиска и может принимать значения –1, 0 или 1. Тип_сопоставления указывает, как Microsoft Excel сопоставляет искомое_значение со значениями просматриваемого диапазона.

Если тип_сопоставления равен 1 или опущен, функция ПОИСКПОЗ() находит в просматриваемом_диапазоне ближайшее значение, которое меньше искомого или равно искомому. При этом просматриваемый_диапазон должен быть упорядочен по возрастанию:... –2, –1, 0, 1, 2... A–Z, ЛОЖЬ, ИСТИНА (см. рис. 2.2).

Рис. 2.2. Функция ПОИСКПОЗ() определяет ближайший элемент
диапазона меньше 24

Если тип_сопоставления равен 0, функция ПОИСКПОЗ() находит первое значение, которое в точности равно аргументу искмое_значение. В этом случае просматриваемый_диапазон может быть не отсортирован.

Если тип_сопоставления равен –1, то функция ПОИСКПОЗ() находит ближайшее значение, которое больше искомого или равно искомому.При этом просматриваемый_диапазон должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z–A... 2, 1, 0, –1, –2... и так далее (см. рис. 2.3).

Рис. 2.3. Функция ПОИСКПОЗ() определяет ближайший элемент
диапазона больше 24

При работе с функцией ПОИСКПОЗ() следует обратить внимание на ряд особенностей ее использования:

§ Если функция ПОИСКПОЗ() не находит соответствующего значения, то возвращается значение ошибки #Н/Д («Неопределенные данные»).

§ При использовании функции ПОИСКПОЗ() для определения позиции текстовых строк тип сопоставления обычно задают 0.

§ Если тип_сопоставления равен 0 и искомое_значение текстовое, то оно может содержать символы шаблона (* или?).

Функция ПРОСМОТР() возвращает значение из строки, из столбца или из массива и две синтаксические формы: вектор и массив.

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

Векторная форма функции имеет следующий синтаксис:

ПРОСМОТР(искомое_значение;просматриваемый_вектор;
вектор_результатов).

Аргумент искомое_значение – это значение, котороефункция ПРОСМОТР() ищет в просматриваемом_векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой.

Аргумент просматриваемый_вектор – это диапазон, содержащий только одну строку или один столбец. Значения в аргументе просматриваемый_вектор могут быть текстами, числами или логическими значениями.

Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания:... –2, –1, 0, 1, 2..., A–Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР() может вернуть неправильный результат. Тексты в нижнем и верхнем регистре считаются эквивалентными.

Аргумент вектор_результатов – это интервал, содержащий только одну строку или один столбец, из которого будет возвращено значение. Данный диапазон должен иметь столько же элементов, что и просматриваемый_вектор.

При работе с функцией ПРОСМОТР() следует обратить внимание на ряд особенностей ее использования:

§ если функция ПРОСМОТР() не может найти искомое_значение, то найдено будет ближайшее значение, которое меньше искомого в просматриваемый_вектор;

§ если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, то функция ПРОСМОТР() возвращает значение ошибки #Н/Д.

Вторая синтаксическая форма функции ПРОСМОТР() предназначена для работы с массивами, она имеет следующий синтаксис:

ПРОСМОТР(искомое_значение;массив).

Эта форма функции ПРОСМОТР() просматривает первую строку или первый столбец массива, находит указанное значение и возвращает значение из аналогичной позиции последней строки или столбца массива. Для определения расположения сравниваемых значений (в столбце или в строке) функция использует размерности таблицы.

Эта форма функции ПРОСМОТР предназначена для совместимости с другими системами электронных таблиц. В случае необходимости осуществления такого рода поиска лучше вместо формы функции ПРОСМОТР(), предназначенной для работы с массивами, использовать более предсказуемые функции ГПР() или ВПР().

Функция ВПР() ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы (см. рис. 2.4). Функция имеет следующий синтаксис:

ВПР(искомое_значение;диапазон_списка;номер_столбца;
интервальный_просмотр)

Рис. 2.4. Использование функции ВПР() для поиска
должности Бортниковой

Аргумент искомое_значение – это значение, которое должно быть найдено в первом столбце таблицы. Искомое_значение может быть числом, ссылкой на ячейку или текстовой строкой, заключенной в двойные кавычки.

Аргумент диапазон_списка – это список, в котором осуществляется выбор данных.

Аргумент номер_столбца – это номер столбца в диапазоне списка, в котором должно быть найдено соответствующее значение. Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента диапазон_списка; если номер_столбца равен 2, то возвращается значение из второго столбца. В нашем примере (см. рис. 2.4) этот аргумент равен 6, номер столбца Должность.

Аргумент интервальный_просмотр – это логическое значение, которое определяет тип соответствия: точное или приблизительное.

· Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, то функция ВПР() ищет в первом столбце таблице точное соответствие искомому_значению. Если таковое не найдено, то возвращается значение ошибки #Н/Д. Д иапазон_списка не обязано должен быть отсортирован.

· Если аргумент интервальный_просмотр имеет значение ИСТИНА или опущен, то в случае отсутствия точного совпадения, возвращается ближайшее значение меньше, чем искомое_значение. Значения в первом столбце аргумента интервальный_просмотр должны быть расположены в возрастающем порядке, в противном случае функция ВПР может выдать неправильный результат.

Функция ГПР() идентична функции ВПР() за исключением того, что работает с вертикальными таблицами, используется в том случае, если данные расположены не в столбцах, а в строчках (см. рис. 2.5).

Рис. 2.5. Использование функции ГПР() для поиска оплаты Бортниковой,
произведенной в апреле

Функция имеет следующий синтаксис:

ГПР(искомое_значение;инфо_таблица;номер_строки;
интервальный_просмотр).

ГПР() ищет значение в верхней строке таблицы и возвращает значение в том же столбце из заданной строки таблицы или массива. Функция ГПР() используется тогда, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые значения расположены на несколько срок ниже. По умолчанию, перед использованием функции ГПР() список должен быть отсортирован.

Еще одной функцией, представляющей интерес для анализа данных в списках, является функция ИНДЕКС(). Функция ИНДЕКС() подобно функции ПРОСМОТР() имеет две формы.

Первая форма функции ИНДЕКС(), имеющая следующий синтаксис:

ИНДЕКС(список; номер_строки; номер_столбца),

возвращает значение элемента списка, заданного номером строки и номером столбца. Например, используя пример рисунка 2.4, для поиска значения из шестого столбца четвертой строки будет использована формула:

=ИНДЕКС(A2:H7;4;6),

которая вернет значение «Преподаватель».

Для поиска значения в списке можно использовать две функции одновременно: ПОИСКПОЗ(), которая определяет номер (возможно номер столбца) элемента в списке и ИНДЕКС(), которая возвращает значение элемента на пересечении заданных столбца и строки.

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

Вопросы для самоконтроля.

1. Какие функции в Microsoft Excel предназначены для анализа данных в списке?

2. Какие особенности использования функций баз данных?

3. Действия каких функций баз данных аналогичны действиям функций СУММЕСЛИ() и СЧЕТЕСЛИ()?

4. Назовите аргументы функций баз данных.

5. В чем состоит различие использования функций баз данных и аналогичных им статистических функций?

6. Использование вычисляемых и невычисляемых условий в функциях баз данных.

7. Использование логических операторов ИЛИ и И в диапазоне условий функций баз данных.

8. Использование функций категории Ссылки и массивы для поиска данных в списке.

Вопросы и задания для самостоятельной работы.

1. Сравнить использование расширенных фильтров и функций баз данных.

2. Определить разницу использования функций БСЧЕТ() и БСЧЕТА().

3. Определить назначение функции БИЗВЛЕЧЬ().

4. Создать новую книгу Задача_2, в которую скопировать таблицу Сотрудники (задание 1 лабораторной работы 1.1).

5. Отступить ниже списка строчку и добавить ячейку

Старше 33 лет

6. Используя функцию СЧЕТЕСЛИ(), рассчитать количество сотрудников, старше 33 лет.

7. На отдельном листе создать две таблицы:

Отдел	Количество		Должность	Количество	Суммарный оклад
Реализации			Менеджер
Снабжения			Начальник
Контроля			Аудитор
Итого			Итого

8. Заполнить вновь созданные таблицы:

1 шаг. С помощью функции СЧЕТЕСЛИ() определить количество сотрудников работающих в каждом отделе (здесь в качестве критерия используется относительная ссылка на ячейку с наименованием отдела).

2 шаг. С помощью функции СЧЕТЕСЛИ() определить количество сотрудников, работающих по каждой из должностей (критерий определяется аналогично шагу 1).

9. Посчитать объем выплачиваемой заработной платы (поле Суммарный оклад таблицы задания 4) по всем должностям организации.

10. Ниже списка добавить ячейку

Младше 33 лет

11. Посчитать общие затраты на выплату заработной платы сотрудникам младше 33.

12. ***Составить формулу и посчитать среднюю заработную плату сотрудников младше 33 лет, используя функции СУММЕСЛИ() и СЧЕТЕСЛИ().

13. Как вторая форма функции ПРОСМОТР() использует размерность таблицы для определения расположения сравниваемых значений?

14. Определить, какому цвету соответствуют частоты 4,91; 5,0; 7,66; 4,06, если известны нижние граничные значения цветов:

15. Используя таблицу Список сотрудников (файл Задача_2.xls), определить какой номер квартиры у сотрудников Иванова и Иванченко:

1 шаг. Ниже списка добавить две ячейки с соответствующими фамилиями.

16. Используя таблицу Продажа товаров, определить информацию, записанную в 22 строке и втором столбце.

17. Добавить в книгу Продажа товаров новый лист, на который поместить следующую информацию

Испания	авиа
Италия	ж/д
Дания	авто
Ирландия	авто
Венесуэла	авиа
Австрия	авиа
Бразилия	авиа
Канада	авиа
Норвегия	авто
Польша	ж/д
Португалия	ж/д
США	авиа
Великобритания	ж/д
Финляндия	авто
Франция	ж/д
Швейцария	ж/д
Швеция	ж/д

18. К списку листа Продажа товаров добавить столбец Доставка.

19. Используя функции ИНДЕКС() и ПОИСКПОЗ() заполнить новый столбец информацией о способах доставки заказов.

20. Определить несколько возможных способов использования формул поиска элементов в столбцах списка.

21. Привести примеры использования второй ссылочной формы функции ИНДЕКС().

Задания лабораторной работы 2.1.

1. Скопировать в отдельную книгу Лабораторная_работа_2_1 таблицу Сотрудники (задание 1 лабораторной работы 1.1).

2. Рассчитать премию, начисляемую сотрудникам организации.

1 шаг. Добавить на новый лист представленную ниже шкалу начисления премии за выслугу лет:

Размер премии за выслугу лет в % к окладу

№	Стаж, менее	Премия, %

2 шаг. К списку сотрудников добавить новый столбец – Премия. Рассчитать премию за выслугу лет для каждого сотрудника, используя (обычную) логическую функцию Если().

3. Добавить следующие ячейки на лист, содержащий список, или на любой другой лист:

Минимальная премия по отделу снабжения
Maксимальная премия по отделу реализации
Средняя премия по отделу снабжения
Сумма премии по отделу реализации

4. Используя функции баз данных, заполнить ячейки таблицы (диапазоны условий не удалять, оставить для проверки).

5. ***Сформировать приказ о премии за выслугу лет: скрыть ненужные столбцы, связать скопированные в Excel столбцы с документом Word

Приказ №

Выплатить премию за выслугу лет за 2005 год
следующим сотрудникам:

6. Скопировать в новую книгу список из книги Продажа товаров.

7. Добавить лист, диапазоны условий, а результаты всех последующих вычислений разместить на новом листе.

8. Используя функцию БСЧЕТА(), подсчитать количество заказов, отправленных в Великобританию.

9. Используя функцию БСЧЕТА(), подсчитать количество заказов, выше средней стоимости, отправленных в Великобританию.

10. Подсчитать суммарный заказ хлебобулочных изделий, проданных в США.

11. Подсчитать количество заказов на хлебобулочные изделия и молочные продукты.

12. Рассчитать количество заказов стоимостью выше среднего значения, но не больше 1 000 000 грн.

13. ***Найти минимальную стоимость заказа, поступившего из Великобритании, в первом квартале 1995 года.

1 шаг. Для преобразования даты, представленной в виде текста, в числовой формат, использовать функцию ДАТАЗНАЧ().

14. ***Найти минимальную стоимость заказа, поступившего из Великобритании или Швеции.

Задания лабораторной работы 2.2.

1. Файл Лабораторная работа_3 сохранить в своей папке под именем Лабораторная работа 2_2_фамилия.

2. Добавить новый лист, на который пометить следующую таблицу:

3. В столбец Дата внести следующие даты: 27.12.1994, 25.01.95, 13.04.95, 19.07.95.

4. Используя функции ссылок и массивов, заполнить остальные столбцы таблицы.

5. Воспользовавшись данными листа Скидка, внести в поля Размер скидки_1 и Размер скидки_2 процент скидки по каждому заказу. Использовать два разных способа (разные функции).

6. Для расчета общей стоимости из стоимости заказа вычесть размер скидки.

7. *** Используя таблицу расстояний от Харькова до населенных пунктов (лист Расстояния), рассчитать возможные поездки автотуристов в зависимости от марки автомобиля (расхода бензина на 100 км), марки (стоимости) бензина и наличия денежных средств. Ответ сформулировать в каждой ячейке в виде «Возможна поездка в …» (см. рис. 2.6).

Рис. 2.6. Расчет возможности поездки автотуристов в зависимости от марки автомобиля (расхода бензина на 100 км), марки (стоимости) бензина и наличия денежных средств

Тема 3. Структуризация таблиц.
Использование промежуточных итогов для анализа списков

1. Добавление в список промежуточных итогов.

2. Работа со структурированными документами.

3. Редактирование промежуточных итогов.

Удобным средством анализа данных в списках Microsoft Excel является структурирование таблиц и вычисление промежуточных итогов. Excel позволяет придать списку определенную структуру, имеющую до восьми уровней иерархии (cм. рис. 3.1). Структуризация таблицы методом автоматического подведения итогов облегчает передвижение по строкам данных и используется для группировки данных по определенным параметрам в целях проведения анализа, построения диаграмм или создания отчетов.

Рис. 3.1. Структуризация списка с использованием группировки по столбцам Код отдела, Должность и итоговой функции СУММА по полю Оклад

Для вычисления итогов на уровне каждой группы элементов списка можно использовать промежуточные итоги. При выводе промежуточных итогов Microsoft Excel всегда создает структуру списка. Вычислять итоги на уровне групп можно на основе различных итоговых функций: Сумм, Количество, Среднее, Минимум, Максимум, Произведение, Отклонение, Дисперсия.

Предположим, что по списку, изображенному на рисунке 1.6, требуется узнать суммарные затраты на выплату заработной платы в каждом отделе. Для того чтобы добавить в список промежуточные итоги, необходимо:

1. Определить параметры (столбцы), по которым будет проводиться подведение итогов, в нашем примере это столбец Код отдела.

2. Отсортировать список по тому столбцу, по которому будут подводиться промежуточные итоги (столбец Код отдела).

3. С помощью команды Итоги меню Данные открыть диалоговое окно Промежуточные итоги (см. рис. 3.2).

Рис. 3.2. Заполнение диалогового окна Промежуточные итоги

4. Заполнить окно диалога Промежуточные итоги.

1 шаг. В поле «При каждом изменении в:» указать наименование столбца, при изменении значения в котором требуется подводить итоги (Код отдела).

2 шаг. В свернутом списке поля «Операция:» выбрать итоговую функцию, используемую для формирования итогов (Сумма); следует помнить, что все итоговые функции, кроме Количество, применяются только к столбцам, содержащим числовые данные.

3 шаг. В поле «Добавить итог по:» указать столбцы, в которых выполняются действия по формированию итогов (Оклад).

4 шаг. Флажок Итоги под данными необходимо установить, если итоговые функции необходимо поместить ниже каждой группы, а общие итоги внизу списка; по умолчанию общий итог разместить над списком, а итоговые функции – над каждой группой.

5 шаг. Флажок Конец страницы между группами необходимо установить, если требуется печатать каждой группы на отдельной странице.

6 шаг. Флажок Заменять текущие итоги установить при создании нового итогового вычисления, если итоги по данному списку уже создавались.

После нажатия клавиши ОК в документе автоматически формируются промежуточные итоги по сгруппированным данным (см. рис. 3.3).

Рис. 3.3. Структуризация списка с использованием группировки по столбцу Код отдела и итоговой функции СУММА по полю Оклад

Дата добавления: 2014-11-29