Построение формул для анализа информации

Таблицы, содержащие данные, их структура

РЕАЛИЗАЦИЯ БАЗЫ ДАННЫХ В MS EXCEL

База данных содержит 10 таблиц, расположенных на листах «Поставщики», «Поставки», «Продажи 1», «Продажи 2», «Остатки», «Заказы», «Итоги», «Анализ объемов продаж».

Таблица «Поставщики» (рис. 1) на листе 2 хранит информацию обо всех поставщиках магазина, с которым сотрудничает он.

Рисунок 1 - Таблица «Поставщики»

Таблица «Поставки» (рис.2) на листе 3 содержит информацию о поставляемой продукции в магазин сотовых телефонов.

Рисунок 2 - Таблица «Поставки»

Таблица «Продажи 1» (рис.3) на листе 4 содержит информацию о продажах отдела 1.

Рисунок 3 - Таблица «Продажи 1»

Таблица «Продажи 2» (рис.4) на листе 5 содержит информацию о продажах отдела 2.

Рисунок 4 - Таблица «Продажи 2»

Таблица «Остатки» (рис.5) на листе 6 содержит информацию об остатках товара в магазине.

Рисунок 5 - Таблица «Остатки»

Таблица «Заказы» (рис.6) на листе 7 содержит информацию об оформлении товара для заказа в магазин.

Рисунок 6 - Таблица «Заказы»

Таблица «Итоги» (рис.7) на листе 8 содержит информацию об итогах продаж по отделу 1, по отделу 2 и по магазину в целом, о менеджерах для премирования.

Рисунок 7 - Таблица «Итоги»

Таблица «Анализ объемов продаж» (рис. 8) на листе 10 содержит информацию о продажах по дням недели и за месяц по отделам 1 и 2 магазина сотовых телефонов.

Рисунок 8 - Таблица «Анализ объемов продаж»

Для подсчета проданных товаров используется функция БДСУММ, которая представлена на рисунке 9.

Описание: суммирует числа в поле (столбце) записей списка или базы данных, которые удовлетворяют заданным условиям.

Синтаксис: БДСУММ (база_данных; поле; условия).

Аргумент функции БДСУММ:

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

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

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

Рисунок 9 - Функция БДСУММ

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

Описание: функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, функция СУММ(A1:A5) вычисляет сумму всех чисел в ячейках от A1 до A5. Другой пример: функция СУММ(A1, A3, A5) вычисляет сумму чисел в ячейках A1, A3 и A5.

Синтаксис: СУММ (число 1, [число 2], …]).

Аргументы функции СУММ:

- число1. Обязательный аргумент. Числовой аргумент, который является первым слагаемым;

- число2,... Необязательный аргумент. От 2 до 255 числовых аргументов, которые являются слагаемыми.

Рисунок 10 - Функция СУММ

Для проверки условия данных использовалась функция ЕСЛИ, которая представлена на рисунке 11.

Описание: функция ЕСЛИ возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Синтаксис: ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь).

Аргументы функции ЕСЛИ:

- лог_выражение - любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, «A10=100» - логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, а в противном случае - значение ЛОЖЬ. Этот аргумент может использоваться в любом операторе сравнения;

- значение_если_истина - значение, которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА. Например, если данный аргумент - строка «В пределах бюджета», а аргумент «лог_выражение» имеет значение ИСТИНА, то функция ЕСЛИ отобразит текст «В пределах бюджета». Если аргумент «лог_выражение» имеет значение ИСТИНА, а аргумент «значение_если_истина» не задан, возвращается значение 0 (ноль). Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. Аргумент «значение_если_истина» может быть формулой;

- значение_если_ложь - значение, которое возвращается, если «лог_выражение» имеет значение ЛОЖЬ. Например, если данный аргумент - строка «Превышение бюджета», а аргумент «лог_выражение» имеет значение ЛОЖЬ, то функция ЕСЛИ отобразит текст «Превышение бюджета». Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» опущен (т. е. после аргумента «значение_если_истина» отсутствует точка с запятой), то возвращается логическое значение ЛОЖЬ. Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» пуст (т. е. после аргумента «значение_если_истина» стоит точка с запятой, а за ней - закрывающая скобка), то возвращается значение 0 (ноль). Аргумент «значение_если_ложь» может быть формулой.

-

Рисунок 11 - Функция ЕСЛИ

Для подсчета финансовых итогов магазина использовалась функция ВПР, которая представлена на рисунке 12.

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

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

Аргумент функции ВПР:

- искомое_значение. Обязательный. Значение, которое должно быть найдено в первом столбце таблицы или диапазона. Аргумент искомое_значение может быть значением или ссылкой. Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента таблица, функция ВПР возвращает значение ошибки #Н/Д;

- таблица. Обязательный. Диапазон ячеек, содержащий данные. Можно использовать ссылку на диапазон (например, A2:D8) или имя диапазона. Значения в первом столбце аргумента таблица - это значения, в которых выполняется поиск аргумента искомое_значение. Эти значения могут быть текстовыми, числовыми или логическими. Текстовые значения в нижнем и верхнем регистре считаются эквивалентными;

- номер_столбца. Обязательный. Номер столбца в аргументе таблица, из которого возвращается совпадающее значение. Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента таблица; если номер_столбца равен 2, - значение из второго столбца аргумента таблица и т. д.

Если значение аргумента номер_столбца:

а) меньше 1, функция ВПР возвращает значение ошибки #ЗНАЧ!;

б) больше, чем число столбцов в аргументе таблица, функция ВПР возвращает значение ошибки #ССЫЛ!;

- интервальный_просмотр. Необязательный. Логическое значение, определяющее, какое совпадение должна найти функция ВПР - точное или приблизительное.

Если аргумент интервальный_просмотр имеет значение ИСТИНА или опущен, то возвращается точное или приблизительное совпадение. Если точное совпадение не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение.

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

Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, сортировка значений в первом столбце аргумента таблица необязательна.

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

Рисунок 12 - Функции ВПР и МАКС

Для получения максимального числа использовалась функция МАКС, которая представлена на рисунке 12.

Описание: функция МАКС возвращает наибольшее значение из набора значений.

Синтаксис: МАКС (число1; число2;...).

Замечания:

- аргументы должны быть либо числами, либо содержащими числа именами, массивами или ссылками;

- учитываются логические значения и текстовые представления чисел, которые введены непосредственно в список аргументов;

- если аргумент является массивом или ссылкой, то в нем учитываются только числа или ссылки. Пустые ячейки, логические значения и текст в массиве или ссылке игнорируются;

- если аргументы не содержат чисел, функция МАКС возвращает значение 0 (ноль);

- аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, приводят в возникновению ошибок;

- если в ссылку в качестве части вычислений необходимо добавить логические значения и текстовые представления, воспользуйтесь функцией МАКСА.

Для подсчета анализа продаж по дням недели и за месяц отдела 1 и отдела 2 использовались сводные таблицы, которые предназначены для отчета работы магазина сотовых телефонов (рис. 13).

Рисунок 13 - Сводные таблицы

Дата добавления: 2015-05-09