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

ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
background image

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

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

 

2017

 

 

11

 

 

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

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

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

(рис. 1.9). 

 

 

Рис. 1.9 

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

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

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

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

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

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

 

Рис. 1.10 


background image

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

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

 

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) 

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


background image

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

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

 

2017

 

 

13

 

 

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

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

 

Рис. 1.12 

1.2. 

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

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

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

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

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

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

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

2

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

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

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

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

другие).  

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

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

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

 

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

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

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

                                                           

2

 

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

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


background image

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

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

 

2017

 

 

14

 

 

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

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

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

1.13 

и 1.14). 

 

  

 

  

Рис. 1.13   

 

 

 

 

Рис. 1.14 

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

 

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

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

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

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

 

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

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

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

 

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

 

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

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

 

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

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

товаров. 


background image

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

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

 

2017

 

 

15

 

 

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

области 

(ячейка 

А1), 

в 

которой 

требуется 

разместить 

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

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

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

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

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

 

Рис. 1.15 

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

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

B2:C10 

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

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

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

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

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

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