Краткое описание пользовательского интерфейса Excel 2007

Оглавление

Введение. 4

1. Краткое описание пользовательского интерфейса Excel 2007. 5

2. Операции с рабочим листом.. 7

3. Структура рабочего листа. 9

4. Выделение ячеек. 10

5. Ввод данных. 11

6. Создание формул. 14

7. Имена. 18

Практическая работа 1. 19

8. Функции в формулах. 20

9. Редактирование рабочего листа. 23

10. Форматирование рабочего листа. 29

Практическая работа 2. 36

Практическая работа 3. 37

Практическая работа 4. 38

11. Формулы массивов. 38

Практическая работа 5. 42

12. Создание диаграмм.. 44

13. Редактирование диаграмм.. 47

14. Форматирование диаграмм.. 51

Практическая работа 6. 53

15. Анализ данных. 56

Практическая работа 7. 59

16. Решение задач оптимизации средствами Microsoft Excel 62

Практическая работа 8. 64

17. Работа с базами данных в Microsoft Excel 67

Практическая работа 9. 78

18. Создание сводных данных. 80

Практическая работа 10. 91

19. Статистические расчеты средствами Excel 94

Практическая работа 11. 99

20. Финансовые расчеты средствами Excel 104

Практическая работа 12. 108

Введение

Практические работы по табличному процессору Excel 2007, входящему в интегрированный пакет Microsoft Office 2007, предназначены для студентов экономических специальностей.

В методических указаниях кратко изложен материал, необходимый для работы с табличным процессором:

- ввод, редактирование и форматирование данных;

- создание формул, в том числе с использованием встроенных функций;

- создание, редактирование и форматирование диаграмм;

- анализ данных с помощью таблиц подстановки, подбора параметра и диспетчера сценариев;

- операции с базами данных: сортировка, поиск, фильтрация данных;

- создание сводных данных: подведение промежуточных и общих итогов, создание сводных таблиц, консолидация данных;

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

- решение задач оптимизации с помощью надстройки Поиск решения;

- статистические расчеты средствами Excel;

- финансовые расчеты средствами Excel.

Практикум включает 12 работ для освоения изложенного материала.

Краткое описание пользовательского интерфейса Excel 2007

В Excel 2007 используется контекстно-зависимая ленточная система.

Под строкой заголовка окна программы располагается строка с названиями вкладок. При выборе вкладки разворачивается лента команд данной вкладки. Каждая команда представлена пиктограммой и названием. Команды объединены в группы. Названия групп отображаются внизу ленты. Если установить указатель мыши на пиктограмме или названии команды, появится всплывающая подсказка с комментарием и указанием «быстрых» клавиш для обращения к команде.

При выделении объектов, созданных с помощью команд вкладки Вставка (таких как таблицы, рисунки, диаграммы) появляются дополнительные ленточные вкладки, содержащие команды для работы с этими объектами.

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

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

Типы ленточных команд. В Excel 2007 используются команды следующих типов:

- простая команда: обращение к ней сразу приводит к выполнению операции;

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

- комбинация простой команды с раскрывающимся списком;

- команда-переключатель: обращение к такой команде устанавливает некоторый параметр, повторное обращение – снимает эту установку;

- команда-флажок: включает и выключает некоторый режим;

- команда-счетчик ‑ такая команда имеет кнопку с двумя разнонаправленными стрелками: щелчок по стрелке, направленной вверх, увеличивает, а по стрелке, направленной вниз, уменьшает значение некоторого числового параметра.

Контекстные меню содержат команды, которые могут быть выполнены в данный момент с выделенным объектом. Открываются щелчком по объекту правой кнопкой мыши. Кроме списка команд контекстного меню выводится мини-панель инструментов, содержащая наиболее часто используемые команды вкладки Главная (если эти команды могут быть выполнены в данный момент).

Панель быстрого доступа содержит кнопки для доступа к наиболее часто используемым командам. Располагается в левой части строки заголовка. По умолчанию на панели быстрого доступа содержатся три команды: Сохранить, Отменить и Вернуть. Но можно добавить и другие команды, щелкнув по кнопке Настройка панели быстрого доступа (кнопка со стрелкой справа от панели).

В левом углу строки заголовка располагается кнопка Office, открывающая доступ к командам для операций с документами, таких как открытие, создание, сохранение, печать, закрытие и др. Здесь же список последних по времени открытия документов, а также кнопки для открытия окна параметров работы с Excel и выхода из Excel.

Диалоговые окна. Группы команд, имеющие диалоговые окна, снабжены пиктограммой с маленькой стрелкой, направленной по диагонали из левого верхнего угла в правый нижний. Щелчок по этой пиктограмме открывает диалоговое окно.

В Excel 2007 используются диалоговые окна двух типов:

- модальные: когда открыто такое окно, никакие операции в рабочей книге невозможны; установки, сделанные в таком окне, начинают действовать только после щелчка по кнопке OK или нажатия клавиши Enter; при щелчке по кнопке Отмена или по кнопке закрытия окна в строке заголовка все сделанные установки отменяются;

- немодальные – незакрывающиеся окна: установки, сделанные в таких окнах, начинают действовать сразу; работу в Excel можно продолжать без закрытия окна; такие окна вместо кнопки OK имеют кнопку Закрыть.

Вкладки в диалоговых окнах. Вкладки – это страницы диалоговых окон. Использование вкладок дает возможность сделать много установок в одном диалоговом окне. В Excel 2007 используются диалоговые окна с вкладками двух типов:

- корешки с названиями вкладок в окнах первого типа расположены в верхней части окна под строкой заголовка и напоминают ярлычки блокнота;

- корешки вкладок в окнах второго типа расположены в левой части окна в виде списка.

Перемещение по вкладкам диалогового окна выполняется одним из следующих способов:

- щелчком мышью по корешку;

- комбинацией клавиш Ctrl+ PgUp, Ctrl + PgDn;

- комбинации клавиши Alt и буквы, подчеркнутой в названии вкладки.

Перемещение по элементам диалогового окна выполняется одним из следующих способов:

- щелчком мышью по элементу;

- комбинации клавиши Alt и буквы, подчеркнутой в названии элемента;

- клавишами Tabи Shift + Tab.

Операции с рабочим листом

Рабочая книга в Microsoft Excel представляет собой файл, используемый для обработки и хранения данных. Стандартное расширение файлов Excel 2007 .xlsx.

Рабочая книга состоит из листов, представленных в виде рабочих листов или листов диаграмм. Количество листов новой книги устанавливается на вкладке Основные в диалоговом окне, которое открывается щелчком по кнопке Office,затем – по кнопке Параметры Excel.

Листам присваиваются стандартные имена: Лист1, Лист2, …, Диаграмма1, Диаграмма2, … . Эти имена записаны на ярлычках, расположенных в нижней части окна, в той же строке, что и горизонтальная полоса прокрутки (рис. 1). Ярлычок активного листа выделен полужирным шрифтом. Чтобы сменить активный лист, надо щелкнуть по ярлычку нужного листа. Для перемещения по листам с помощью клавиатуры используются комбинации клавиш Ctrl+ PgUp, Ctrl + PgDn.

Рис. 1

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

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

Выделение листов

Выделив несколько листов, можно работать с ними одновременно (удалять, вводить одинаковые данные, форматировать и т. д.).

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

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

Все листы можно выделить с помощью команды Выделить все листы из контекстного меню ярлычков.

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

Вставка новых листов

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

I способ:

1. Выделить лист, перед которым нужно вставить новый; для вставки несколько листов, выделить их соответствующее количество.

2. На вкладке Главная в группе Ячейки выбрать команду Вставить.

3. В открывшемся списке выбрать команду Вставить лист.

II способ:

1. В контекстном меню ярлычка выбрать команду Вставка.

2. В открывшемся диалоговом окне выбрать пиктограмму Лист.

III способ:

1. Выделить лист или несколько листов, перед которыми нужно вставить новые

2. Нажать комбинацию клавиш Shift + F11.

IV способ: щелкнуть по кнопке Вставить лист, расположенной правее последнего ярлычка. При этом новый лист вставится после последнего листа.

Удаление листов

Вставку новых листов можно выполнить двумя способами.

I способ:

1. Выделить листы, которые нужно удалить.

2. На вкладке Главная в группе Ячейки выбрать команду Удалить.

3. В открывшемся списке выбрать команду Удалить лист.

II способ:

1. Выделить листы, которые нужно удалить.

2. В контекстном меню ярлычка выбрать команду Удалить.

Переименование листов

Переименовать лист можно одним из трех способов.

I способ:

1. Выделить лист, который нужно переименовать.

2. На вкладке Главная в группе Ячейки выбрать команду Формат.

3. В открывшемся списке выбрать команду Переименовать лист.

4. Ввести новое имя и нажать клавишу Enter.

II способ:

1. Выделить лист, который нужно переименовать.

2. В контекстном меню ярлычка выбрать команду Переименовать.

3. Ввести новое имя и нажать клавишу Enter.

III способ: дважды щелкнуть по ярлычку листа, который нужно переименовать, ввести новое имя и нажать клавишу Enter.

Перемещение и копирование листов

Чтобы переместить или скопировать листы, используя меню, надо:

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

2. Выделить листы, который нужно переместить или скопировать.

3. На вкладке Главная в группе Ячейки выбрать команду Формат.

4. В открывшемся списке выбрать команду Переместить или скопировать лист.

5. В диалоговом окне выбрать нужную рабочую книгу и лист, перед которым надо вставить перемещаемые или копируемые листы.

6. Для копирования листов включить режим Создать копию.

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

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

Копирование с помощью мыши отличается от перемещения тем, что выполняется при нажатой клавише Ctrl.

Структура рабочего листа

Рабочий лист представляет собой сетку из строк и столбцов (рис. 2).

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

Строки расположены по горизонтали и обозначаются номерами, написанными слева. Эти номера называются заголовками строк.

Пересечения столбцов и строк образуют ячейки. Каждая ячейка имеет адрес (или ссылку), складывающийся из заголовков столбца и строки, на пересечении которых она находится, например, D4.

Одна из ячеек является активной. Активная ячейка выделяется толстой рамкой. В активную ячейку вводится информация с клавиатуры. Заголовки строки и столбца, на пересечении которых находится активная ячейка, выделяются жирным шрифтом.

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

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

Адрес диапазона, содержащего целые столбцы рабочего листа, составляется из заголовков левого и правого столбцов диапазона, разделенных двоеточием. Например, A:C – адрес диапазона, состоящего из целых столбцов A, B и C; B: B – адрес столбца B.

Аналогично, адрес диапазона, содержащего целые строки рабочего листа, составляется из заголовков верхней и нижней строки диапазона, разделенных двоеточием. Например, 2:4 – адрес диапазона, состоящего из 2-й, 3-й и 4-й строк; 5:5 – адрес 5-й строки рабочего листа.

Выделение ячеек

Выделение ячеек с помощью мыши

Чтобы выполнить операцию по редактированию или форматированию ячеек, надо выделить эти ячейки.

Чтобы выделить одну ячейку, надо щелкнуть по ней.

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

Чтобы выделить всю строку, надо щелкнуть по заголовку этой строки. Для выделения нескольких смежных строк – протащить указатель мыши по заголовкам этих строк.

Чтобы выделить весь столбец, надо щелкнуть по заголовку этого столбца. Для выделения нескольких смежных столбцов – протащить указатель мыши по заголовкам этих столбцов.

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

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

Выделение ячеек с помощью клавиатуры

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

Выделить диапазон ячеек можно одним из следующих способов:

- поместить курсор в левую верхнюю ячейку диапазона, нажать клавишу F8, с помощью клавиш управления курсором переместить курсор в правую нижнюю ячейку диапазона, повторно нажать клавишу F8;

- поместить курсор в левую верхнюю ячейку диапазона, нажать клавишу Shift и, удерживая ее, с помощью клавиш управления курсором переместить курсор в правую нижнюю ячейку диапазона, после чего отпустить клавишу Shift;

Для выделения столбца поместить курсор в любую ячейку этого столбца и нажать комбинацию клавиш Ctrl + пробел. Для выделения нескольких смежных столбцов выделить любую строку, принадлежащую этим столбцам, и нажать комбинацию клавиш Ctrl + пробел.

Для выделения строки поместить курсор в любую ячейку этой строки и нажать комбинацию клавиш Shift + пробел. Для выделения нескольких смежных строк выделить любой столбец, принадлежащий этим строкам, и нажать комбинацию клавиш Shift + пробел.

Для выделения всех ячеек листа нажать комбинацию клавиш Ctrl + A.

Для выделения несмежных диапазонов с помощью клавиатуры надо:

1. Выделить первый диапазон.

2. Нажать клавиши Shift + F8.

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

Выделение ячеек с помощью поля Имя

Для выделения одной ячейки или диапазона ячеек надо ввести адрес ячейки или диапазона в поле Имя.

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

Выделение ячеек с помощью меню

Для выделения одной ячейки или диапазона ячеек надо:

1. На вкладке Главная в группе Редактирование щелкнуть по кнопке Найти и выделить.

2. Выбрать из списка команду Перейти.

3. В поле Ссылка ввести адрес нужной ячейки или диапазона ячеек.

4. Щелкнуть по кнопке ОК.

Ввод данных

Правила ввода данных

Данные вводятся в активную ячейку. Ввод данных заканчивается нажатием клавиши Enter или любой из клавиш со стрелками. После нажатия клавиши со стрелкой одновременно с окончанием ввода произойдет переход к соседней ячейке в соответствующем направлении. Нажатие клавиши Enter также может сопровождаться переходом к другой ячейке, если установлен флажок Переход к другой ячейке после нажатия клавиши ввод на вкладке Дополнительно диалогового окна кнопки Параметры Excel, которая находится в диалоговом окне кнопки Office.

Чтобы заполнить данными интервал (прямоугольную группу) ячеек, удобно выделить эти ячейки (можно и несмежные), а затем последовательно ввести данные в ячейки этого интервала. При этом для заполнения интервала сверху вниз используется клавиша Enter, снизу вверх – Shift +Enter, слева направо – Tab, справа налево – Shift +Tab.

Чтобы ввести одни и те же данные сразу в несколько ячеек, надо выделить нужные ячейки, ввести данные в одну из них, а затем нажать клавиши Ctrl + Enter.

Чтобы отменить ввод до нажатия клавиши Enter, надо нажать клавишу Esc.

Чтобы отменить ввод данных после нажатия клавиши Enter, надо щелкнуть по кнопке Отменить на панели быстрого доступа.

Типы данных

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

Текст и числа являются константами.

Формула – это последовательность значений, ссылок на ячейки, имен, функций и знаков операций, по которым из заданных значений выводится новое. Это значение не является константой, оно зависит от значений, входящих в формулу.

Текст

Текст может быть представлен символами или произвольными сочетаниями чисел и символов. Любой набор символов, не являющийся числом или формулой, интерпретируется как текст.

Если необходимо ввести текст, имеющий вид числа, нужно отформатировать ячейку, выбрав на вкладке Главная в группе Число, в списке Числовой формат пункт Текстовый. Можно ввести число в текстовом формате и другим способом, поставив перед ним апостроф.

В ячейку входит до 255 символов. Если длина текста превосходит ширину ячейки, то он переходит в пустые ячейки справа, если же ячейка справа содержит данные, то текст усекается.

Числа

Числа могут состоять из цифр от 0 до 9 и любого из следующих специальных символов:

+ − ( ) , / р. % . E e

В числа можно включать пробелы.

Запятая воспринимается как десятичный разделитель.

Знаки «+» перед числами не принимаются во внимание.

Перед отрицательными числами ставится знак «−», или они заключаются в круглые скобки.

Числа могут быть введены в экспоненциальном формате: мантисса и порядок, разделенные символом E. Например, число вводится в виде 1,45E-2.

В ячейки можно вводить обыкновенные дроби в следующем формате:

целая часть пробел числитель слеш знаменатель,

например, число следует ввести так: 8 3/4. Ноль в целой части опускать нельзя, например, дробь следует ввести как 0 2/7.

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

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

При вводе дат в качестве символа-разделителя используется точка или слеш (например: 4.03.2009 или 4/03/2009), при вводе времени − двоеточие (20:50, 20:50:35).

Заполнение смежных ячеек и создание рядов

В правом нижнем углу активной ячейки или выделенного интервала ячеек находится маркер заполнения (рис. 3), с помощью которого можно копировать содержимое одних ячеек в другие, а также создавать ряды (числовые последовательности).

Чтобы скопировать содержимое ячейки или интервала ячеек в смежные ячейки, следует:

1. Выделить ячейки, из которых надо скопировать данные.

2. Установить указатель мыши на маркер заполнения так, чтобы указатель принял вид перекрестия.

3. Перетащить маркер заполнения через те ячейки, куда надо скопировать данные.

Чтобы создать ряд (числовую последовательность), надо ввести первые два члена последовательности, выделить эти ячейки, а затем поступать так же, как при копировании. Причем при перетаскивании маркера вправо и вниз создаются последовательности с возрастающими значениями, а при перетаскивании влево и вверх – с убывающими.

Для создания последовательностей дат, месяцев в году или дней в неделе (с шагом 1) достаточно ввести только первый член этой последовательности.

Можно создавать последовательности строк (столбцов). Для этого надо заполнить первые две строки (столбца), а затем использовать маркер заполнения.

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

Контекстное меню содержит следующие команды:

- Копировать ячейки – копирование содержимого выделенных ячеек;

- Заполнить – заполнение ячеек числовой последовательностью;

- Заполнить только форматы – копирование только форматов;

- Заполнить только значения – копирование только значений без форматов;

- Заполнить по дням – заполнение ячеек последовательностью дней недели или дат с шагом 1 день;

- Заполнить по рабочим дням – то же, но исключая выходные дни;

- Заполнить по месяцам – заполнение ячеек последовательностью месяцев или дат с шагом 1 месяц;

- Заполнить по годам – заполнение ячеек последовательностью лет или дат с шагом 1 год;

- Линейное приближение – заполнение ячеек значениями, вычисленными на основе аппроксимации исходных данных линейной функцией;

- Экспоненциальное приближение – заполнение ячеек значениями, вычисленными на основе аппроксимации исходных данных экспоненциальной функцией;

- Прогрессия – заполнение ячеек арифметической или геометрической прогрессией; в открывшемся диалоговом окне устанавливаются параметры прогрессии (расположение, тип, единицы, шаг, предельное значение).

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

Создание формул

Структура формулы

Формула – это последовательность значений, ссылок на ячейки, имен, функций и операторов, по которым из заданных значений выводится новое.

Простейшая формула – это комбинация данных и операторов действий над данными. Формула начинается со знака равенства (синтаксический признак формулы).

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

Операторы

В Microsoft Excel используются следующие операторы действий над данными:

1. Арифметические операторы:

+ сложение;

− вычитание;

* умножение;

/ деление;

^ возведение в степень;

% сотая доля числа (ставится после значения).

2. Операторы сравнения – сравнивают два значения, результатом выполнения является логическое значение (Истина или Ложь):

= равно;

<> не равно;

> больше;

< меньше;

>= больше или равно;

<= меньше или равно.

3. Текстовый оператор:

& конкатенация (объединение строк).

Порядок выполнения действий в формулах

Когда в одной формуле объединяются несколько операций, действия выполняются в следующем порядке:

1) процент (%);

2) возведение в степень (^);

3) умножение и деление (*, /);

4) сложение и вычитание (+, −);

5) конкатенация (&);

6) операторы сравнения (=, <>, >, <, >=, <=).

Операторы с одинаковым приоритетом выполняются слева направо.

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

Ссылки

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

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

Существует три типа ссылок: относительные, абсолютные и смешанные.

Относительные ссылки

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

Координаты относитель­ной ссылки зависят от того, в какую ячейку вводится формула.

При копировании формулы изменение координат относительной ссылки равно разности между координатами ячейки-получателя и ячейки-источника (рис. 4). При этом относительное положение ячеек остается неизменным.

Абсолютные ссылки

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

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

При копировании формулы координаты абсолютной ссылки не изменяются (рис. 5).

В абсолютной ссылке перед заголовками столбца и строки ставятся знаки доллара ($).

Смешанные ссылки

Ссылка, относитель­ная по одной из коорди­нат и абсолютная – по другой, называется сме­шанной (рис. 6).

В ссылке, абсолют­ной по столбцу и относительной по строке, координата столбца по­стоянная, а координата строки зависит от того, в какую строку вводится формула. В такой ссылке знак доллара ставится только перед заголовком строки.

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

Ссылки на другие листы рабочей книги

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

Лист2!A1

Если в имени листа имеются пробелы, то оно заключается в апострофы:

‘Сбыт за январь’!$A$1

Внешние ссылки

Ссылки на ячейки других рабочих книг называются внешними.

В начале внешней ссылки в квадратных скобках ставится имя файла рабочей книги:

[СБЫТ.XLS]Лист1!$F$7

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

‘C:\SALES\[СБЫТ.XLS]Лист1’!$F$7

Ввод ссылок в формулы

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

Этим способом можно ввести ссылки не только на ячейки, расположенные на одном рабочем листе с формулой, но и на ячейки, находящиеся на другом рабочем листе и даже в другой рабочей книге. Для этого в процессе ввода формулы надо перейти к нужному листу или нужной книге и выделить нужные ячейки.

Для ввода знаков доллара в абсолютные и смешанные ссылки можно использовать клавишу F4 (нажимать несколько раз, пока ссылка не отобразится в нужном виде).

Копирование формул

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

Копирование удобнее всего выполнять с помощью маркера заполнения, но возможны и другие способы (см. «Редактирование рабочего листа»).

Управление вычислениями

Вычисления вручную. При изменении данных все формулы автоматически пересчитываются. Чтобы уменьшить количество вычислений (например, при вводе или редактировании большого количества формул), можно заблокировать автоматический пересчет. Для этого надо в окне кнопки Office щелкнуть по кнопке Параметры Excel, выбрать вкладку Формулы и в группе переключателей Вычисления в книге выбрать вариант Вручную. Ручной пересчет выполняется с помощью кнопки Пересчет, которая находится на вкладке Формулы в группе Вычисления или при нажатии клавиши F9.

Имена

Правила для имен

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

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

При назначении имен нужно руководствоваться следующими правилами:

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

- первым символом может быть буква или знак подчеркивания;

- имя не должно иметь вид ссылки;

- длина имени не должна превышать 255 символов;

- прописные и строчные буквы не различаются.

Присвоение имени

Чтобы присвоить имя ячейке или интервалу ячеек, следует:

1. Выделить ячейку или интервал.

2. На вкладке Формулы в группе Определенные имена выбрать команду Присвоить имя или Диспетчер имен.

3. Если была выбрана команда Диспетчер имен, в открывшемся диалоговом окне щелкнуть по кнопке Создать.

4. В открывшемся диалоговом окне в поле Имя ввести имя.

5. В раскрывающемся списке Область выбрать область видимости для создаваемого имени (вся книга или отдельный лист).

6. В поле Диапазон указать адрес ячейки или диапазона, которому присваивается имя.

7. В поле Примечание можно ввести комментарии. Это поле заполнять не обязательно.

Другой способ: выделить нужную ячейку (или диапазон) и в поле Имя ввести имя этой ячейки (или диапазона).

Поле имя содержит список всех использующихся в данной книге имен.

Управление именами

Создавать, редактировать и удалять имена позволяет Диспетчер имен.

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

Для изменения имени надо выделить в списке нужное имя, затем щелкнуть по кнопке Изменить. Откроется окно Изменение имени, в котором можно изменить имя, диапазон и примечание.

Для удаления имени надо выделить в списке это имя, затем щелкнуть по кнопке Удалить.

Практическая работа 1

Абсолютные и относительные ссылки

1. В табличном процессоре Microsoft Excel 2007 создать новую рабочую книгу.

2. На первом листе составить таблицу, составить таблицу, содержащую информацию о зарплате сотрудников. Столбцы таблицы: Номер по порядку, Фамилия, Имя, Отчество, Должность, Оклад, Аванс, Пенсионный фонд, Налог, К выдаче.

Столбец Номер заполнить номерами по порядку.

Значения столбцов Фамилия, Имя, Отчество, Должность и Оклад заполнить произвольными значениями.

Остальные столбцы рассчитываются по формулам:

- аванс составляет 40% от оклада;

- отчисления в пенсионный фонд– 1% от оклада;

- Налог= (Оклад – Пенсионный фонд – МРОТ) × 13%;

- МРОТ – минимальный размер оплаты труда;

- К выдаче = Оклад – Аванс –Пенсионный фонд – Налог.

Значения процентов аванса, отчислений в пенсионный фонд, налога, а также минимального размера оплаты труда (МРОТ) разместить в ячейках второй строки таблицы (под «шапкой). Для этого вставить пустую строку между «шапкой» и первой строкой с данными. В формулах использовать ссылки на эти ячейки.

Таблица должна содержать не менее 10 строк.

3. Присвоить листу имя Расчет зарплаты.

4. На рабочем диске создать свою папку и сохранить в ней файл.

Смешанные ссылки

5. Открыть новый лист.

6. Составить таблицу умножения:

Левую верхнюю ячейку таблицы оставить пустой. Первую строку и первый столбец этой таблицы (начиная со вторых ячеек) заполнить числами от 0 до 9. В остальных ячейках поместить результат перемножения чисел из первых ячеек столбца и строки, на пересечении которых находится данная ячейка. Ввести формулу, используя смешанные ссылки.

7. Составить таблицу значений функции двух переменных

для x в интервале от -3 до 0 с шагом 0,15 и y в интервале от 2 до 5 с шагом 0,15.

Значения переменной x разместить в первой строке таблицы, y – в первом столбце, z – на пересечении соответствующих строк и столбцов. Приводе формулы использовать смешанные ссылки.

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

для x в интервале от -1 до 1 с шагом 0,1 и y в интервале от -1 до 1 с шагом 0,1.

9. Присвоить листу имя Смешанные ссылки.

Функции в формулах

Функция – это имеющая имя, заранее созданная формула, которая выполняет операции над заданными значениями и возвращает одно или несколько значений.

Функция − составная часть формулы; в частном случае формула может состоять из одной функции.

Значения, над которыми функции выполняют операции, называются аргументами.

Последовательность символов, с помощью которых вводится формула, называется синтаксисом.

Все функции имеют одинаковый основной синтаксис: сначала идет имя функции, затем в круглых скобках список аргументов, разделенных точками с запятой (или иным символом, определенным как разделитель элементов списка).

Аргументами функций могут быть числа, текст, логические значения, массивы, ссылки, формулы, функции.

Некоторые функции не имеют аргументов, круглые скобки тем не менее сохраняются, например, функция СЕГОДНЯ() возвращает текущую дату.

Ввод функций

Функции можно вводить в формулу с клавиатуры, но удобнее вставлять функции в формулу, используя диалоговое окно Аргументы функции.

Это окно можно вывести на экран несколькими способами:

I способ:

1. На вкладке Формулы открыть список Библиотека функций.

2. В открывшемся списке выбрать категорию, к которой относится нужная функция.

3. В открывшемся списке функций выбрать нужную.

II способ:

1. На вкладке Формулы открыть список Библиотека функций.

2. Щелкнуть по кнопке Вставить функцию. Откроется диалоговое окно Мастера функций.

3. В поле списка Категория выбрать категорию, к которой относится нужная функция.

4. В поле списка Функция выбрать имя функции и щелкнуть по кнопке ОК.

III способ: щелкнуть по кнопке Вставить функцию, которая находится левее строки формул, после этого откроется диалоговое окно Мастера функций, в котором надо выбрать категорию и функцию.

IV способ: нажать клавиши Shift + F3 и в открывшемся окне Мастера функций выбрать нужную функцию.

После любого из этих действий откроется диалоговое окно Аргументы функции. В этом окне имеется отдельное поле для каждого аргумента функции.

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

После ввода каждого аргумента справа от поля отображается его значение.

В средней части окна выводится текст, объясняющий назначение функции и смысл её аргументов.

В нижней части окна отображается значение функции, а также дается ссылка для вызова справки для данной функции.

После ввода всех аргументов щелкнуть по кнопке ОК.

Автозаполнение формул

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

Вложенные функции

Если аргумент функции сам является функцией, то такая функция называется вложенной, а формула называется формулой с двумя уровнями вложенных функций. Microsoft Excel допускает до семи уровней вложения функций в одной формуле.

Чтобы в поле диалогового окна Аргументы функции в качестве аргумента ввести функцию, надо щелкнуть мышью по этому полю, а затем – по кнопке раскрытия списка функций, расположенного слева в строке формул. После выбора из этого списка нужной функции откроется диалоговое окно для ввода аргументов вложенной функции.

Для продолжения ввода данных во внешнюю функцию надо щелкнуть мышью по ее имени в строке формул.

Редактирование функций

Чтобы отредактировать функцию в существующей формуле, надо выделить ячейку с формулой, а затем выполнить одно из трех действий:

- на вкладке Формулы открыть список Библиотека функций, затем щелкнуть по кнопке Вставить функцию;

- щелкнуть по кнопке Вставка функции в строке формул;

- нажать клавиши Shift + F3.

После этого откроется диалоговое окноАргументы функции, в которое можно внести нужные изменения.

Автосуммирование

Для суммирования содержимого ячеек предназначена функция СУММ. Синтаксис функции:

СУММ(аргументы)

Функция СУММ может иметь до 255 аргументов. Аргументами могут быть отдельные ячейки, диапазоны ячеек, в том числе, несмежные, строки, столбцы, а также значения.

Для суммирования содержимого строки или столбца в Excel имеется инструмент автосуммирования, упрощающий работу с этой функцией.

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

- на вкладке Главная в группе Редактирование выбрать команду Сумма;

- на вкладке Формулы в группе Библиотека функций выбрать команду Автосумма;

- нажать комбинацию клавиш Alt + =.

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

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

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

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

Кроме суммы кнопка Автосумма содержит список наиболее часто используемых статистических функций:Среднее,Число,Максимум,Минимум.

Для ввода этих функций используются те же приемы, что и для функции суммирования.