Создание именованного блока ячеек
Именованные блоки можно создавать разными способами.
Если в качестве имени блока ячеек нужно использовать текст, расположенный сверху, снизу, слева или справа от этого блока, то:
· выделить диапазон ячеек, включая ячейки, содержимое которых будет использоваться в качестве имени (имен) блока (блоков);
· выполнить команду Создать из выделенного группы Определенные имена вкладки Формулы;
· в появившемся окне Создание имен из выделенного диапазона поставить галочку в нужном параметре (например, в строке выше, если имя блока берется из верхней ячейки) и нажать ОК.
В результате выполнения команды для приведенного на Рис. 7 примера будет создан именованный блок ячеек Наименование_товара.
Рис. 7. Вид окна создания имени из выделенного фрагмента.
Если в качестве имени блока ячеек нужно задать произвольное имя или именованный блок содержит более одной строки или столбца, то:
· выделить диапазон ячеек, которым надо присвоить имя;
· выполнить команду Присвоить имя группы Определенные имена вкладки Формулы;
· в появившемся окне Создание имени ввести имя создаваемого блока ячеек в строке Имя (автоматически выводится имя, соответствующее содержимому ячейки, расположенной над выделенным блоком, или имя из первой выделенной ячейки, в строке Формула выводится ссылка на диапазон выделенных ячеек) и выбрать область действия присвоенного имени (Книга – все листы рабочей книги, или только конкретный лист);
· нажать кнопку ОК.
Для удаления ненужного имени блока выполнить следующие действия:
· выполнить команду Диспетчер имен группы Определенные имена вкладки Формулы.
· в появившемся окне Диспетчер имен выделить имя, которое нужно удалить и нажать кнопку Удалить.
Практическое задание
1. Открыть рабочую книгу Продажи, созданную в задании 4.1.
2. При помощи копирования создать на новом листе справочник товаров, представленный на Рис. 5.
3. Дать этому листу имя Справочник товаров.
4. Столбцу Наименование товара дать имя Наименование_товара.
5. Удалить данные из первых трех столбцов на листе Продажи (столбцы Код товара, Наименование товара, Цена).
6. Выделить строку 11 и вставить 5 новых строк, выполнив 5 раз команду Вставить ячейки группы Ячейки вкладки Главная.
7. Выделить блок ячеек В4:В15, в которых будет организован выбор наименования товара из раскрывающегося списка.
8. Выполнить команду Проверка данных группы Работа с данными вкладки Данные для создания раскрывающегося списка.
9. Выбрать из списка любые названия товаров (в любом порядке, причем названия могут повторяться).
10. Активизировать ячейку А4 и вызвать функцию ВПР для автоматического определения кода товара. Функция будет иметь вид:
=ВПР(B4;'Справочник товаров'!$A$2:$C$8;2;0)
11. Скопировать эту формулу при помощи автозаполнения в остальные строки таблицы (строки 5-15).
12. Активизировать ячейку С4 и вызвать функцию ВПР для автоматического определения цены товара. Функция будет иметь вид:
=ВПР(B4;'Справочник товаров'!$A$2:$C$8;3;0)
13. Скопировать эту формулу при помощи автозаполнения в остальные строки таблицы (строки 5-15).
14. Ввести любое количество проданного товара в строки 11 – 15 столбца Продано (ед.).
15. Удалить данные о проценте скидки.
16. Активизировать ячейку G4 и вызвать функцию ЕСЛИ для определения величины скидки по правилу:
· при покупке более трех единиц каждого наименования товара предоставляется скидка 7% от первоначальной суммы, т.е. % скидки - 7%;
· при покупке не более 3 единиц товара % скидки - 0%.
Функция ЕСЛИ будет иметь вид: =ЕСЛИ(D4>3;7%;0%)
17. На новом листе создать таблицу, представленную на Рис. 8.
Рис. 8. Таблица для подсчета суммарных данных.
Для этого:
· Скопировать с листа Справочник товаров столбец с наименованиями товаров.
· Активизировать ячейку В2 и подсчитать количество проданного товара при помощи функции СУММЕСЛИ. Функция будет иметь вид:
=СУММЕСЛИ(Продажи!$B$4:$B$15;A2;Продажи!$D$4:$D$15)
· Активизировать ячейку С2 и подсчитать общую сумму продаж товара при помощи функции СУММЕСЛИ.
· Скопировать формулы из ячеек В2 и С2 в остальные строки таблицы.
18. В любых ячейках найти максимальное количество проданного товара при помощи функции МАКС и минимальную сумму продаж товара при помощи функции МИН.
19. Сохранить рабочую книгу с именем Продажи_2.
Задание для самостоятельной работы
1. Создать таблицу Календарь (Рис. 9) на первом листе рабочей книги.
2. Создать таблицу Табель (Рис. 10) на втором листе рабочей книги:
3. Организовать выбор месяца, за который составляется табель, из списка на основе Календаря с листа "Календарь". Месяц выбирать в ячейке W3.
Рис. 9. Справочник Календарь.
Рис. 10. Таблица Табель.
4. Создать формулы для автоматического выбора значений "Всего раб. часов" (в ячейке С5) и "Всего часов" (в ячейке С6) из таблицы Календарь в соответствии с выбранным месяцем.
5. Рассчитать значения остальных столбцов таблицы:
· Итого рабочих дней вычисляется при помощи функции СЧЕТ.
· Выходные, больничные и отпуск вычисляются при помощи функции СЧЕТЕСЛИ.
· Итого раб. часов вычисляется при помощи функции СУММ.
· Последний коэффициент рассчитывается по формуле: Итого раб. часов/Всего раб. часов.
6. Дать листу имя Табель.
7. Сохранить рабочую книгу с именем Сам_задание_4_2.
Литература
1. Уокенбах Дж., Microsoft Excel 2010. Библия пользователя: Пер. с англ.– М.: ООО «И.Д. Вильямс», 2014 г. — 912 с.