Использование ссылок. Примеры часто используемых функций (формул)

1.

Информатика 2023

2.

Урок 1 MS Word
1
Создать стили для документа (book_name, book_name1, book_text).
Заголовки крупные, жирные, цветные, шрифт Monotype Corsiva, размер 18,16, выровнены по
центру.
Основной текст: начертание по желанию, размер 14, цвет черный, выровнен по ширине,
красная строка 1см.
2
Разбить документ на отдельные страницы, в соответствии с частями. Заголовок, слово Конец –
на отдельных страницах. Настройте в тексте междустрочный интервал 1,25
3
1 часть разбита на 2 колонки
4
2 часть содержит рисунок (папка фото) и в альбомном формате
5
К слову Нукеры (1часть) создана обычная сноска
6
Страницы пронумерованы, на первой странице номер не проставлен
7
Создано автоматическое оглавление, на отдельной странице
8
Сохранить в \\Pdc\transport\МТИД в свою папку и выложить на портал

3.

Урок 2 MS Excel
1. Использование ссылок
2. Примеры часто используемых функций (формул)
В процессе работы мы будем использовать файл EXCEL.xlsm, он
храниться T:\МТИД_23-1б\!_Материалы (рекомендую сделать себе
копию)
Данная презентация там же. К каждому уроку я ее обновляю!
Файлы созданные на уроках сохранять в свои папки с
рекомендованными именами!

4.

Использование ссылок
Ссылки в Excel бывают двух стилей:
стиль А1
стиль R1C1
Ячейка или диапазон
Ссылка
Ячейку в столбце A и строке 10
A10
Диапазон ячеек: столбец А, строки 10-20.
A10:A20
Диапазон ячеек: строка 15, столбцы B-E.
B15:E15
Все ячейки в строке 5.
5:5
Все ячейки в строках с 5 по 10.
5:10
Все ячейки в столбце H.
H:H
Все ячейки в столбцах с H по J.
H:J
Диапазон ячеек: столбцы А-E, строки 10-20.
A10:E20

5.

Использование ссылок
• В стиле R1C1 и строки (rows), и столбцы (columns) обозначаются номерами.
Например, R2C2 – абсолютная ссылка на ячейку, расположенную во второй строке и
во втором столбце.
• RC - относительная ссылка на текущую ячейку
• R2C2 - то же самое, что $B$2 (абсолютная ссылка)
• RC5 - ссылка на ячейку из пятого столбца в текущей строке
• RC[-1] - ссылка на ячейку из предыдущего столбца в текущей строке
• RC[2] - ссылка на ячейку, отстоящую на два столбца правее в той же строке
• R[2]C[-3] - ссылка на ячейку, отстоящую на две строки ниже и на три столбца левее
от текущей ячейки
• R5C[-2] - ссылка на ячейку из пятой строки, отстоящую на два столбца левее
текущей ячейки
• и т.д.

6.

Использование ссылок

7.

Использование ссылок
• В MS Excel используются три типа ссылок относительные, абсолютные
и смешанные.
• Относительные ссылки это адресация в синтаксисе формулы ячейки,
содержащей значение в качестве аргумента для данной функции и при
перемещении формулы в другие ячейки, перемещается также
адресация на ячейки с соразмерным шагом перемещения ячейки
содержащей эту формулу
• Смешанная ссылка — это ссылка, которая сочетает в себе
относительную и абсолютную ссылку.
• Абсолютные ссылки всегда указывают на конкретные ячейки при
перемещении функции. Фиксация ссылки осуществляется с помощью
знака $

8.

Использование ссылок
$H$12 — Абсолютный адрес (фиксирован столбец и фиксирована строка);
H$12 — Смешанный адрес (фиксирована только строка);
$H12 — Смешанный адрес (фиксирован только столбец);
H12 — Относительный адрес (ссылка не имеет фиксации).
Клавиша F4

9.

Задание

10.

Задание
Создайте формулу в ячейке D5, которая путем копирования в
диапазоне D5:H9 позволит получить таблицу умножения.
Файл Excel.xlsx Лист 16
1
1
2
3
4
5
2
3
4
5

11.

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

12.

Чем отличаются?
• =SUM([Budget.xlsx]Annual!C10:C25)
• =SUM('C:\Reports\[Budget.xlsx]Annual'!C10:C25)

13.

Задание
• Создайте книгу бюджет.xlsx в которой соберите данные
продуктовой корзины по месяцам (январь, февраль, март) и
квартальный отчет. Заполним данные используя функцию
=случмежду(10000;55000)
• Результат выведите в книгу EXCEL.XLSX лист 16 в ячейку J17

14.

Трехмерные ссылки
• Имеем несколько однотипных таблиц на разных листах одной
книги. Например, вот такие
=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3
=СУММ('2001 год:2003 год'!B3)

15.

Задание
В файле бюджет создайте формулу с использованием
трехмерной ссылки

16.

Использование структурированных ссылок
в таблицах Excel
• Данные организованные в формате Таблицы будут использованы
в формулах как Структурированные ссылки
• Привести к структурированным данным можно преобразовав
данные в Таблицу иногда ее называют Умная таблица
Это не просто диапазон данных, а цельный объект, у которого есть
свое название, внутренняя структура, свойства и множество
преимуществ по сравнению с обычным диапазоном ячеек.

17.

Способы создания структурированной
таблицы
• Для преобразования
диапазона в Таблицу выделите
любую ячейку и затем Вставка
→ Таблицы → Таблица
• Есть горячая клавиша Ctrl+T
• Для преобразования
диапазона в Таблицу выделите
любую ячейку и затем Главная
→ Форматировать как
таблицу

18.

Структура и ссылки на Таблицу Excel
• Каждая Таблица имеет свое название. Это видно во
вкладке Конструктор
• Если в вашей книге Excel планируется несколько Таблиц, то имеет
смысл придать им более говорящие названия. В дальнейшем это
облегчит их использование (например, при работе в Power Pivot
или Power Query). Имя Таблицы видна в диспетчере
имен Формулы → Определенные Имена → Диспетчер имен.
• Эксель видит не только целую Таблицу, но и ее отдельные части:
столбцы, заголовки, итоги и др. Ссылки при этом выглядят
следующим образом.

19.

Структура и ссылки на Таблицу Excel
=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца
«Продажи»

20.

задание
• Откройте Лист 17 сравните
две таблицы
• Создайте таблицу
преобразуйте ее в таблицу

21.

Функции Excel (Файл EXCEL.xlsm Лист10)
• ОКРУГЛ(число; число разрядов) функция бухгалтера (возврат НДС)
=ОКРУГЛ(21,5; -1) вернет 20 =ОКРУГЛВНИЗ(21,5; -1) вернет 20 =ОКРУГЛВВЕРХ(21,5; -1) вернет 30
=ОКРУГЛ(2,15; 1) вернет 2,2 =ОКРУГЛВНИЗ(2,15; 1) вернет 2,1 =ОКРУГЛВВЕРХ(2,15; 1) вернет 2,2
• ПРОИЗВЕД()
• СТЕПЕНЬ(число;степень)
• СРЗНАЧ() только числовые значения
• СЧЕТ() только числовые значение
• СЧЁТЕСЛИ(диапазон;критерий) агригационно логические функции
• СУММЕСЛИ(диапазон_проверки; критерий; диапазон_суммирования)

22.

Задание

23.

Задание

24.

Задание
• Task 10 файл EXCEL.xlsm

25.

ДЗ №1

26.

Структурированные данные
• Структурированные данные это данные, которые соответствуют
модели данных, имеют четко определенную структуру, следуют
последовательному порядку и могут быть легко доступны и
использованы человеком или компьютерной программой.
• Структурированные данные обычно хранятся в четко
определенных схемах, таких как базы данных.
В MS Excele:
• Есть заголовок
• Однотипные данные

27.

Структурированные данные
Наименование
Товар1
Товар2
Товар3
Товар4
Товар5
Товар6
Товар7
Товар8
квартал 1
январь февраль март
4
4
6
6
8
8
10
10
12
12
14
14
16
16
18
18
апрель
4
6
8
10
12
14
16
18
квартал 2
май
4
6
8
8
8
8
8
8
№
июнь
4
6
8
8
8
8
8
8
4
6
8
8
8
8
8
8
Дата
Наименование
1
15.декТовар1
2
16.декТовар2
3
17.декТовар3
4
18.декТовар4
5
19.декТовар5
6
20.декТовар6
7
21.декТовар7
8
22.декТовар8
9
23.декТовар9
Цена
Кол-во
15
15
15
15
15
15
15
15
Сумма Сумма с НДС
15
225
265,5
15
225
265,5
6
90
106,2
15
225
265,5
15
225
265,5
15
225
265,5
4
60
70,8
15
225
265,5
15
15
225
265,5

28.

Непостоянные функции
ДВССЛ
ИНДЕКС
СМЕЩ
ЯЧЕЙКА
ОБЛАСТИ
СТРОКА
СТОЛБЕЦ
СЕЙЧАС
СЕГОДНЯ
СЛЧИС
СЛЧИСМЕЖДУ
Урок 2.xlsx скопируйте файл
Вычисляется без прямой инициации:
•Вычисления др.функции
•Обновление
•Ввод значений в систему
•Открытие документа.
Программа автоматически пересчитывает
эти функции, даже если вы ничего не меняли
в формуле и аргументах, на которые
функция ссылается

29.

Агрегационно логические функции
• Что это за функции?
Допустим нам необходимо
посчитать сумму всех отрицательных
чисел для расчета суммарного
расхода по движению финансовых
средств. Этот результат будет позже
сравниваться вместе с сумой
положительных чисел с целью
верификации и вывода балансового
сальдо. Узнаем одинаковые ли
суммы доходов и расходов –
сойдется ли у нас дебит с кредитом.

30.

Задание
=СЕГОДНЯ() =СЛЧИСМЕЖДУ(0;1000)
Пример использования агрегационно логических функций, условного форматирования
и функции =СЕГОДНЯ()

31.

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

32.

Функции даты и времени
• Форматирование– это изменение внешнего вида документа и
его отдельных частей с целью придания ему лучшего восприятия,
удобочитаемости.
• Форматирование – это представление данных в визуальном
пространстве
Примечание: Важно понимать, что применение числового формата в ячейке никоим образом не
изменяет само число, которое там находится. Форматирование изменяет только внешний вид
отображаемого числового значения. Например, если в ячейке находится число 0,874543, его можно
отформатировать так, чтобы на экране оно выглядело как 87%. Но если на ячейку сделана ссылка в
формуле, то во время вычислений будет использоваться полное числовое значение (0,874543), а не
отображаемое (0,87).

33.

Функции даты и времени
• В приложении Excel все даты начиная с 1 января 1900 года хранятся в
виде числовой последовательности. Так, 1.01.1900 г. будет
соответствовать значение 1, а для 1.01.2009 г. будет эквивалентом
число 39 813, так как именно такое количество дней составляет
разница между двумя рассматриваемыми значениями
• Датам от 01.01.1900 до 31.12.9999 в EXCEL сопоставлены целые
положительные числа от 1 до 2958466.
• Если создать в программе Microsoft Excel ячейку в формате
«дата/время» и ввести туда 0, программа выведет 0 января 1900 года
• Истор.справка: до 1900 не упоминалось о структурированных данных

34.

Измерение
Как мерить часы:
• суткам соответствует число 1;
• 1 час – это 1/24 суток;
• 1 минута – 1/24/60;
• 1 сек – 1/24/60/60.
Примеры:
• число 0,5 тождественно 12:00.
• Времени 2:35:20 ( 2 часа 35 минут 20 секунд) соответствует число 0,10787
( 2 *1/24+ 35 *1/24/60+ 20 *1/24/60/60).
• Дате 14.01.2011 соответствует число 40557. Введем в ячейку 40557. Прибавим к 40557,
например, 0,75 и применим к ячейке формат ДД.ММ.ГГ ч:мм;@ получим 14.01.11 18:00 , т.к.
0,75 соответствует 3/4 суток, т.е. 6 часов вечера.
• Лист 11 прорешиваем

35.

Функции даты и времени
Есть список сотрудников с датами поступления их в штат.
Необходимо рассчитать 5-летний юбилей вступления в
должность.

36.

Задание task14
• Посчитаем сумму продаж по рабочим дням за 2016 и 2017 гг.
• Определяем для себя рабочие дни с пн по пт.
• Используем функции: ДЕНЬНЕД, ГОД, СУММЕСЛИМН

37.

Сдвиг даты (Урок 2)
• Создайте таблицу примерно на 300
значений, для заполнения
используем СЛЧИСМЕЖДУ
• Нужно получить первый день
предыдущего месяца
• Последний день

38.

Сдвиг даты
Первый день месяца в Excel
Для формулы, преобразующей текущую или другую дату в первый день месяца,
используем функции «ДАТА», «ГОД» и «МЕСЯЦ»:
Первыйдень
деньтекущего
текущегомесяца
месяцаототтекущей
текущейдаты:
даты:=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)
=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)
• •Первый
Первыйдень
деньпрошлого
прошлогомесяца
месяцаототтекущей
текущейдаты:
даты:=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())-1;1)
=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())-1;1)
• •Первый
Первыйдень
деньтекущего
текущегомесяца
месяцапредыдущего
предыдущегогода
годаототтекущей
текущейдаты:
даты:=ДАТА(ГОД(СЕГОДНЯ())-1;МЕСЯЦ(СЕГОДНЯ());1)
=ДАТА(ГОД(СЕГОДНЯ())-1;МЕСЯЦ(СЕГОДНЯ());1)
• •Первый
Первыйдень
деньмесяца
месяцаототпроизвольной
произвольнойдаты,
даты,записанной
записаннойв вячейку
ячейку«A1»:
«A1»:=ДАТА(ГОД(A1);МЕСЯЦ(A1);1)
=ДАТА(ГОД(A1);МЕСЯЦ(A1);1)
• •Первый
Рассчитать Возраст
Урок 2
фио
дата рожд
возраст
сотр 1
04.02.1998
24,0000000
сотр 2
15.09.2011
11,0000000
сотр 3
30.08.2009
13,0000000
сотр 4
28.05.2013
9,0000000
Задание Task19
чисторабочиедни

39.

Логические функции
• Логические функции это такие функции, результат работы которых имеет либо Истина, либо
Ложь, в соответствии с результатом условия выполняется то, или иное действие.
• логический оператор - это интегральная функция, которая объединяет результаты условий
• Кто автор алгоритмов?
• Какие методы проверок вы знаете?

40.

Методы проверок
Усл.1
Усл.2
Усл.1
Усл.3
да
да
нет
нет
Усл.3
Усл.2
да
нет
да
нет

41.

Операторы Excel
И – возвращает Истину, если все условия возвращают Истину
ИЛИ - возвращает Истину, если хотя бы одно условие возвращает Истину
НЕ – возвращает обратное булевое значение булевому условию

42.

Примеры Урок 2
Задача 1. Необходимо переоценить товарные остатки. Если продукт хранится на складе
дольше 8 месяцев, уменьшить его цену в 2 раза.
Задача 2. Если товар хранится дольше 8 месяцев, то его стоимость уменьшается в 2 раза. Если
дольше 5 месяцев, но меньше 8 – в 1,5 раза.
Задача 3. Если стоимость товара на складе после уценки стала меньше 300 р. или продукт хранится
дольше 10 месяцев, его списывают.

43.

Задание лист 12 Аттестация
Задание лист 11 Отработанное время

44.

Домашнее задание
• Группу в ТГ, всех добавить: староста
• Я выкладываю в группу домашние задания
• Вы выполняете, отправляете мне в личных сообщениях в ТГ
• Разбор ДЗ
• СУММЕСЛИ, СУММЕСЛИМН

45.

Урок 5
• дашборды

46.

Урок 5
• Что такое последовательная проверка
• Что такое параллельная проверка
• Какой тип данных ждет первый аргумент логической функции
ЕСЛИ
• Типы данных в Excel

47.

Типы данных в Excel
• Числовое значение
• Текст
• Формула
• Сообщение об ошибке
Наибольшее + число — 9,9Е+307.
Наименьшее + число — 2,2251Е-308
Наибольшее – число -- 2,2251Е-308
Наименьшее – число -- 9,9Е+307
Для представления чисел в Excel
используются 15 десятичных цифровых
позиций.
Например, если вы вводите большое число,
такое как
123 456 789 123 456 789 (18 цифр), Excel
сохраняет его с точностью до 15 цифр:
123 456 789 123 456 000.
На первый взгляд, такое ограничение может
показаться довольно жестким, но на практике
это редко вызывает какие-либо проблемы

48.

Текстовые функции (Лист 40)
• Когда вы вводите в ячейку данные, Excel сразу же определяет, что
именно вы вводите: формулу, число (включая дату и время) или
что-нибудь еще. Вот это "что нибудь еще" и воспринимается
программой как текст.
• Иногда вместо слова текст вы можете услышать понятие строка.
Эти термины имеют один и тот же смысл и обозначают
последовательность символов. Иногда эти два слова
употребляются вместе, например вы можете встретить такие
выражения, как текстовая строка или строка текста.
В одной ячейке может храниться до 32 тысяч символов.

49.

1
Текстовые функции
Excel предлагает большое количество
функций, с помощью которых можно
обрабатывать текст. Область применения
текстовых функций не ограничивается
исключительно текстом, они также могут
быть
использованы
с
ячейками,
содержащими числа.
Подстрока
2
СКОЛЬКО СИМВОЛОВ В 1 И 2 СТРОКАХ?
60% текстовых данных
обрабатывает финансовый аналитик

50.

Оператор конкатенации “&”
• Этот оператор указывает программе на необходимость
объединения двух текстовых строк

51.

Оператор конкатенации “&”
• При использовании для объединения в одну строку текстового и
числового значений простого оператора конкатенации исходное
форматирование последнего утрачивается.
• Чтобы решить эту проблему, следует вложить ссылку на ячейку с
исходным числовым значением в функцию ТЕКСТ, которая позволяет
применить к возвращаемому ей числовому значению необходимое
форматирование.
• =ВЗ&”: "&ТЕКСТ(СЗ;"$0 000")
• =ВЗ&”: "&ТЕКСТ(СЗ;"$# ###")
• =ТЕКСТ(99,21;"0%")
• =В34&": "&РУБЛЬ (СЗ, 0)

52.

Текстовые функции
• ТЕКСТ Преобразует числа в текст.
Синтаксис:
=текст(значение (числовое или ссылка на ячейку с формулой,
дающей в результате число); формат).
• Самая полезная возможность функции ТЕКСТ – форматирование
числовых данных для объединения с текстовыми данными. Без
использования функции Excel «не понимает», как показывать
числа, и преобразует их в базовый формат.

53.

Текстовые функции
1. ЛЕВСИМВ/ПРАВСИМВОЛ: Возвращает подстроку из
текста в порядке слева направо (справа налево) в
заданном количестве символов.
• Синтаксис: =ЛЕВСИМВ(текст; [кол-во_знаков])
Определения аргументов:
• текст – строка либо ссылка на ячейку, содержащую
текст, из которого необходимо вернуть подстроку;
• кол-во_знаков – необязательный аргумент. Целое
число, указывающее, какое количество символов
необходимо вернуть из текста. По умолчанию
принимает значение 1.
Пример использования:
Формула: =ЛЕВСИМВ("Произвольный текст";8) –
возвращенное значение «Произвол».
Формула: =ПРАВСИМВ("произвольный текст";5) –
возвращенное значение «текст».

54.

Текстовые функции
Функция ПСТР возвращает из указанной строки
часть текста в заданном количестве символов, начиная с
указанного символа.
Синтаксис: ПСТР(текст; начальная_позиция;
количество_знаков)
Определения аргументов:
• текст – строка или ссылка на ячейку, содержащую текст;
• начальная_позиция – порядковый номер символа,
начиная с которого необходимо вернуть строку;
• количество_знаков – натуральное целое число,
указывающее количество символов, которое необходимо
вернуть, начиная с позиции начальная_позиция.

55.

Текстовые функции НАЙТИ
НАЙТИ Возвращает число, являющееся вхождением первого
символа подстроки, искомого текста. Если текст не найден, то
возвращается ошибка «#ЗНАЧ!».
Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; [нач_позиция])
Определения аргументов:
искомый_текст – строка, которую необходимо найти;
текст_для_поиска – текст, в котором осуществляется поиск первого
аргумента;
нач_позиция – необязательный элемент. Принимает целое число,
которое указывает, с какого символа текст_для_поиска необходимо
начинать просмотр. По умолчанию принимает значение 1.
Пример использования:
Из отрывка стихотворения поэта С.А.Есенина находим вхождение
первого символа строки «птица». Поиск осуществляется с начала строки.
Если в приведенном примере поиск осуществлялся бы с 40 символа, то
функция в результате вернула ошибку, т.к. позиции вхождения не было
найдено.
Функция НАЙТИ() учитывает РЕгиСТР
букв и не допускает использование
подстановочных знаков(*,?). Для поиска
без учета регистра, а также для поиска
с использованием подстановочных
знаков пользуйтесь функцией ПОИСК() .

56.

Текстовые функции ПОИСК
Функция Поиск Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают
начальную позицию первой текстовой строки.
Синтаксис: ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция])
.=ПОИСК("н";"принтер")
Пример использования:
Данная функция используется для выделения подстроки из текста, например в наименовании товара (Конфеты
"Рафаело" в упаковке, 350 гр.) необходимо вычленить бренд Рафаело, это возможно в комбинации функции ПСТР
с функцией ПОИСК
Функция ищет без учета регистра

57.

Пример
• Лист 43 ,
• Task 12

58.

Функции поиска информации
• В MS Excel предусмотрено
несколько функций, которые
можно использовать для
создания формул выбора и
поиска значений в таблице
данных

59.

Функция ВПР
• ВПР (Vlookup, или вертикальный просмотр) — поисковая
функция в Excel. Она находит значения в одной таблице и
переносит их в другую.
• Функция ВПР - это одна из поисковых функций. Она используется
для выполнения вертикального поиска значения в крайнем
левом столбце таблицы или массива и возвращает значение,
которое находится в той же самой строке в столбце с заданным
номером.
• ГПР – горизонтальный просмотр

60.

Функция ВПР
Синтаксис
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
Аргументы обязательные!
•Искомое_значение Значение, которое должно быть найдено в первом столбце таблицы или диапазона. Аргумент
искомое_значение может быть значением или ссылкой. Если искомое_значение меньше, чем наименьшее значение
в первом столбце аргумента таблица, функция ВПР возвращает значение ошибки #Н/Д.
•Таблица Диапазон ячеек, содержащий данные. Можно использовать ссылку на диапазон (например, A2:D8) или
имя диапазона. Значения в первом столбце аргумента таблица — это значения, в которых выполняется поиск
аргумента искомое_значение. Эти значения могут быть текстовыми, числовыми или логическими. Текстовые значения
в нижнем и верхнем регистре считаются эквивалентными.
•Номер_столбца Номер столбца в аргументе таблица, из которого возвращается совпадающее значение. Если
номер_столбца равен 1, то возвращается значение из первого столбца аргумента таблица; если номер_столбца
равен 2, — значение из второго столбца аргумента таблица и т. д.
Если значение аргумента номер_столбца:
•меньше 1, функция ВПР возвращает значение ошибки #ЗНАЧ!;
•больше, чем число столбцов в аргументе таблица, функция ВПР возвращает значение ошибки #ССЫЛ!.

61.

Функция ВПР
•Интервальный_просмотр Необязательный. Логическое значение,
определяющее, какое совпадение должна найти функция ВПР — точное
или приблизительное.
• Если аргумент интервальный_просмотр имеет значение ИСТИНА
или опущен, то возвращается точное или приблизительное
совпадение. Если точное совпадение не найдено, то возвращается
наибольшее значение, которое меньше, чем искомое_значение.
Важно. Если аргумент интервальный_просмотр имеет значение
ИСТИНА или опущен, значения в первом столбце аргумента таблица
должны быть расположены в возрастающем порядке, иначе функция
ВПР может вернуть неправильный результат.

62.

Функция ВПР: Задание

63.

ВПР Правила
Правило 1: Второй аргумент - это диапазон, при выделении
диапазона первый столбец таблицы должен содержать данные
Правило 2: В выделенный диапазон должны попасть значения для
подстановки
Старайтесь не использовать приближенный межинтервальный
просмотр!
Лист 42 Заполняем данными магазин 1 и 2

64.

Функция ВПР
Магазин 3 – что делать?
=ИНДЕКС($B$27:$B$36;ПОИСКПОЗ(A47;$C$27:$C$36;0))
• функция ИНДЕКС: Возвращает
значение или ссылку на
значение из таблицы или
диапазона. (в примере про
магазины мы найдем
значение 66)
• функция ПОИСКПОЗ: выполняет
поиск указанного элемента в
диапазоне. Ячейки диапазона
могут быть как смежными, так и
несмежными. (в примере
найдем какая позиция у яблок в
искомой таблице )

65.

функция ПОИСКПОЗ
Тип сопоставления
Поведение
1) Правило размерности массивов
2) Вертикальный массив определяется строками
1 или опущен Функция ПОИСКПОЗ находит наибольшее значение, которое
меньше или равно значению аргумента искомое_значение.
Просматриваемый_массив должен быть упорядочен по
возрастанию: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА.
0
Функция ПОИСКПОЗ находит первое значение, равное
аргументу искомое_значение. Просматриваемый_массив
может быть не упорядочен.
-1
Функция ПОИСКПОЗ находит наименьшее значение, которое
больше или равно значению аргумента искомое_значение.
Просматриваемый_массив должен быть упорядочен по
убыванию: ИСТИНА, ЛОЖЬ, Z-A, ..., 2, 1, 0, -1, -2, ... и т. д.

66.

функция ГПР и ЕСЛИОШИБКА
• Магазин 4. Считаем через ГПР
ЕСЛИОШИБКА Описание
• Данная функция возвращает указанное значение, если вычисление
по формуле вызывает ошибку; в противном случае функция
возвращает результат формулы. Функция ЕСЛИОШИБКА позволяет
перехватывать и обрабатывать ошибки в формулах.
Синтаксис: ЕСЛИОШИБКА (значение; значение_при_ошибке)
• Примечание
• Если "значение" или "значение_при_ошибке" является пустой ячейкой, функция
ЕСЛИОШИБКА рассматривает их как пустые строковые значения ("").
• Если "значение" является формулой массива, функция ЕСЛИОШИБКА возвращает массив
результатов для каждой ячейки диапазона, указанного в значении.

67.

Массивы
• Массивы в Excel — это данные из
двух и более смежных ячеек
таблицы, которые используют в
расчётах как единую группу,
одновременно.
• Массивом может быть одна
строка или столбец
(одномерные), несколько строк
или столбцов и даже целые
таблицы (двумерные).
• Операции с массивами делают
работу с большими диапазонами
значений удобнее и быстрее.
С помощью массивов можно
проводить расчёты
не поочерёдно с каждой ячейкой
диапазона, а со всем
диапазоном одновременно.
• Или создать формулу, которая
выполнит сразу несколько
действий с любым количеством
ячеек.

68.

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

69.

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

70.

Массивы
Задание №1
Задание №2
Горизонтальный
Вертикальный
{1;2;3;4;5;6;7;8;9}
{1:2:3:4:5:6:7:8:9}
Чтобы Excel воспринял нашу формулу
как формулу массива жмем Ctrl + Shift
+ Enter
Фигурные скобки - отличительный
признак формулы массива. Вводить их
вручную с клавиатуры бесполезно - они
автоматически появляются при
нажатии Ctrl + Shift + Enter.
Есть специальная функция Excel для работы с двумерными массивами. Функция
«ТРАНСП» возвращает сразу несколько значений. Преобразует горизонтальную
матрицу в вертикальную и наоборот.

71.

Создание массива констант
Этот тип массивов, хранится только в памяти компьютера
=СУММ({1;0;1;0;1})
• В формуле используется функция сумм, в качестве аргумента которой
указан этот массив {1;0;1;0;1}.
Эта формула возвращает сумму элементов данного массива (число 3).
в формуле используется массив, сама она не является формулой
массива. Поэтому не следует пользоваться комбинацией клавиш для
ввода такой формулы.

72.

СУММ({1; 2; 3; 4} * {5; 6; 7; 8})
{5; 12; 21; 32} 70
{=СУММ((A1:D1*{1;2;3;4})) }
A1=2, B1=3,C1=2,D1=5
{2; 6; 6; 20} 34
• Массивы констант могут содержать числа, текст, логические
значения (истина и ЛОЖЬ) и даже значения ошибок, например
#Н/Д.
• Числа могут быть целыми, десятичными дробями или
представленными в экспоненциальном формате.
• Текст в массивах должен быть заключен в двойные кавычки.
• В одном массиве констант могут содержаться значения разного
типа, например (1;2;3;ИСТИНА;ЛОЖЬ;"Май";"Коля";"Иванов"}
• Массив констант не может содержать формулы, функции и другие
массив

73.

Массивы
Задание №3
Создайте таблицу умножения
при помощи массива
Изменение массива
Задание №4 Лист 48
Способ2, способ3, способ4

74.

Подбор параметра
• Если результат, который необходимо получить при вычислении
формулы, известен, но неясно, какое входное значение
необходимо для получения этого результата, можно использовать
средство Подбор параметра.
• ! Если формула зависит от нескольких входных параметров,
средство Подбор параметра позволяет указать только один
параметр, изменение значения которого позволит подобрать
необходимый результат вычисления формулы.
• Лист33

75.

Условное форматирование
• Условное форматирование - лист
• Условное форматирование - файл

76.

подбора параметра
• задача: Через сколько километров, за счет более дешевого
топлива окупятся расходы на приобретение газового
оборудования, если оно стоит 10000р.? Литр бензина стоит
10р., литр газа 4р. потребление газа на 100км 11литров,
бензина 8 литров.

77.

Урок №4
• Практическая работа
\\PDC\transport\Информатика МТИД\Практическая
работа
Результаты в файл с ФИО, в папку Информатика МТИД,
но с текущей датой

78.

Задание №1.
Поиск позиции и массив
Создать карточку заказа, где по номеру
артикула можно будет видеть, что это за
товар, какой клиент его приобрел,
сколько было куплено и по какой общей
стоимости. Сделать это поможет
функция ИНДЕКС совместно
с ПОИСКПОЗ.
• создадим выпадающий список для
поля АРТИКУЛ ТОВАРА

79.

Задание №2
• Имеем список заказов с
номерами и названиями
товаров. Необходимо
вытаскивать из таблицы
по номеру заказа все
товары, которые в него
входят.
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИМЕНЬ
ШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)1;"");СТРОКА()-5));"")

80.

Задание №3
• написать формулу, которая
просуммирует продажи для
клиента и указанного периода.

81.

Задание №4
• Заполните столбец A именем.

82.

Именованные диапазоны
• Обычно ссылки на диапазоны ячеек вводятся непосредственно в
формулы, например =СУММ(А1:А10) .
• Другим подходом является использование в качестве ссылки
имени диапазона =СУММА(ИТОГ).
• Использование имен позволяет значительно упростить
понимание и изменение формул. Вы можете задать имя для
диапазона ячеек, функции, константы или таблицы. Начав
использовать имена в книге, можно с легкостью обновлять,
проверять имена и управлять ими.

83.

Синтаксические правила для имен
• Пробелы в имени не допускаются. В качестве разделителей слов используйте
символ подчеркивания (_) или точку (.), например, «Налог_Продаж» или
«Первый.Квартал».
• Допустимые символы. Первым символом имени должна быть буква, знак
подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут
быть буквами, цифрами, точками и знаками подчеркивания.
• Нельзя использовать буквы "C", "c", "R" и "r" в качестве определенного имени,
так как эти буквы используются как сокращенное имя строки и столбца
выбранной в данный момент ячейки при их вводе в поле Имя или Перейти .
• Имена в виде ссылок на ячейки запрещены. Имена не могут быть такими же,
как ссылки на ячейки, например, Z$100 или R1C1.
• Длина имени. Имя может содержать до 255-ти символов.
• Учет регистра. Имя может состоять из строчных и прописных букв. EXCEL не
различает строчные и прописные буквы в именах.

84.

Синтаксические правила для имен
в качестве имен не следует использовать следующие специальные
имена:
• Критерии – это имя создается автоматически Расширенным
фильтром ( Данные/ Сортировка и фильтр/ Дополнительно );
• Извлечь и База_данных – эти имена также создаются
автоматически Расширенным фильтром ;
• Заголовки_для_печати – это имя создается автоматически при определении
сквозных строк для печати на каждом листе;
• Область_печати – это имя создается автоматически при задании области
печати.
Если Вы в качестве имени использовали, например, слово Критерии с областью
действия Лист1, то оно будет удалено при задании критериев для Расширенного
фильтра на этом листе (без оповещения).

85.

Именованные диапазоны
Именованные диапазоны в Excel создавать выпадающие списки в
пункте Проверка данных.
Создание выпадающего списка
• Чтобы создать выпадающий список, выделите ячейку, где он
должен появиться (или группу ячеек) и перейдите на вкладку
Данные -> Проверка данных.

86.

Именованные диапазоны
Открываем Task13:
Способы создания:
1. Выделяем A1:B12 - создать из выделенного
2. Выделяем A1:C1 – через строку формул
3. Выделяем C2:C5 -через ПК (Ограничения по листам)
4. Через Ленту

87.

Технология управления справочниками
• Любой анализ данных начинается со сбора информации и
составления справочников.
• НСИ – нормативно справочные информация или master data, или мастерданные, или основные данные - это условно-постоянная часть всей
корпоративной информации
• MDM Master Data Manager - система управления данными
Информацию собирает пользователь (ему все равно «Банан», «Бананы», «Банан_»,
«__Банан»)
• Через имена и проверку данных можно настроить ограничения
для пользователя
• Task 13 (номера телефона и Фрукты)

88.

Функция ДВССЫЛ
• ССЫЛКИ: Диапазон; Листы; Книги
=[пример.xlsx]Лист1!$A$1
• ДВССЫЛ-возвращает ссылку на ячейку(и), заданную текстовой строкой .
• Например, формула = ДВССЫЛ("Лист1!B3") эквивалентна формуле
= Лист1!B3 .
• Мощь этой функции состоит в том, что саму ссылку ( Лист1!B3 ) также
можно изменять формулами, ведь для ДВССЫЛ() это просто текстовая
строка!
• С помощью этой функции можно транспонировать таблицы, выводить
значения только из четных/ нечетных строк, складывать цифры числа и
многое другое.
• ДВССЫЛ - это написание ссылки программным способом.

89.

Синтаксис функции
• ДВССЫЛ( ссылка_на_ячейку ;a1 )
• Ссылка_на_ячейку — это текстовая строка в формате ссылки (т.е.
указаны столбец и строка)
• Второй аргумент а1 — это логическое значение (ИСТИНА или ЛОЖЬ),
указывающее, какого типа ссылка содержится в
аргументе Ссылка_на_ячейку .
= ДВССЫЛ("B3") или = ДВССЫЛ("Лист1!B3") или =ДВССЫЛ("[Книга1.xlsx]Л
ист1!B3") . Первая формула эквивалентна формуле = B3 , вторая = Лист1!B3 , третья = [Книга1.xlsx] Лист1!B3
• Если вы делаете ссылку в другой файл, то она работает только пока
исходный файл открыт. Если его закрыть, то получим ошибку
#ССЫЛКА!

90.

Несбиваемые ссылки
• Excel автоматически корректирует
адреса ссылок в формулах при
вставке или удалении строкстолбцов на лист. В большинстве
случаев это правильно и удобно, но
не всегда.
• Если ставить обычные ссылки (в
первую зеленую ячейку ввести =B9 ),
то потом при удалении, например,
получим в соответствующей ей
зеленой ячейке ошибку #ССЫЛКА!. В
случае применения для создания
ссылок функции ДВССЫЛ такой
проблемы не будет.

91.

Транспонирование списка
Нужно превратить вертикальный диапазон в горизонтальный (транспонировать).
Как это сделать с функцией ДВССЫЛ?
Чтобы получить адрес
очередной ячейки, мы
склеиваем спецсимволом "&"
букву "А" и номер столбца
текущей ячейки, который
выдает нам функция СТОЛБЕЦ

92.

Суммирование по интервалу
• Если нам нужно
суммировать данные
только из
определенного
диапазона-периода, то
можно склеить его из
кусочков и превратить
затем в полноценную
ссылку, которую и
вставить внутрь
функции СУММ

93.

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

94.

Сводные таблицы (СТ)
Работа с данными происходит через разные измерения.
Какие измерения вы знаете?
y
10 000
Что такое 10 000?
Сами по себе данные не имеют значения,
нет интерпретации, нет контекста!
х
z
Значения появляются в контексте, если х, y, z
- магазин, покупатель, товар 10 000
выручка, т.е. значение по денежной шкале

95.

Сводные таблицы (СТ)
• СТ – введение в BI (Business Intelligence.)
• СТ – инструмент для разработки многомерных отчетов.
Работа с данными происходит через разные измерения.
Какие измерения вы знаете?
y
10 000
Значения появляются в контексте, если х, y, z
- магазин, покупатель, товар 10 000
выручка, т.е. значение по денежной шкале
х
z
Что такое 10 000?
Сами по себе данные не имеют значения,
нет интерпретации, нет контекста!

96.

СТ – это BI средство
Business Intelligence
• Термин Business Intelligence был введен аналитиками Gartner как «процесс,
ориентированный на бизнес-пользователя и включающий доступ и
исследование информации, ее анализ, выработку интуиции и понимания,
которые ведут к улучшенному и неформальному принятию решений».
• Business Intelligence (бизнес-аналитика, бизнес-анализ) - программное
обеспечение, созданное для помощи управленцу в анализе информации о
своей компании и её окружении. BI-технологии позволяют анализировать
большие объёмы информации, заостряя внимание пользователей лишь на
ключевых факторах эффективности, моделируя исход различных вариантов
действий, отслеживая результаты принятия тех или иных решений.
• Сегодня под Business Intelligence понимают главным образом технологии,
связанные с хранением и анализом фактографической структурированной
информации (базы данных, плоские файлы и т.п.) и квазиструктурированной
информации (XML).

97.

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

98.

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

99.

Какая таблица не подойдет для построения
СТ?
Таблица №1
Таблица №2

100.

Сводная таблица

101.

Сводная таблица
• Область значений – это центральная часть сводной таблицы со значениями, которые
получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования. Если все данные в выбранном поле
имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть
хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество ячеек.
(В ячейках сводной таблицы можно использовать и другие способы вычисления)
• Область строк – названия строк, которые расположены в крайнем левом столбце. Это все
уникальные значения выбранного поля (столбца). В области строк может быть несколько
полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные
переменные типа названий продуктов, месяцев, регионов и т.д.
• Область столбцов – аналогично строкам показывает уникальные значения выбранного
поля, только по столбцам. Названия столбцов – это также обычно качественный признак.
Например, годы и месяцы, группы товаров.
• Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом
отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то
отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают
поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.

102.

Задание task18
• Построим СТ и разместим ее на
новый лист.
1. Продажи по магазинам
2. Продажи по магазинам и группам
3. Продажи по магазинам и группам и
подгруппам
(Альфа…бакалея…консервы)
группировку в выбранном контексте

103.

Задание task18
• Смотрим агрегационные функции, числовой формат.
• Промежуточные итоги, макеты отчетов
• Сортировка данных внутри группы

104.

Создание новых измерений СТ
• Создадим СТ магазины….даты….продажи
• Гранулярность – уровень детализации
• Смотрим группировку по годам, месяцам, неделям

105.

Новый лист возраст

106.

Срезы/контексты
• СТ – это BI средство
• Данные хранятся в
многомерном представлении.
Таблицы (источник) это
плоское представление
данных, медленная обработка.
• Главный механизм СТ – это
фильтрация.
• Контекст фильтра –
определяется конкретным
значением измерения:
• Строками отчета
• Столбцами отчета
• Срезами отчета

107.

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

108.

109.

Фильтрация СТ с помощью временной
шкалы timeline
• Временные шкалы (timeline)
концептуально подобны срезам,
но предназначены для
упрощения фильтрации данных в
сводных таблицах по временным
показателям.
• Временные шкалы применимы
только в том случае, если в
сводной таблице имеется поле,
отформатированное как дата.
• Это средство не работает с
данными времени.
• Для того чтобы добавить временную
шкалу, выберите ячейку в сводной
таблице и выполните команду
Вставка…Фильтры…ременная шкала.
В диалоговом окне появится
перечень всех полей,
отформатированных как даты. Если
ваша сводная таблица не содержит
поле, отформатированное как дата,
Excel выведет сообщение об ошибке.

110.

111.

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

112.

• Вычисляемое поле
Вычисляемый элемент.

113.

задача
• Рассчитать сумму НДС по
магазинам и кварталам
• Добавляем вычисляемы
столбец

114.

Создание сводных диаграмм
• Сводные диаграммы — это графическое представление данных,
обобщенных в сводных таблицах. Все возможности обычных
диаграмм распространяются и на сводные диаграммы.
________________________________________________________
• Между сводной таблицей и сводной диаграммой программа Excel
устанавливает двухстороннюю связь. Поэтому, если внести какие-либо
структурные изменения в один из этих объектов, эти изменения сразу
же будут отображены и на другом объекте.
• При активизации сводной диаграммы панель Поля сводной таблицы
заменяется панелью Поля сводной диаграммы. На этой панели
область Условные обозначения (Ряды) заменяет область Столбцы, а
область Ось (Категории) — область Строки.

115.

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

116.

• На основе одной сводной таблицы можно создать любое
количество сводных диаграмм, при этом каждую из этих
диаграмм можно настраивать и форматировать независимо от
другой. Однако на всех этих диаграммах будут отображаться одни
и те же данные
• При выборе обычной диаграммы справа отображаются три
пиктограммы: Элементы диаграммы, Стили диаграммы и
Фильтры диаграммы. В случае сводных диаграмм пиктограмма
Фильтры диаграммы не отображается
• К сводным таблицам можно также применять срезы и временную
шкалу.

117.

Связанные выпадающие списки
• Связанные выпадающие списки – это списки, в которых
выпадающие значения появляются не «просто так», а в
зависимости от уже заполненных данных. Так, для выбранной
группы появится только список входящих в неё наименований.
=ДВССЫЛ("Источник[#Заголовки]")
=ДВССЫЛ("Источник["&$G2&"]")

118.

СМЕЩ
• У именных списков необходимо править диапазон после каждого
добавления/удаления строк данных в исходном диапазоне.
• Чтобы избежать подобной ситуации, можно создать динамический
диапазон, применив формулы вместо жёстко заданных координат.
Чаще всего используется функция СМЕЩ.
• Данная функция возвращает ссылку на диапазон, отстоящий от ячейки
или диапазона ячеек на заданное число строк и столбцов.
Возвращаемая ссылка может быть отдельной ячейкой или диапазоном
ячеек. Можно задавать количество возвращаемых строк и столбцов.
• Причина, по которой мы используем СМЕЩ — возможность замены
параметров (аргументов) формулами. Они и позволяют получить
динамическую часть.

119.

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

120.

СМЕЩ
• Синтаксис: СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])
• Ссылка — обязательный аргумент. Ссылка, от которой вычисляется смещение. Аргумент "ссылка" должен быть
ссылкой на ячейку или на диапазон смежных ячеек, в противном случае функция СМЕЩ возвращает значение
ошибки #ЗНАЧ!.
• Смещ_по_строкам Обязательный. Количество строк, которые требуется отсчитать вверх или вниз, чтобы левая
верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента
"смещ_по_строкам" задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть
на пять строк ниже, чем указано в аргументе "ссылка". Значение аргумента "смещ_по_строкам" может быть как
положительным (для ячеек ниже начальной ссылки), так и отрицательным (выше начальной ссылки).
• Смещ_по_столбцам Обязательный. Количество столбцов, которые требуется отсчитать влево или вправо, чтобы
левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента
"смещ_по_столбцам" задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть
на пять столбцов правее, чем указано в аргументе "ссылка". Значение "смещ_по_столбцам" может быть как
положительным (для ячеек справа от начальной ссылки), так и отрицательным (слева от начальной ссылки).
• Высота Необязательный. Высота (число строк) возвращаемой ссылки. Значение аргумента "высота" должно
быть положительным числом.
• Ширина Необязательный. Ширина (число столбцов) возвращаемой ссылки. Значение аргумента "ширина"
должно быть положительным числом.

121.

СМЕЩ
• Примечания
• Если аргументы "смещ_по_строкам" и "смещ_по_столбцам"
выводят ссылку за границы рабочего листа, функция СМЕЩ
возвращает значение ошибки #ССЫЛ!.
• Если высота или ширина опущена, то предполагается, что
используется та же высота или ширина, что и в аргументе
"ссылка".
• Функция СМЕЩ фактически не передвигает никаких ячеек и не
меняет выделения; она только возвращает ссылку.

122.

• Функция СМЕЩ может использоваться с любой функцией, в
которой ожидается аргумент типа "ссылка". Например, с
помощью формулы СУММ(СМЕЩ(C2;1;2;3;1)) вычисляется
суммарное значение диапазона, состоящего из трех строк и
одного столбца и высотой в 3 строки, шириной в 1 столбец

123.

Задача
• Посчитать кол-во обращений каждого клиента