Файл: Методичка ИТЭ_19_06_17.pdf

«Информационные технологии в экономике и управлении». Ахметова М.И., Крутова А.В.                               

Кафедра «Экономика и финансы», ПНИПУ

2017

11

Если все введено верно в ячейке С2 должна отобразиться группа 

I.  Далее  необходимо  протянуть  эту  формулу  для  всех  банков  в 

диапазоне C2:C11. В результате каждому баку присвоена своя группа 

(рис. 1.9). 

Рис. 1.9 

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

помощью  функции  СЧЕТЕСЛИ.  Для  этого  необходимо  создать 

столбец «Количество банков» и в ячейку J3 ввести формулу: 

=СЧЁТЕСЛИ($C$2:$C$11;G3) 

Протяните  её  на  остальные  интервалы.  У  вас  получится 

количество банков, входящих в каждую группу (рис. 1.10). 

Рис. 1.10 

«Информационные технологии в экономике и управлении». Ахметова М.И., Крутова А.В.                               

Кафедра «Экономика и финансы», ПНИПУ

2017

12

Тренинг 1.3. Применение функции СУММЕСЛИ 

Продолжим  решение  предыдущей  задачи.  После  того,  как  мы 

нашли  количество  банков,  входящих  в  каждую  группу,  рассчитаем 

суммарную прибыль. Для этого создаем следующий  столбец «Сумма 

прибыли» и в ячейку K3 вводим формулу: 

=СУММЕСЛИ($C$2:$C$11;G3;$B$2:$B$11) 

Протяните  её  на  остальные  интервалы.  У  вас  получится  сумма 

прибыли по каждой группе банков (рис. 1.11). 

Рис. 1.11 

Далее  убедитесь,  что  группировка  выполнена  верно,  для  этого 

необходимо  просуммировать  общую  прибыль  по  четырем  группам 

банков  (ячейки  K3:K6),  а  также  годовую  прибыль  по  десяти  банкам 

(ячейки  B2:B11)  и  сравнить  их.  Группировка  выполнена  правильно, 

если результаты расчетов по каждому столбцу совпали. 

Домашнее задание 1.2. Применение функции СЧЕТЕСЛИ и 

СУММЕСЛИ 

По  данным  домашней  задачи  (смотрите  домашнее  задание  1.1) 

найдите,  применяя  логические  функций  СЧЕТЕСЛИ  и  СУММЕСЛИ, 

«Информационные технологии в экономике и управлении». Ахметова М.И., Крутова А.В.                               

Кафедра «Экономика и финансы», ПНИПУ

2017

13

общее  количество  человек  в  каждой  группе  и  сумму  оплаты, 

полученную с каждой группы обучающихся (рис. 1.12). 

Рис. 1.12 

1.2. 

Составление консолидированных отчетов 

Консолидация  собирает  воедино  данные  таблиц  отдельных 

листов,  суммирует  их  и  помещает  в  указанный  рабочий  лист.  Эта 

функция  Excel  может  быть  полезной  при  составлении  отчетов  о 

продажах,  отчетов  о  закупках,  от  разных  филиалов,  магазинов, 

торговых точек, торговых представителей и т.п. В консолидации могут 

участвовать  до  255  областей-источников

2

. 

Функция  консолидация 

также  может  выполнять  объединение  данных  разных  рабочих  книг,  а 

также  кроме  суммирования  «консолидация»  может  выполнять  и 

другие  функции  (произведение,  вычисление  среднего  значения  и 

другие).  

Команда  «Консолидация»  находится  на  вкладке  «Данные»  в 

группе  «Работа  с  данными».  В  следующем  тренинге  мы  составим 

отчет о продажах фирмы, имеющей два филиала. 

Тренинг 1.4. Составление консолидированного отчета на 

основе таблиц одной рабочей книги 

Откройте новую книгу и назовите ее «Консолидация». 

2

Ахметова  С.Г.  Информационные  технологии  управления:  Учебное  пособие.  –  Пермь:  Изд-во  Перм.  Гос. 

Техн. Ун-т, 2010. – 166 с. 

«Информационные технологии в экономике и управлении». Ахметова М.И., Крутова А.В.                               

Кафедра «Экономика и финансы», ПНИПУ

2017

14

Присвойте  имя  первому  рабочему  листу  «отчет»,  второму  – 

«филиал 1», третьему – «филиал 2».  

Введите исходные данные по первому и второму филиалам (рис. 

1.13 

и 1.14). 

Рис. 1.13   

Рис. 1.14 

Настройте данные для консолидации: 



Убедитесь, что все диапазоны данных представлены в формате 

списка:  первая  строка  каждого  столбца  содержит  подпись, 

остальные  строки —  однотипные  данные,  пустые  строки  или 

столбцы в списке отсутствуют.  



Разместите  каждый  диапазон  на  отдельном  листе.  Не 

помещайте диапазоны на лист, на котором должна выполняться 

консолидация.  



Убедитесь, что макеты всех диапазонов совпадают. 



Убедитесь,  что  подписи  столбцов  или  строк,  которые  требуется 

объединить, совпадают с учетом регистра букв. 



Убедитесь, что количество пробелов в наименовании товаров по 

каждому  филиалу  совпадает,  и  нет  ошибок  в  наименовании 

товаров. 

«Информационные технологии в экономике и управлении». Ахметова М.И., Крутова А.В.                               

Кафедра «Экономика и финансы», ПНИПУ

2017

15

На  новом  листе  «Отчет»  щелкните  на  левый  верхний  угол 

области 

(ячейка 

А1), 

в 

которой 

требуется 

разместить 

консолидированные данные.  

На вкладке Данные выберите команду Консолидация.  

Выберите  из  раскрывающегося  списка  Функция  функцию, 

которую  требуется  использовать  для  консолидации  данных,  в  нашем 

случае это «СУММА» (рис. 1.15).  

Рис. 1.15 

Щелкните поле Ссылка, откройте лист «филиал 1», содержащий 

первый  диапазон  данных  для  консолидации,  введите  этот  диапазон 

B2:C10 

и нажмите кнопку Добавить. Повторите этот шаг для второго 

диапазона  на  листе  «филиал  2».  Если  у  вас  есть  еще  филиалы,  то 

повторите этот шаг для всех диапазонов.  

Если  таблицу  консолидации  требуется  обновлять  автоматически 

при  каждом  изменении  данных  в  каком-либо  исходном  диапазоне,  а 

позже  точно  не  потребуется  изменять  или  добавлять  диапазоны