Формулы и функции MS Excel. Виды ссылок на ячейки. Маркер автозаполнения. Лекция 2

1. Лекция 2 Формулы и функции MS Excel. Виды ссылок на ячейки. Маркер автозаполнения.

2. Опр. 1 Формула

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

3. Режимы отображения данных

Таблица находится в режиме отображения формул, который позволяет
проследить алгоритм табличных вычислений.
Результаты вычислений по формулам видны на экране в режиме отображения
значений.
! Основное свойство электронной таблицы: изменение числового
значения в ячейке приводит к мгновенному пересчету формул,
содержащих имя этой ячейки.

4.

Пример:
В таблице находится сам
ответ на вычисление

5. Редактирование формул

Для редактирования формулы необходимо:
1. Выделить ячейку с формулой. При этом формула отобразится в
строке формул;
2. Установить курсор в строку формул;
3. Внести исправления и нажать клавишу Enter.
Для редактирования формулы можно дважды щелкнуть на ячейке,
установить курсор в место ошибки, внести нужные исправления и нажать
Enter.

6. Относительные, абсолютные и смешанные ссылки

7.

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

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

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

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

При изменении позиции ячейки, содержащей
формулу, абсолютная ссылка не изменяется.
Для задания абсолютного адреса перед
обозначением строки и столбца ставится знак доллара $.
Например, ссылка на ячейку $C$3 является абсолютной
ссылкой. Также для задания абсолютного адреса можно
воспользоваться функциональной клавишей F4.

10.

Пример 1 применения абсолютной ссылки:

11. Пример 2 применения абсолютной ссылки:

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

Кроме относительных и абсолютных ссылок в формулах еще
можно использовать смешанные ссылки.
Смешанная ссылка бывает двух типов:
1. абсолютный столбец и относительную строку (например,
$A1, $B1 и т.д.),
2. абсолютную строку и относительный столбец (например,
A$1, B$1 и т.д.).

13. Быстрый доступ к часто используемым математическим функциям

14. Маркер автозаполнения

15. Маркер автозаполнения

Маркер автозаполнения – это инструмент для
быстрого применения формулы.

16. Пример копирования формул:

Нужно зажать ЛКМ по
данной области и
протянуть вниз

17. Результат копирования формул:

18.

ВОЗМОЖНОСТИ МАРКЕРА
АВТОЗАПОЛНЕНИЯ

19. Маркер автозаполнения

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

20. Копирование чисел

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

21. Копирование текста

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

22. Копирование текста с числом

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

23. Копирование даты и времени

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

24. Числовые последовательности

Программа Excel позволяет получать числовые
последовательности.
Если зажать клавишу CTRL и перетаскивать число вниз или
вправо в соседние ячейки, то к нему автоматически будет
прибавляться 1.
CTRL
CTRL

25. Числовые последовательности

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

26. Числовые последовательности

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

27. Числовые последовательности

28. Последовательности дат

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

29. Последовательности времени

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

30. Последовательности дат и времени

31. Списки

Программа содержит готовые стандартные
последовательности (списки). Это названия месяцев и
дней недели в полном и сокращенном виде.
Для использования списка нужно напечатать из него
одно слово и протащить его за маркер автозаполнения.

32. Ошибки при работе с формулами MS Excel и способы их решения

33.

Ошибка #####.
• Ошибка появляется, когда вводимое числовое значение не умещается в
ячейке. В этом случае можно увеличить ширину столбца путем
перемещения границы, расположенной между заголовками столбцов.
Ошибка #ЗНАЧ!
• Вместо числового или логического (ИСТИНА или ЛОЖЬ) значения введен
текст, и Microsoft Excel не может преобразовать его к нужному типу
данных;
• Для оператора или функции, требующей одного значения, возвращается
диапазон.
Ошибка #ДЕЛ/0!
• В качестве делителя используется ссылка на ячейку, содержащую нулевое
или пустое значение (если операнд является пустой ячейкой, то ее
содержимое интерпретируется как ноль);
• В формуле содержится явное деление на ноль (например, «=5/0»).

34.

Ошибка #ИМЯ?
• Используемое имя было удалено или не было определено;
• Имеется ошибка в написании имени;
• Имеется ошибка в написании имени функции;
• В формулу введен текст, не заключенный в двойные кавычки. Microsoft
Excel пытается распознать такую запись как имя, даже если это не
предполагалось;
• В ссылке на диапазон ячеек пропущен знак двоеточия (:).
Ошибка #Н/Д
• Для функций ГПР, ПРОСМОТР, ПОИСКПОЗ или ВПР задан недопустимый
аргумент искомое значение;
• Используются функции ВПР или ГПР, просматривающие значение в
несортированной таблице;
• В формуле массива используется аргумент не соответствующий
диапазону, определяющимся числом строк и столбцов, который указан
в формуле массива;
• Не заданы один или несколько аргументов стандартной или
пользовательской функции листа;
• Используется пользовательская функция, обращение к которой
приводит к ошибке.

35.

Ошибка #ССЫЛКА!
• Ячейки, на которые ссылаются формулы, были удалены или в эти
ячейки было помещено содержимое других скопированных ячеек.
Ошибка #ЧИСЛО!
• В функции с числовым аргументом используется неприемлемый
аргумент;
• Использована функция, при вычислении которой применяются
итерации, например, такие функции, как ВНДОХ или НОРМА, и при
этом итерации не сходятся и результат не может быть получен;
• Введена формула, возвращающая числовое значение, которое
слишком велико или слишком мало, чтобы его можно было
представить в Microsoft Excel.
Ошибка #ПУСТО!
• Использован ошибочный оператор пересечения диапазонов или
ошибочная ссылка на ячейку.