Лабораторные работы
Для выполнения лабораторных работ рекомендуется использовать MS Excel 2007, для других версий название команд и функций может быть немного другим. Рассчитаны на слушателей, имеющих базовый навык работы в Excel.
Отчет по выполнению лабораторных работ предоставляется в виде файлов MS Excel 2007 (97-2003) по каждому заданию и файлов с пояснениями к решению MS Word 2007 (97-2003).
Лабораторная работа 1
Имена диапазонов
В Excel есть возможность присваивать пользователем свои имена ячейкам, диапазонам ячеек, константам и формулам.
Правила присваивания имен ячейкам и диапазонам:
1. Имена диапазонов не могут быть числами. Имя должно начинаться с буквы, обратной косой черты (\) или символа подчеркивания (_).
2. В имени можно использовать только буквы, цифры, обратную косую черту и символ подчеркивания.
3. Нельзя использовать имена, которые могут трактоваться как ссылки на ячейки (А1, С4).
4. В качестве имен могут использоваться одиночные буквы за исключением букв R, C.
5. Имена могут состоять из нескольких слов, тогда в формуле при вызове имени вместо пробела ставится знак нижнего подчеркивания. Если имя начинается с числа, то перед ним тоже ставится знак нижнего подчеркивания.
Пример:
имя в ячейке – Налоговая ставка, имя в формуле – Налоговая_ставка
имя в ячейке – 2009 год, имя в формуле – _2009_год
Задание 1. Создание имен диапазона
1. Открываем файл 1_1.xls. В файле указана статистика выезда российских граждан с целью туризма, по странам.
2.
 |
Выделяем диапазон ячеек В4:C33, выбираем во вкладке Формулы/Определенные имена команду Создать из выделенного фрагмента, в диалоговом окне Создать имена из значений отмечаем в столбце слева (см. рисунок 1).
3. Теперь, к значениям ячеек C4:C33 мы сможем обращаться по именам указанным в B4:B33. Узнаем, сколько раз граждане РФ посетили Турцию, Грецию и Египет. В ячейку C35 вводим формулу (см. рисунок 2):
=Египет+Турция+Греция
Рисунок 2 – Использование имен ячеек в формулах
Задание 2. Удаление (изменение) имен диапазон
1. Выделяем диапазон ячеек В4:C33, выбираем во вкладке Формулы/Определенные имена команду Диспетчер имен ( см. рисунок 3). Можно удалять или изменять выборочно, чтобы удалить все, щелкните на первую строчку, а затем, пролистав список, и зажав клавишу Shift на последнюю, нажмите кнопку удалить.
2. Формула в ячейке C35 перестанет вычисляться. Заново назначьте имена см. задание 1.
Рисунок 3 – Удаление имен диапазона ячеек
Задание 3. Имена диапазонов, основанные на заголовках таблиц
1. Откройте файл 1_3.xls. В файле указана статистика выезда российских граждан с целью туризма по странам за период 2005-2009 гг. Сохраните файл с именем 1_3_1.xls.
2. Выделите ячейки C4:C34, затем выберете во вкладке Формулы/Определенные имена команду Создать из выделенного фрагмента, в диалоговом окне Создать имена из значений отметьте в строке выше (см. рисунок 4).
Рисунок 4
3. В ячейку С35 введите формулу:
=СУММ(_2009_год)
Для вставки имен можно воспользоваться командой Использовать в формуле (см. рисунок 5).
Рисунок 5
4. Для назначения имени диапазону D5:D34 используем второй способ. Выделите диапазон D5:D34. Выберете во вкладке Формулы/Определенные имена команду Присвоить имя, в диалоговом окне в поле Имя уже будет имя сформированное из шапки таблицы – _2008_год (см. рисунок 6). В этом же диалоговом окне можно задать и область применения имени Книга, или Лист1, Лист2 и тп. (в данном примере не меняйте или поменяйте на Лист1) и написать примечание к имени.
Рисунок 6
5. Подсчитайте граждан выехавших за границу за 2008 год, аналогично пункту 3 задания 3.
6. Третий способ назначения имен ячейкам. Выделите диапазон E5:E34. В название ячеек рядом со строкой формул введите _2007_год и нажмите клавишу Enter (см. рисунок 7).
Рисунок 7
7. Назначьте имена и для остальных столбцов, используя второй, третий способ. Подсчитайте количество граждан выехавших за 2007, 2006 и 2005 года.
Лабораторная работа 2