Пример задания

Необходимо из отчетной ведомости, рассчитанной на первом практическом занятии, по результатам работы сети торговых точек за IV квартал, подсчитать:

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

- найти тройку лучших и тройку худших реализаций по всем торговым точкам;

- среднюю суммарную выручку по каждой торговой точке за три месяца и распределить их по местам (проранжировать);

- количество торговых точек, в которых сумма реализации превышала среднюю суммарную выручку, по всем торговым точкам.

Минимальная и максимальная выручка

Введите в ячейку А9 и В9 соответственно заголовки: минимум и максимум. Для того чтобы подсчитать минимальное значение в указанном диапазоне, воспользуйтесь мастером функций, который вызывается по нажатию кнопки , расположенной в строке формул. В появившемся диалоговом окне, представленном на рисунке 17, в поле Категория укажите соответствующую категорию, в нашем случае Статистические, и выберите нужную функцию из списка, представленного в поле Выберите функцию. Нажмите кнопку ОК.

Рис. 17. Первый шаг мастера функций

На втором шаге мастера задайте аргументы функции. На рисунке 18 в поле Число1 задайте интервал ячеек В3:D6. В нашем случае этот диапазон содержит сведения о всех реализациях за квартал, кроме итоговых значений.

Рис. 18. Второй шаг мастера функций

Нажмите кнопку ОК.

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

Тройка лучших результатов

В диапазон ячеек С9:С11 проставьте места 1, 2 и 3 соответственно, а в ячейку С12 введите Лучшие. Результаты будем подсчитывать в диапазон ячеек D9:D11.

Для подсчета результатов вызовите мастер функций и выберите в категории Статистические функцию НАИБОЛЬШИЙ и нажмите кнопку ОК На втором шаге задайте аргументы функции (рис. 19).

Рис. 19. Аргументы функции НАИБОЛЬШИЙ

В качестве массива задайте тот же диапазон В3:D6, однако ссылку на него сделаете абсолютную, т.е. нажмите клавишу [F4], для появления абсолютной адресации $В$3:$D$6.

В поле К укажите на ячейку С9, которая содержит искомое место. В нашем примере ячейка С9 содержит 1.

Нажмите кнопку ОК.

Введенную в ячейку D9 функцию с помощью маркера заполнения протащите на диапазон D10:D11. Обратите внимание, что ссылка на диапазон не изменилась, а значение мест «перенастраивалось». Теперь, если Вы введете, например, в ячейку С11 значение 5 и нажмете клавишу [Enter], то значение в ячейке D11 изменится.

Аналогично найдите три самых худших результата, для чего воспользуйтесь функцией НАИМЕНЬШИЙ.

Среднее значение

Введите в ячейку G2 заголовок столбца: Среднее. Для подсчета среднего значения по строке вызовите мастер функций и выберите в категории Статистические функцию СРЗНАЧ и нажмите кнопку ОК. На втором шаге задайте аргументы функции (рис. 20).

Рис. 20. Аргументы функции СРЗНАЧ

На рисунке 20 в поле Число1 задайте интервал ячеек В3:D3. В нашем случае этот диапазон содержит сведения о реализациях за квартал по одной торговой точке. Нажмите кнопку ОК.

Введенную в ячейку G3 функцию с помощью маркера заполнения протащите на диапазон G4: G7.

Ранжирование результатов

Введите в ячейку Н2 заголовок столбца: Ранг. Для ранжирования результатов работы торговых точек за квартал вызовите мастер функций и выберите в категории Статистические функцию РАНГ и нажмите кнопку ОК. На втором шаге задайте аргументы функции (рис. 21).

Рис. 21. Аргументы функции РАНГ

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

В поле Ссылка задайте ссылку на диапазон ячеек, внутри которого будем проводить ранжирование. В нашем случае это диапазон $Е$3:$Е$6.

В поле Порядок - число, определяющее способ упорядочения. Введите 0, так как упорядочение будет по убыванию. Для сортировки по возрастанию следует ввести любое ненулевое число.

Нажмите кнопку ОК.

Введенную в ячейку Н3 функцию с помощью маркера заполнения протащите на диапазон Н4:Н6.

Количество реализаций, превышающих среднюю

Сначала в отдельной ячейке подсчитайте среднее значение всех реализаций, воспользовавшись функцией

=СРЗНАЧ(B3:D6).

Затем в мастере функций выберите в категории Статистические функцию СЧЕТЕСЛИ и нажмите кнопку ОК. На втором шаге задайте аргументы функции (рис. 22).

Рис. 22. Аргументы функции СЧЕТЕСЛИ

В поле Диапазон задайте диапазон ячеек, в котором нужно подсчитать ячейки. В нашем случае это диапазон B3:D6.

В поле Критерий — критерий отбора. Критерий отбора может содержать ссылку на ячейку, но в этом случае будет проверяться условие равенства. В нашем случае введите критерий >360, где 360 – это среднее значение реализаций.

Нажмите кнопку ОК.

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