Заполнить столбец таблицы с названиями типов технических устройств (под заголовком "Total"), установив связь между двумя таблицами по названиям технических устройств. Для этого выполнить следующие действия:
- Активировать первую ячейку столбца верхней части таблицы с названиями технических устройств порта
- Набрать с клавиатуры знак "="
- Щелкнуть встретившееся в ячейке название устройства № 1 "Центробежный насос GRUNDFOS CR 15" столбца "Портовые устройства" листа "Ремонт портовых устройств"
Пример из строки формул: ='ремонт портовых устройств'!C40
- По аналогии заполнить весь данный столбец таблицы названиями технических устройств (названия устройств в ячейках столбца должны быть заданы автоматически как ссылки на их имена в общем списке компаний столбца "Портовые устройства") из листа "Ремонт портовых устройств"
11. Вычислить общее количество устройств типа "Центробежный насос GRUNDFOS CR 15", находящихся в ремонте 12.1.2008, т.е. заполнить первую ячейку строки для устройства типа "Центробежный насос GRUNDFOS CR 15". Для этого необходимо выполнить следующие действия:
- Активировать данную ячейку первой строки
- Установить курсор в строку формул и набрать с клавиатуры знак "=".
- Из раскрывающегося списка функций слева в верхней части окна выбрать математическую функцию СУММЕСЛИ (на ленте вкладка "Формулы", диалоговое окно "Библиотека функций", кнопка "Математические", функция "СУММЕСЛИ").
- Если в раскрывшемся списке ее нет, то нужно выбрать "Другие функции", категория " Математические", функция СУММЕСЛИ.
- В окне функции СУММЕСЛИ установить курсор в строку аргумента функции "Критерий", щёлкнуть по ячейке с названием устройства "Центробежный насос GRUNDFOS CR 15" и применить смешанную адресацию
- Переустановить курсор в строку аргумента "Диапазон" и выделить на листе "Ремонт портовых устройств" все значения столбца "Портовые устройства" с названиями устройств, используя смешанную адресацию
- Затем в окне функции СУММЕСЛИ переустановить курсор в строку аргумента "Диапазон_суммирования", выделить на листе "Ремонт портовых устройств" все ячейки таблицы, содержащие данные по нахождению устройств в капитальном ремонте (с расставленными единицами), и применить смешанную адресацию
Пример из строки формул:
=СУММЕСЛИ('ремонт портовых устройств'!$C$2:$C$46;$B3;'ремонт портовых устройств'!D$2:K$46)
- Распространить данную формулу на всю таблицу
12. Заполнить строку "Total" верхней части таблицы. Для этого выполнить следующие действия:
- Активировать первую ячейку строки
- Вычислить общее количество устройств, находящихся в капитальном ремонте, для всех типов с помощью математической функции СУММ (на ленте вкладка "Формулы", диалоговое окно "Библиотека функций", кнопка "Математические", функция "СУММ").
Пример из строки формул: =СУММ(C3:C9)
- Распространить формулу на всю строку "Total":
13. Отформатировать итоговую таблицу.
14. Сохранить работу, просмотреть и проверить результаты. Образец заполненной итоговой таблицы на листе "Всего":
15. В больших таблицах MS Excel с данными целесообразно использовать функцию "Закрепление областей". Для этого необходимо выполнить следующие действия:
- Активировать ячейку (установить курсор) первую ячейку строки "Total" с суммой (в созданной таблице ячейка С2 с числом 28)
- Выбрать на ленте вкладку "Вид", диалоговое окно "Окно", кнопку "Закрепить области
- Используя маркеры линеек прокрутки (горизонтальной и вертикальной), изменить границы видимости таблицы
- Для большей наглядности целесообразно применение функции "Разделить". Для этого необходимо выбрать на ленте вкладку "Вид", диалоговое окно "Окно", кнопку "Разделить"
- Используя маркеры линеек прокрутки (горизонтальной и вертикальной) изменить границы видимости таблицы
16. Отменить разделение таблицы, снова нажав кнопку "Разделить"
17. Вставить в рабочую книгу новый лист, назвать "Отчет"
18. Выделить в итоговой таблице (лист "Всего") диапазон данных с количеством устройств, находящихся в капитальном ремонте на начало каждого месяца и выбрать команду "Копировать":
19. Перейти в лист "Отчет", активировать любую пустую ячейку и выбрать команду "Вставить". В результате получится таблица с нулевыми значениями данных на начало каждого месяца:
20. Обнуление произошло, т.к. при перемещении и копировании ячеек стандартным способом учитывается вся содержащаяся в них информация. Это относится как к формулам, так и к параметрам форматирования. Для решения вышеописанной задачи копирования данных в лист "Отчет" в MS Excel 2007 предусмотрены возможности "Специальной вставки". Для этого необходимо выполнить следующие действия:
- Удалить вставленные данные на листе "Отчет"
- Снова выделить в итоговой таблице (лист "Всего") диапазон данных с количеством устройств, находящихся в капитальном ремонте на начало каждого месяца и выбрать команду "Копировать"
- Перейти в лист "Отчет", активировать любую пустую ячейку, правой клавишей мыши вызвать контекстное меню и выбрать команду "Специальная вставка"
- Включить в диалоговом окне опцию "Значения и форматы чисел" и нажать
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.
