Создание именованного блока ячеек

Именованные блоки можно создавать разными способами.

Если в качестве имени блока ячеек нужно использовать текст, расположенный сверху, снизу, слева или справа от этого блока, то:

· выделить диапазон ячеек, включая ячейки, содержимое которых будет использоваться в качестве имени (имен) блока (блоков);

· выполнить команду Создать из выделенного группы Определенные имена вкладки Формулы;

· в появившемся окне Создание имен из выделенного диапазона поставить галочку в нужном параметре (например, в строке выше, если имя блока берется из верхней ячейки) и нажать ОК.

В результате выполнения команды для приведенного на Рис. 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 с.