Подведение промежуточных итогов

ОК

Расчет в таблицах и основные функции Excel

Расчет в таблицах осуществляется с помощью введенных пользователем формул (рис.3). При этом Необходимо помнить правило: ввод формул начинается со знака равенства (=).

Рис. 3. Пример ввода формул в таблицу Excel.

Кроме использования формул результат можно получить с помощью более трехсот функций Excel. Все они распределены по категориям: финансовые, дата и время, математические, статистические, ссылки и массивы, работа с базой данных, текстовые, логические и некоторые другие. Ввод функции в ячейку таблицы осуществляется, например, с помощью мастера функций, окно которого открывается последовательностью команд: ВСТАВКА – ФУНКЦИЯ (кнопка ).

Рассмотрим примеры использования основных функций.

Пример 1. Определить общее количество преступлений за первое полугодие. Определение суммы чисел в диапазоне ячеек d6:d23 с помощью функции =СУММ(…)осуществляется следующим образом (рис.4):

1. Курсор в ячейку результата (d24);

2. Вызвать мастер функций (кнопка );

3. Категория МАТЕМАТИЧЕСКИЕ;

4. Функция =СУММ(D6:D23)

Рис. 4. Пример ввода функции =СУММ(…).

Пример 2.Определить общее количество преступлений за первое полугодие по каждому району отдельно, используя функцию =СУММЕСЛИ(…).Решение задачи осуществляется следующим образом (рис.5):

1. Создать таблицу в диапазоне G5:I8 (рис.5);

2. Курсор в ячейку результата (H6);

3. Вызвать мастер функций (кнопка );

4. Категория МАТЕМАТИЧЕСКИЕ;

5. Функция =СУММЕСЛИ(…);

6. В окне ввода аргументов функции =СУММЕСЛИ(…):

a. в поле Диапазон вводим диапазон проверки условия (столбец таблицы - Район): С6:С23;

b. в поле Критерий вводим адрес ячейки, содержащей ключ поиска информации для суммирования (название конкретного района), в нашем случае – G6;

c. в поле Диапазон_суммирования вводим диапазон ячеек D6:D23, значение которых необходимо просуммировать (столбец таблицы «Количество преступлений, ед.»);

7. ОК

Рис. 5. Пример ввода функции =СУММЕСЛИ(…).

В результате в ячейке H6 получим функцию:

=СУММЕСЛИ(C6:C23;G6;D6:D23)

Пример 3.Определить средний процент раскрываемости преступлений за первое полугодие по всем районам. Для этого используется функция =СРЗНАЧ(…).На рис. 6 приведен пример ее использования.

Определение среднего значения чисел в диапазоне ячеек Е6:Е23 с помощью функции =СРЗНАЧ(…)осуществляется следующим образом (рис.6):

1. Курсор в ячейку результата (Е24);

2. Вызвать мастер функций (кнопка );

3. Категория СТАТИСТИЧЕСКИЕ;

4. Функция =СРЗНАЧ(Е6:Е23);

5. ОК

Рис. 6. Пример ввода функции =СРЗНАЧ(…).

Пример 4.Определить количество наблюдений (строк) в документе «Сводный отчет» с помощью функции =СЧЕТ(…)(рис.7).Для этого:

1. Курсор в ячейку результата (D25);

2. Вызвать мастер функций (кнопка );

3. Категория СТАТИСТИЧЕСКИЕ;

4. Функция =СЧЕТ(D6:D23);

5. ОК

Рис. 7. Пример ввода функции =СЧЕТ(…).

Пример 5.Определить число случаев за первое полугодие (отраженных в отчете), когда процент раскрываемости был менее 90 % (рис.8).Для этого используем функции =СЧЕТЕСЛИ(…):

1. Курсор в ячейку результата (Е25);

2. Вызвать мастер функций (кнопка );

3. Категория СТАТИСТИЧЕСКИЕ;

4. Функция =СЧЕТЕСЛИ(…);

5. В окне ввода аргументов функции =СЧЕТЕСЛИ:

a. в поле Диапазон вводим диапазон: Е6:Е23;

b. в поле Критерий:<90;

6. ОК

Рис. 8. Пример ввода функции =СЧЕТЕСЛИ(…).

Пример 6.Определить средний процент раскрываемости за первое полугодие по каждому району. Для этого необходимо определить суммарный процент раскрываемости по определенному району и разделить его на количество наблюдений по этому району в отчете, то есть, например для Краснополянского района, формула имеет вид (рис. 9):

=СУММЕСЛИ(C6:C23;G6;D6:D23)/СЧЁТЕСЛИ(C6:C23;G6)

Для остальных районов вводятся аналогичные функции:

- для Пырьевского: =СУММЕСЛИ(C6:C23;G7;D6:D23)/СЧЁТЕСЛИ(C6:C23;G7);

- для Октябрьского: =СУММЕСЛИ(C6:C23;G8;D6:D23)/СЧЁТЕСЛИ(C6:C23;G8).

Рис. 9 Пример использования в формуле нескольких функций.

Пример 7.Определить максимальный и минимальный процент раскрываемости за первое полугодие, используя функции =МАКС(Е6:Е23) и =МИН(Е6:Е23). Результат ввода функций показан на рис.10.

Рис. 10. Пример ввода функций =МАКС(…) и =МИН(…).

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

Пример 8.Определить средний уровень преступности и средний процент раскрываемости преступлений за каждый месяц первого полугодия по всем районам. Для этого необходимо реализовать следующую последовательность действий:

1.Выделить таблицу, включая «шапку»;

2.Команда: ДАННЫЕ – ИТОГИ (рис. 11);

3.Определить параметры подведения итогов:

a. При каждом изменении в: Месяц;

b. Операция: Среднее;

c. Добавить итоги по: Количество преступлений, ед.; Процент раскрываемости, %;