Материал: ЭКСЕЛЬ2 2013 Методичка Расширенные возможности

6

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Модуль 1. ПРИМЕНЕНИЕ ВСТРОЕННЫХ ФУНКЦИЙ EXCEL

Формулы

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

Последовательность действий:

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

2.Ввести с клавиатуры знак = .

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

4.Enter .

Знаки операций

Различные типы ссылок

Переход между типами ссылок – нажатие клавиши F4 на клавиатуре.

A1 → F4 → $A$1→ F4 → A$1→ F4 →$A1→ F4 →A1

Параметры вычислений

Обновление результатов вычислений происходит автоматически при изменении исходных данных или копировании формул – так заложено в программе изначально, но можно настроить и изменение вручную, т.е. по команде пользователя. Выбрать режим пересчета формул можно на вкладе Формулы [Formulas], в группе Вычисление [Calculation], раскрыв

Параметры вычислений [Calculation Options]:

Автоматически [Automatic] – перерасчет результатов происходит в автоматическом режиме.

8

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Для редактирования связи с внешним источником на вкладке Данные [Data], в группе

Подключения [Connections], выбрать Изменить связи [Edit Links].

В окне Изменение связей [Edit Links] выбрать нужное действие:

Обновить [Update Values] – обновление выделенного источника.

Изменить [Change source] – изменение внешнего источника в случае смены имени файла или перемещения источника в другую папку.

Открыть [Open Source] – открытие файла-источника.

Разорвать связь [Break Link] - разорвать связь с внешним источником. Формулы в ячейках заменяются значениями, которые получены в ячейках на данный момент. Команду отменить нельзя, поэтому следует создать копию файла, чтобы при необходимости остался исходный файл с формулами для последующих обновлений.

Запрос на обновление связей [Startup Prompt] – возможность настроить вариант обновления:

Пользователь указывает, нужно ли задавать вопрос [Let users choose to display the alert or not]]

Не задавать вопрос и не обновлять связи [Don’t display the alert and don’t update automatic links]

Не задавать вопрос и обновлять связи [Don’t display the alert and update links]

Для удобства перехода между открытыми книгами, можно нажимать клавиши CTRL + Tab .

ПРАКТИКУМ:

Открыть файл 01_1 Вычисления.

Выполнить задания на листе ССЫЛКИ5.

9

Microsoft Excel 2013. Уровень 2. Расширенные возможности

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

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

Например, формула =Цена€*курсЕвро гораздо понятнее, чем формула =C2*курс!$B$2.

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

ТРЕБОВАНИЯ К ИМЕНАМ: имя может начинаться с буквы, знака подчеркивания_ или обратного слеша \, затем могут быть буквы, цифры, точки, подчеркивание и обратный слеш \. В имени нельзя использовать пробелы и имя не может совпадать с адресацией ячеек (например, А1 или R1C1). Длина имени не может превышать 255 знаков.

Создание имен

1-й способ: Присвоение имени в поле Имя.

1.Выделить ячейку или диапазон ячеек.

2.В поле Имя [Name Box] (слева в строке формул) ввести имя с клавиатуры,

нажать Enter .

2-й способ: Присвоение имени в окне Диспетчера имен.

1.Выделить ячейку или диапазон ячеек.

2.На вкладке Формулы [Formulas], в группе Определенные имена [Defined Names], выбрать Диспетчер имен [Name Manager] или нажать клавиши

Ctrl + F3 , затем Создать [New]. Или можно щелкнуть правой кнопкой мыши по

выделению и выбрать Присвоить имя [Define name].

3.В окне Создание имени [New Name]:

Ввести Имя [Name].

Определить Область [Scope] действия имени (в пределах листа или книги).

Ввести текст в Примечание [Comment] – комментарий при необходимости.

Диапазон [Refers to] – область ссылки, тип ссылки, формула.

OK.

10

Microsoft Excel 2013. Уровень 2. Расширенные возможности

3-й способ: Из заголовков строк/столбцов.

1.Выделить диапазон данных вместе с заголовками.

2.На вкладке Формулы [Formulas], в

группе Определенные имена [Defined Names], выбрать Создать из выделенного [Create from Selection] или нажать клавиши Ctrl + Shift + F3 .

3.Выбрать расположение заголовков относительно данных в выделенном диапазоне, OK.

Редактирование имен

Для редактирования имени:

1.На вкладке Формулы [Formulas], в группе Определенные имена [Defined Names], выбрать

Диспетчер имен [Name Manager] или нажать клавиши Ctrl + F3 .

2.Выделить имя, выбрать:

Изменить [Edit] – изменить имя, диапазон ячеек или область применения.

Удалить [Delete] – удалить имя.

3.OK.

Вставка имен в формулу

При написании формулы можно:

Щелкнуть по ячейке или выделить диапазон ячеек.

Ввести имя с клавиатуры, используя автозавершение формул.

Нажать клавишу F3 для выбора из списка имен.

На вкладке Формулы [Formulas], в группе Определенные имена [Defined Names], выбрать в раскрывающемся списке кнопки Использовать в формуле [Use in Formula].

ПРАКТИКУМ:

Открыть файл 01_1 Вычисления.

Выполнить задания на листах ИМЕНА и ИМЕНА ДИАПАЗОНОВ

Сохранить изменения в файле и закрыть его.

САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ

Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 1-2.