Первый пример

Перейдите на Лист2. Переименуйте его в «Отчетная ведомость по магазинам». Все упражнения в данного раздела лабораторной работе выполняйте на этом листе.

Сначала обсудим, как создать отчетную ведомость о результатах работы сети магазинов, приведенную на рис. 8.3.

В ячейку Е4 введем формулу:

=СУММ(В4:D4),которую с помощью маркера заполнения протащим на диапазон Е4:Е9.

В ячейку В 10 введем формулу:

=СУММ(В4:В9),которую протащим на диапазон В10:Е10.

В ячейку G4 введем формулу:

=СРЗНАЧ(В4:D4), которую протащим на диапазон G4:G9.

В ячейку Н4 введем формулу:

=Е4/$Е$10, которую протащим на диапазон Н4:Н9. После чего диапазону ячеек H4:H9 назначим процентный формат с помощью кнопки на панели инструментов: .

Рис. 8.3. Отчетная ведомость о работе сети магазинов за июнь – август.

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

Для ввода в формулу абсолютного адреса ячейки достаточно после ввода ее относительного адреса нажать клавишу <F4>. Если бы в ячейку Н4 была введена формула =Е4/Е10, то ее протаскивание на ячейки Н5:Н9 привело бы к неверному результату.

Присвоение ячейке имени с помощью команды вкладка Формулы - Определенные имена - Присвоить имя дает другой способ абсолютной адресации ячейки. Например, если бы ячейке Е10 было присвоено имя «Итого», то в ячейку Н4 можно было бы ввести формулу:

=Е4/Итого

которую затем протаскиваем на диапазон Н4:Н9.

Присвойте имя Итого ячейке Е10 и заново введите формулу в ячейку Н4, затем скопируйте ее с помощью автозаполнения в ячейки Н5:Н9.

Для нахождения места магазина по объему продаж введем в ячейку F4 формулу:

=РАНГ(Е4;$Е$4:$Е$9),

которую протаскиваем на диапазон F4:F9.

С помощью функции ЧАСТОТА подсчитаем для данного множества суммарных выручек магазинов, сколько значений попадает в интервалы от 0 до 1000, от 1001 до 1100, от 1101 до 1200 и свыше 1201 млн. руб. С этой целью в диапазон ячеек I4:I7 введем верхние границы этих интервалов: 1000, 1100 и 1200, соответственно, а в диапазон ячеек J4:J7 (т. е. надо выделить этот диапазон) введем формулу:

{=ЧАСТОТА(Е4:E9;I4:I7)}

Фигурные скобочки не вводите вручную. После того как Вы введете функцию и ее аргументы, нажмите одновременно три клавиши: Ctrl+Shift+Enter и скобки появятся автоматически.

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

Данная формула выведет в ячейку J4, сколько значений находится в интервале от 0 до 1000, в ячейку J5 – от 1001 до 1100, в ячейку J6 – от 1101 до 1200, в ячейку J7 – сколько значений будет не меньше 1201.

Функция ЧАСТОТА возвращает распределение частот в виде вертикального массива. Для данного множества значений и данного множества карманов (интервалов, в математическом смысле) частотное распределение подсчитывает, сколько исходных значений попадает в каждый интервал.

Синтаксис: ЧАСТОТА(массив_данных; массив_карманов).

Аргументы:

массив_данных – массив или ссылка на множество данных, для которых вычисляются частоты; если массив_данных не содержит значений, то функция ЧАСТОТА возвращает массив нулей;

массив_карманов – массив или ссылка на множество интервалов, в которые группируются значения аргумента массив_данных; если массив_карманов не содержит значений, то функция ЧАСТОТА возвращает количество элементов в аргументе массив данных.

Частоты можно также вычислить, воспользовавшись диалоговым окном Анализ данных, которое открывается командой Анализ данных (вкладка Данные, группа Анализ данных)

Средство анализа данных является одной из надстроек Excel. Если команда Анализ данных отсутствует, то для ее установки нужно выполнить следующее: кнопка Office – Параметры Excel – Надстройки; выбрать в списке Надстройки пункт Пакет анализа и щелкнуть по кнопке Перейти. Откроется окно Надстройки. Отметить флажком надстройку Пакет анализа.

Выполните команду Анализ данных – Гистограмма.

После выбора пункта Гистограмма в диалоговом окне Анализ данных откроется диалоговое окно Гистограмма (рис. 8.4).

Рис. 8.4. Диалоговое окно Гистограмма.

В поле Входной интервал введем диапазон Е4:Е9, по которому строим гистограмму. В поле Интервал карманов введем диапазон I4:I6 со значениями верхних границ интервалов. Гистограмма строится на новом рабочем листе или на текущем листе с указанием диапазона ячеек для результата. В данном случае в поле ввода Выходной интервал введем диапазон L4:L7. На рис. 8.5 приведен результат построения гистограммы.

Рис. 8.5. Результат построения гистограммы.