Упражнение по Информатике Excel_5

Упражнение 5 - Присвоение имен ячейкам и диапазонам ячеек

Правила создания имен

В Excel можно назначать имена отдельным ячейкам или диапазонам ячеек, а затем использовать эти имена в формулах. Работать с именами удобнее. Имена, определенные на текущем листе, могут использоваться в любых других листах книги. Каждая книга может содержать свое собственное множество имен.

Имя – слово или строка знаков, представляющих ячейку, диапазон ячеек, формулу или константу.

Задание имен имеет несколько преимуществ:

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

когда ячейка перемещается, имя остается с ней;

имена ячеек или диапазонов можно использовать в формулах или аргументах функций;

при копировании формулы, в которой присутствует имя, достигается эффект, аналогичный абсолютной ссылке на ячейку.

В имени может быть больше одного слова. В качестве разделителей слов могут быть использованы знаки подчеркивания и точки, например: Год_2007 или Год.2007.

Правила создания имен:

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

имя может включать до 255 символов и содержать только буквы, цифры, символ точки (.), обратной косой черты и подчеркивания;

нельзя использовать пробелы, точки с запятой, восклицательный знак или другие специальные символы;

имена не должны совпадать с адресами ячеек, т.е. нельзя использовать имена, которые могут трактоваться как ссылки на ячейки;

имена не зависят от регистра символов, т.е. имя может состоять из строчных и прописных букв, но Excel их не различает;

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

В Excel существуют четыре способа задания имен ячейкам и диапазонам. Самый простой – использовать поле Имя. Поле Имя расположено слева от строки формул, и в нем отображается адрес текущей активной ячейки (рис. 5.1).

Рис. 5.1. Поле Имя

Задание 1. Присвоение имени отдельной ячейке с помощью поля Имя.

1. Откройте файл Упражнения.xlsx из своей личной папки.

2. Создайте лист с именем Упр.6.

3. Введите исходные данные, как показано на рис. 5.2.

4. В ячейке В3 введите формулу =В1*В2.

Рис.  5.2. Исходные данные – пример 1

5. Выделите ячейку B1.

6. Сделайте щелчок в поле Имя. Excel превратит это поле в стандартное поле редактирования и выделит помещенный в него адрес ячейки (это адрес текущей активной ячейки).

7. Введите присваиваемое выделенной ячейке или диапазону имя, как показано на рис. 5.2. и нажмите на клавишу Enter.

Задание 2. Присвоение имени с помощью команды присвоить имя.

1. Перейдите на вкладку Формулы.

8. Выделите ячейку В2 и выполните команду Присвоить имя , расположенную в группе команд Определенные имена. Появится диалоговое окно Создание имени (рис. 5.3).

Рис. 5.3. Диалоговое окно Создание имени

Excel автоматически использует заголовки строк и столбцов для ссылок на ячейки. Поэтому в поле Имя уже будет написано слово «Цена».

1. Подтвердите выбор, сделанный Excel, и нажмите на клавишу ОК.

2. Самостоятельно присвойте ячейке В3 соответствующее имя «Продажи», используя один из описанных выше способов.

Задание 3. Применение имен в формулах.

1. Сделайте активной ячейку В3.

9. Откройте список команд, нажимая на стрелку, расположеную рядом с командой Присвоить имя и выполните команду Применить имена (рис. 5.4).

Рис. 5.4. Выпадающее меню Присвоить имя

10. В появившемся диалоговом окне Применение имен (рис. 5.5) выделите последовательно все имена и нажмите на клавишу ОК.

Рис. 5.5. Диалоговое окно Применение имен

3. В результате формула примет конечный вид, показанный на рис. 5.6.

Рис. 5.6. Конечный вид формулы после применения имен

Задание 4. Создание имен для диапазона ячеек автоматическим способом.

1. Введите исходные данные (рис. 5.7).

11. В чейку Е4 введите формулу, показанную в строке формул (см. рис. 5.7).

12. Выделите диапазон ячеек D2:E4.

Рис.  5.7. Исходные данные – пример 2

13. Перейдите на вкладку Формулы и в разделе Определенные имена выполните команду Создать из выделенного фрагмента .

14. В появившемся диалоговом окне (рис. 5.8) нажмите на клавишу ОК, подтверждая выбор, сделанный Excel.

Рис. 5.8. Окно для создания нескольких имен одновременно

15. Выделяя по очереди ячейки E3 и E4, проверьте правильность присвоенных имен, глядя в поле Имя.

16. Выполните команду Применить имена.

17. В появившемся диалоговом окне Применение имен выделите последовательно щелчком левой кнопой мыши все вновь созданные имена, как показано на рис. 5.9.

Рис. 5.9. Выделение имен

4. Нажмите на кнопку ОК. В результате всем трем ячейкам диапазона Е2:Е4 будут одновременно присвоены имена. В этом можно будет убедиться, открыв список имен в поле Имя (рис. 5.10).

Рис. 5.10. Список имен

Задание 5. Замена имен в ссылках.

1. Дополните таблицу, как показано на рис. 5.11, введя данные за второй и третий год.

18. Выделите все ячейки диапазона D2-G4.

19. С помощью команды Создать из выделенного фрагмента присвойте имена, соответствующие заголовкам строк.

20. На запрос Excel о замене имен ответьте утвердительно.

21. Формулу, находящуюся в ячейке E4, распространите на оставшийся диапазон F4:G4, используя маркер заполнения.

22. Проверьте правильность присвоения имен, выделяя по очереди диапазоны ячеек E2:G2, E3:G3. Имя для всего диапазона выделенных ячеек вы увидите, по-прежнему, в поле Имя.

23. Выделяя по очереди ячейки E4, F4 и G4, обратите внимание, что произошла подстановка имен в формулу произведения двух соседних ячеек (рис. 5.11).

Рис. 5.11. Применение имен в формулах

Задание 6. Удаление имен.

1. Перейдите на вкладку Формулы и в группе команд Определенные имена выполните команду Диспетчер имен.

24. В появившемся диалоговом окне Диспетчер имен (рис. 5.12) выделите любое имя.

25. Выберите команду Удалить и нажмите на клавишу ОК.

В этом же окне также можно присвоить диапазону имя, используя команду Создать, или изменить его, используя команду Изменить.

Рис. 5.12. Диалоговое окно Диспетчер имен

Задание 7. Использование имен в формулах.

Имена можно вводить везде, где происходят ссылки. например, имя диапазона можно использовать в качестве аргумента функции. Имена несут также навигационные функции (функции перемещения), особенно в больших рабочих книгах или рабочих листах. Для перемещения к именованному диапазону в любом месте рабочей книги и выделения его щелкните на стрелке поля Имя и выберите имя из списка.

1. Перейдите на лист Упр.5.

26. Выделите диапазон ячеек А2:Е5.

27. Выполните команду Создать из выделенного фрагмента, затем выполните команду Применить имена.

Рис. 5.13.

В результате во всех формулах вместо ссылок на ячейки появятся имена (рис. 5.13). Excel редактирует имена таким образом, чтобы сделать их полноценными. Если имена содержат пробелы, Excel заменит пробелы знаками подчеркивания, например, Фирма_1. Если имя начинается с цифры, Excel добавит в его начало символ подчеркивания, например, 1_квартал.

28. Сохраните файл Упражнения.xlsx.

Задание 8. Самостоятельная работа.

1. Откройте файл с именем Лабораторные работы.xls и на листе Лаб_2 создайте следующую таблицу (рис. 5.14). Обрамление таблицы и форматирование текста любое.

29. В итоговой строке и столбце для подсчета сумм примените команду Сумма.

Отдел № 1: Восточный регион
Затраты в первом квартале:	Январь	Февраль	Март	1 кв.
Амортизация	750 р.	800 р.	800 р.	2 350 р.
Арендная плата	200 р.	200 р.	200 р.	600 р.
Поставки	600 р.	600 р.	600 р.	1 800 р.
Разное	100 р.	160 р.	140 р.	400 р.
Расходы по офису	200 р.	175 р.	225 р.	600 р.
Реклама	800 р.	825 р.	675 р.	2 300 р.
Страхование	110 р.	120 р.	325 р.	555 р.
Такси	325 р.	340 р.	500 р.	1 165 р.
Фонд заработной платы	11000 р.	11200 р.	12000 р.	34 200 р.
Итого:	14 085 р.	14 420 р.	15 465 р.	43 970 р.

Рис. 5.14. Исходные данные – пример 3

5. Присвойте ячейке Амортизация-1кв. имя Износ.

6. Назовите ячейку Итого-Январь именем янв_общ.

7. Присвойте имена следующим диапазонам ячеек: данным за Январь, Февраль, Март, 1кв. Имена диапазонов должны совпадать с заголовками этих столбцов.

8. Аналогичным способом назовите область ячеек, содержащую итоговые данные для столбцов Январь, Февраль, Март, 1 кв. как Итого.

9. Перейдите к ячейке с именем Износ, используя функциональную клавишу F5.

10. Используя поле Имя в строке формул переместитесь в диапазон с названием Итого.

11. Таким же образом, перейдите в поименованные области Январь и 1 кв.

12. Установите курсор в свободную ячейку, расположенную ниже исходной таблицы.

13. Выполните команду , расположенную в разделе Определенные имена вкладки Формулы (рис. 5.15).

Рис. 5.15. Выпадающее меню списка имен

14. В выпадающем меню выберите команду Вставить имена. Появится диалоговое окно Вставка имени (рис. 5.16).

15. Выполните команду Все имена. В результате будет создана таблица со списком имен всей рабочей книги.

Рис. 5.16. Диалоговое меню Вставка имени

16. Транспонируйте исходную таблицу таким образом, чтобы названия столбцов и строчек поменялись местами. Транспонированную таблицу поместите ниже исходной:

выделите исходную таблицу и скопируйте ее в буфер обмена;

установите курсор в свободное место;

в контексном меню выберите команду Специальная вставка (рис. 5.17), установите флажок Транспонировать.

Рис. 5.17. Диалоговое окно Специальная вставка 

17. Скопируйте готовую исходную таблицу в книгу Упражнения.xlsx одновременно на три последующих свободных листа (при необходимости добавьте листы в книгу). Все три таблицы должны располагаться в одних и тех же ячейках, начиная с А1.

18. Переименуйте полученные листы соответственно Отдел 1, Отдел 2, Отдел 3.

19. Измените названия таблиц на листах Отдел 2, Отдел 3 соответственно: Отдел № 2: Западный регион и Отдел № 3: Южный регион.