Работа в Excel. Расширенные возможности

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

Московский Государственный Технический Университет им. Н.Э. Баумана
[email protected]

2.

Операторы
Знаки операция и раскладка клавиатуры, в которых имеется возможность их использовать
Арифметические операторы
Операторы сравнения
>
+
Сложение
-
Вычитание
>=
Больше и равно
*
/
Умножение
<
<=
<>
Меньше
^
Деление
Возведение в степень
Другие
Больше
Меньше и равно
Не равно
()
приоритетные действия
=
&
знак равенства
{}
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]
объединение текстовых строк
формула массива (устанавливаются по
средством использования сочетания
клавиш для формулы массива)

3. Алгоритм написания формулы в ячейке

Выделить ячейку, в которой необходимо прописать формулу
Формула начинается со знака «=»
При написании формулы с клавиатуры, знак «=» устанавливается посредством
клавиши на клавиатуре
При выборе любой функции из группы «Библиотека функций», знак «=»
устанавливается самостоятельно с именем выбранной функцией
Формула прописывается с использованием ссылок на ячейки
Завершается ввод формулы нажатием на клавишу Enter
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

4.

В Excel используется библиотека встроенных функций
Вставка функции:
1.
2.
-
Выделить ячейку для получения результата
Вызвать мастер функций одним из вариантов:
Нажать
на строке формул или
Нажать
с вкладки Формулы.
- Shift+F3.
3. Ввести функцию в поле Поиск функций и нажать кнопку Найти или
установить Категорию и выбрать функцию, затем кнопку ОК.
4. Ввести аргументы функции.
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

5.

Вставка функции:
1. Выделить ячейку для получения результата.
2. Ввести знак равно с клавиатуры.
3. Ввести с клавиатуры первые буквы имени функции.
4. Выбрать нужную функцию из предлагаемого списка.
5. Щелкнуть дважды по нужной функции и ввести аргументы функции.
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

6. Использование различных Типов ссылок в расчётах. Относительный Тип ссылок

Относительная ссылка
C3
При использовании такого типа ссылки при
дальнейшем копировании формулы
влево/вправо/вниз/вверх адрес ячейки будет
изменяться в сторону направления копирования
С1
С2
А3
В3
С3
D3
С4
С5
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]
E3

7. Использование различных Типов ссылок в расчётах. Абсолютный Тип ссылки

Абсолютная ссылка
$C$3
При использовании такого типа ссылки при
дальнейшем копировании формулы
влево/вправо/вниз/вверх адрес ячейки изменяться
не будет в сторону направления копирования
$С$3
$С$3
$C$3
$С$3
$С$3 $C$3
$С$3
$С$3
$C$3
Переход к Абсолютному типу ссылки: F4 – клавиша функционального ряда
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

8. Использование различных Типов ссылок в расчётах. Смешанный Тип ссылок (закрепление СТРОКИ)

Смешанная ссылка (закрепление СТРОКИ)
C$3
При использовании такого типа ссылки при
дальнейшем копировании формулы влево/вправо/
адрес ячейки будет изменяться; при дальнейшем
копировании формулы вниз/вверх адрес ячейки не
будет изменяться
С$3
С$3
А$3
В$3
С$3
D$3
E$3
С$3
С$3
Переход к Абсолютному типу ссылки: F4 – клавиша функционального ряда
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

9. Использование различных Типов ссылок в расчётах. Смешанный Тип ссылок (закрепление СТОЛБЦА)

Смешанная ссылка (закрепление СТОЛБЦА)
$C3
При использовании такого типа ссылки при
дальнейшем копировании формулы влево/вправо/
адрес ячейки не будет изменяться; при
дальнейшем копировании формулы вниз/вверх
адрес ячейки будет изменяться
$С1
$С2
$C3
$C3
$С3
$C3
$C3
$С4
$С5
Переход к Абсолютному типу ссылки: F4 – клавиша функционального ряда
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

10. F4: клавиша функционального ряда для создания нужного типа ссылки

C3
F4
$C$3
F4
C$3
F4
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]
$C3
F4
C3

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

Алгоритм действия при использования ССЫЛКИ на ДРУГОЙ ЛИСТ книги:
Установить «=»
Создать ссылку на нужную ячейку
Мышкой перейти на нужный лист книги
Указать ячейку с нужным значением на листе перехода
Указать нужный тип ссылки (по умолчанию всегда относительный)
Enter или (Ctrl+Enter)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

12. Ссылки на ячейки других листов другой книги

Алгоритм действия при использования ССЫЛКИ на ячейки листов ДРУГОЙ книги:
Файл, который участвует в расчетах должен быть ОТКРЫТ!!!
Установить «=»
Создать ссылку на нужную ячейку
Мышкой перейти на ФАЙЛ
Мышкой выбрать лист в файле перехода
Мышкой выбрать нужную ячейку (изначально будет в абсолютном типе ссылке)
Enter (Ctrl+Enter)
Московский Государственный Технический Университет им. Н.Э. Баумана
[email protected]

13. Математические функции

ОТБР, ЦЕЛОЕ, ОКРУГЛ, СУММЕСЛИ, СУММЕСЛИМН,
СУММПРОИЗВ;
ОТБР(Число;Число_разрядов) - отбрасывает дробную часть числа.
(Независимо от знака числа)
= ОТБР(5,56748;2)
5,56
= ОТБР(-5,56748;2)
-5,56
ОКРУГЛ(Число;Число_разрядов) – округляет число до указанного
количества десятичных разрядов (по правилам математики).
=ОКРУГЛ(5,56748;2)
5,57
ЦЕЛОЕ(Число) – округляет число до ближайшего меньшего целого
=ЦЕЛОЕ(5,56748)
5
=ЦЕЛОЕ(-5,56748)
-6
Московский Государственный Технический Университет им. Н.Э. Баумана
[email protected]

14. Математические функции

ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ, ОКРВНИЗ, ОКРВВЕРХ
ОКРУГЛВВЕРХ(Число;Число_разрядов) – округляет вверх до ближайшего
большего по модулю до указанного количества десятичных разрядов.
=ОКРУГЛВВЕРХ(5,56765;2)
5,57
ОКРУГЛВНИЗ(Число;Число_разрядов) округляет вниз до ближайшего
большего по модулю до указанного количества десятичных разрядов.
=ОКРУГЛВНИЗ(5,56765;2)
5,56
ОКРВНИЗ(Число;Точность) – округляет число до ближайшего меньшего по
модулю целого, кратного указанному значению.
=ОКРВНИЗ(55,56765;10)
50
ОКРВВЕРХ(Число;Точность) – округляет число до ближайшего меньшего по
модулю целого, кратного указанному значению.
=ОКРВВЕРХ(55,56765;10)
60
Московский Государственный Технический Университет им. Н.Э. Баумана
[email protected]

15. Математические функции

СУММЕСЛИ
СУММЕСЛИ(Диапазон;Критерий;Диапазон_суммирования)
Суммирует ячейки, заданные указанным условием
Диапазон – диапазон условий.
Критерий – критерий отбора
Диапазон суммирования – диапазон суммируемых ячеек
Пример: Рассчитать количество продаж модемов
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

16. Особенности ввода условия (отбора) значений

• Условие/условия отбора (критерии) могут содержать операторы сравнения:
• Больше (>)
• Меньше (<)
• Больше равно (>=)
• Меньше равно (<=)
• Не рано (<>)
• Для текстовых значений
• ? – замена 1-го символа
• * замена символов
• Примеры использования символов
• К?т – слово из 3-х букв, где первая – к, третья – т и обязательно один символ между ними.
Варианты: кот, кит и т.д.
• *дом – заканчивается на дом
• Дом* - начинается с дом
• *дом* содержит дом
• Условия сравнения со ссылкой на ячейку: «оператор сравнения» (т.к. текст, заключается в
кавычки)&Ячейка
• Например: «>=»&F4
• Порядок: имеет значения последовательность Диапазона условия и Условия
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

17. Математические функции

СУММЕСЛИМН
СУММЕСЛИМН(Диапазон_суммирования;Диапазон_условия;Условие) Суммирует
ячейки, удовлетворяющие заданному набору условий.
Диапазон суммирования – диапазон суммируемых ячеек
Диапазон_условия– диапазон ячеек, где ответ на условие (критерий).
Условие - условие отбора
Пример: Рассчитать количество продаж модемов с 16.09.2016 года
На строке формул:
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

18. Статистические функции

СЧЕТ, СЧЕТ3, СЧЕТЕСЛИ, СЧЕТЕСЛИМН, СЧИТАТЬПУСТОТЫ,
СРЗНАЧЕСЛИМН
СЧЁТ(Значение1;Значение2;)
Подсчитывает количество ячеек в диапазоне, которые содержат числа
=СЧЕТ(10;20;количество)
2
СЧЁТ3(Значение1;Значение2;)
Подсчитывает количество непустых ячеек в диапазоне
СЧЕТ3(10;20;количество)
3
СЧЁТЕСЛИ(Диапазон;Критерий)
Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному
условию.
Диапазон – диапазон ячеек, где ответ на критерий (условие).
Критерий – критерий отбора (условие).
Московский Государственный Технический Университет им. Н.Э. Баумана
[email protected]

19. Статистические функции

СЧЕТЕСЛИ
Пример: Подсчитать количество ячеек в столбце В, содержащих слово Модем
В строке формул: =СЧЁТЕСЛИ(B2:B9;B8)
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

20. Статистические функции

СЧЕТЕСЛИ
Пример: Посчитать количество ячеек в столбце D c датой продажи после 16.09.2016
=СЧЁТЕСЛИ(D2:D9;">16.09.2016")
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

21. Статистические функции

СЧЕТЕСЛИМН
СЧЁТЕСЛИМН(Диапазон_условия;Условие;) – подсчитывает количество ячеек в
диапазоне, удовлетворяющих заданному набору условий.
Диапазон – диапазон ячеек, где ответ на условие. Условие – условие отбора
Пример: Рассчитать количество продаж модемов, начиная с 16.09.2016
=СУММЕСЛИ(B2:B9;B8;C2:C9)
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

22. Статистические функции

СРЗНАЧЕСЛИМН
СРЗНАЧЕСЛИМН (Диапазон_усреднения;Диапазон_условия;Условие;)
Подсчитывает среднее арифметическое для ячеек, удовлетворяющих заданному
набору условий.
Диапазон_усреднения – диапазон ячеек для расчета среднего значения.
Диапазон_условия – диапазон ячеек, где ответ на условие (критерий).
Условие – условие отбора.
Пример: Рассчитать среднее значение количества проданных модемов начиная с
16.09.2016
=СРЗНАЧЕСЛИМН(C2:C9;B2:B9;B6;D2:D9;">=16.09.2016")
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

23. Статистические функции

СЧИТАТЬПУСТОТЫ
СЧИТАТЬПУСТОТЫ (Диапазон)
Подсчитывает количество пустых ячеек в диапазоне.
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

24. Логические функции

Логическое выражение:
обязательный аргумент, является основным
условием задачи (стаж >3; количество детей >=2)
Значение если истина:
необязательный аргумент, однако требует
заполнения, так как при отсутствии в нем данных в ячейку будет возвращаться
значение ИСТИНА. Этот аргумент является неким событием, которое происходит в
следствии выполнения основного условия задачи. Например, если сотрудник имеет
стаж работы >3 лет, то его поощрительная премия 45% от размера оклада.
Значение если ложь: необязательный
аргумент, однако требует заполнения,
так как при отсутствии в нем данных в ячейку будет возвращаться значение ЛОЖЬ.
Этот аргумент является неким событием, которое происходит в следствии не
выполнения основного условия задачи. Например, если сотрудник имеет стаж
работы <=3 лет, то его поощрительная премия 25% от оклада
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

25. Логические функции

ЕСЛИ, ИЛИ
Пример: Определить кому полагается подарок.
Подарок выдается только в случаях, если стоимость заказа на сумму не менее
3000 рублей или количество наименований в заказе не менее 5.
=ЕСЛИ(ИЛИ(C2>=3000;D2>=5);"Подарок";"")
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

26. Логические функции

И
И(логическое_значение1;логическое_значение2…)
Функция И() используется тогда, когда необходимо проверить несколько условий
следующим образом - Условие 1 И Условие 2. При этом все условия должны быть
истинными. Результатом работы данной функции является ИСТИНА или ЛОЖЬ).
Пример: ЕСЛИ а = b И а=с ТОГДА значение 1 ИНАЧЕ значение 2.
Московский Государственный Технический Университет им. Н.Э. Баумана
[email protected]

27. Логические функции

ЕСЛИОШИБКА
ЕСЛИОШИБКА(Значение;Значение_если_ошибка)
Возвращает указанное значение, если вычисление по формуле вызывает ошибку, в
противном случае возвращает результат формулы.
Значение – формула, проверяемая на ошибки.
Значение_если_ошибка – значение при ошибке.
Пример: Вычислить среднюю сумму заказов фирм.
=ЕСЛИОШИБКА(C2/D2;"НЕТ ЗАКАЗОВ")
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

28. Вложенные функции ЕСЛИ В ЕСЛИ

Логические функции
Вложенные функции ЕСЛИ В ЕСЛИ
Московский Государственный Технический Университет им. Н.Э.
Баумана [email protected]

29. Текстовые функции

ЛЕВСИМВ, ПРАВСИМВ
ЛЕВСИМВ(Текст;Количество_знаков) – выдает указанное количество символов с
начала строки текста.
Пример:
В ячейке А1 написано слово Профессор
= ЛЕВСИМВ(А1;4) будет Проф
ПРАВСИМВ(Текст;Количество_знаков) – выдает указанное количество символов с
конца строки текста.
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

30. Функции Текстовые ПРАВСИМВ

ПРАВСИМВ (RIGHT) позволяет из указанного
в ячейки текста «забрать» в ячейку с
функцией указанное количество символов
справа в указанном тексте
Текст (обязательный аргумент): аргумент, в
котором указывается текст или ссылка на
ячейку с текстом
Число знаков (необязательный аргумент):
заполняется числовым значением того
количества знаков, которое необходимо
«забрать» в ячейку в функцией. В случае, если
аргумент не заполнять, то по умолчанию
функция «заберет» только последний символ
справа из указанного текста
Данная
функция
часто
является
вспомогательной и работает с другими
функциями в качестве вложенной
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

31. Пример использования функции ЛЕВСИМВ с другими функциями (СЦЕПИТЬ(ЛЕВСИМВ))

Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

32. Текстовые функции

СЖПРОБЕЛЫ
СЖПРОБЕЛЫ(Текст)
Удаляет из текста лишние пробелы, кроме одиночных между словами.
Пример: Исправить написанный с лишними пробелами текст в ячейке
А1.
=СЖПРОБЕЛЫ (A1)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

33. Функции Текстовые

Объединить данные из нескольких ячеек в
одну можно двумя способами:
1) Используя & (объединение текстовых
строк)
2) СЦЕПИТЬ (CONCATENATE). Данной
функцией возможно соединить в одну
ячейку до 255 включительно текстовых
значений из разных ячеек
Московский Государственный Технический
Университет им. Н.Э. Баумана [email protected]

34. Текстовые функции

СЦЕПИТЬ
СЦЕПИТЬ(Текст1;Текст2;)
Объединение несколько текстовых строк из разных ячеек в одну.
Пример: Объединить фамилию, имя и отчество.
=СЦЕПИТЬ(B2;" ";C2;" ";D2)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

35. Функции Текстовые

ПРОПИСН(UPPER) позволяет все
буквы текста сделать
ЗАГЛАВНЫМИ буквами
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

36. Функции Текстовые

СТРОЧН (LOWER) позволяет все
буквы текста сделать
СТРОЧНЫМИ буквами
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

37. Функции Текстовые

ПРОПНАЧ (PROPER) позволяет
все буквы текста сделать
СТРОЧНЫМИ буквами
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

38. Текстовые функции

НАЙТИ(Искомый-текст;Просматриваемый_текст;Нач_позиция)
Определяет позицию символа (или символов) в указанной текстовой строке с учетом регистра.
Пример: Определить какой по счету будет символ ф в слове Профессор
=НАЙТИ("ф";A1;1)
Ответ в ячейке – цифра 4
ПОИСК(Искомый-текст;Просматриваемый_текст;Нач_позиция)
Определяет позицию символа (или символов) в указанной текстовой строке без учета регистра.
=ПОИСК(«Ф";A1;1)
Ответ в ячейке – цифра 4
ДЛСТР(Текст) – определяет количество символов в текстовой строке.
Пример: Определить количество символов в слове Профессор
=ДЛСТР(А1)
Ответ в ячейке – цифра 9
ЗНАЧЕН (Текст) – преобразует текстовый аргумент в число
Преобразуем текст в число.
Пример: в ячейке В2 текст 5, в ячейке D2 – цифра 5
=ЗНАЧЕН (B2)=D2
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

39. Пример использования функций: Правсимв, длстр, поиск

Необходимо из текста 454Договор№12-49
в ячейку «забрать» только символы после
знака «№»
В указанном тексте подсчитать общее
количество символов (ДЛСТР) и вычесть
из них количество знаков после «№»
(ПОИСК)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

40. Пример использования функций: ПСТР, ПОИСК

Из всего текста 454Договор№12-49
Начинать «забирать» текст с символ
буквы «д» (нет чувствительности к
регистру)
До знака «№», при этом важно узнать,
какой по счёту символ знака «№» в тексте
и из этого количественного показателя
вычесть количественный показатель
символа «Д»Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

41. Разбиение текста на столбцы

Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

42. Функции даты и времени

.
СЕГОДНЯ
СЕГОДНЯ() – вставка текущей даты в формате даты.
= СЕГОДНЯ() дает текущую дату.
ТДАТА
ТДАТА() – вставка текущей даты в формате даты и времени.
= ТДАТА() - текущая дата и время
.
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

43. Функции даты и времени

РАБДЕНЬ
РАБДЕНЬ(Нач_дата;Число_дней;Праздники) – определение даты, отстоящей
на заданное число рабочих дней вперед или назад от начальной даты.
Пример: Определить дату выдачи документов , при условии, что для дата
выдачи возможна через 2 рабочих дня после даты получения документов.
=РАБДЕНЬ(C2;2;$E$2)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

44. Функции даты и времени

ЧИСТРАБДНИ
ЧИСТРАБДНИ(Нач_дата;Кон_дата;Праздники)
Возвращает количество полных рабочих дней между двумя датами.
Пример: Определить количество рабочих дней между датой получения и датой выдачи документов с учетом
праздника.
=ЧИСТРАБДНИ(C2;D2;$F$2)
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

45. Функции Даты и Время ДОЛЯГОДА

Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

46. Функции Даты и Время ДОЛЯГОДА

Долягода (yearfrac) возвращает в ячейку целое количество прожитых частей (лет) и
незавершенную долю года (дробную часть)
Нач_дата (обязательный аргумент): ссылка на ячейку с датой с полном числовом формате
(ДД.ММ.ГГГГ) !!! Обратить внимание!!! В ячейке отображается дата, а в аргументе функции дата
преобразуется в число ей равное
Кон_дата (обязательный аргумент): ссылка на ячейку с датой с полном числовом формате
(ДД.ММ.ГГГГ) !!! Обратить внимание!!! В ячейке отображается дата, а в аргументе функции дата
преобразуется в число ей равное
Базис (необязательный аргумент): используемый способ вычисления дня.
Базис
Способ вычисления дня
0 или опущен
Американский (NASD) 30/360
1
Фактический/фактический
2
Фактический/360
3
Фактический/365
4
Европейский 30/360
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

47. Функции Даты и Время ДОЛЯГОДА

Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

48. Функции Даты и Время ДЕНЬНЕД ()

Деньнед () возвращает в ячейку числовое
значение дня недели
Дата_в_числовом_формате (обязательный
аргумент): ссылка на ячейку с датой с полном
числовом формате (ДД.ММ.ГГГГ) !!! Обратить
внимание!!! В ячейке отображается дата, а в
аргументе функции дата преобразуется в число ей
равное
Тип (необязательный аргумент): используемый
способ вычисления дня.
Тип
Возвращаемое число
1 или опущен
Чило от 1 (вс) до 7 (сб).
2
Число от 1 (пн.) до 7 (вс)
3
Число от 0 (пн.) до 6 (вс.)
11
Число от 1 (пн.) до 7 (вс.)
12
Число от 1 (вт.) до 7 (пн.)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

49. Функции Даты и Время ТЕКСТ

Текст (text) возвращает в ячейку
ТЕКСТОВОЕ значение, например, дня,
месяца, года, времени и т.д. Все зависит
от прописанного Формата
Значение (обязательный аргумент): ссылка
на ячейку с датой с полном числовом
формате (ДД.ММ.ГГГГ) !!! Обратить
внимание!!! В ячейке отображается дата, а в
аргументе функции дата преобразуется в
число ей равное
Формат (обязательный аргумент):
используемый для применения формата к
указанному значению
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

50. Функции Даты и Время РАЗНДАТ

Разндат (DATEDIF) Функция отсутствует в Excel. Однако если прописывать ее по средствам клавиатуры, то
функция работает
Начальная дата (обязательный аргумент): ссылка на ячейку с датой с полном числовом формате
(ДД.ММ.ГГГГ) !!! Обратить внимание!!! В ячейке отображается дата, а в аргументе функции дата
преобразуется в число ей равное
Конечная дата (обязательный аргумент): ссылка на ячейку с датой с полном числовом формате (ДД.ММ.ГГГГ)
!!! Обратить внимание!!! В ячейке отображается дата, а в аргументе функции дата преобразуется в число ей
равное
Далее обязательный аргумент, в котором указывается формат обязательно в английской раскладке
клавиатуры:
D – день (количество дней)
M – месяц (количество месяцев)
Y – год (количество лет)
YD – количество дней без учета лет
YM – количество месяцев без учета лет
MD – количество дней без учета месяцев
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

51. Функции Ссылки и Массивы ВПР

ВПР - вертикальный просмотр, т.е. просмотр
для вертикальных таблиц, у которых «шапка»
таблице в строке выше
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

52. ВПР : разбор аргументов функции

Функции Ссылки и Массивы
ВПР : разбор аргументов функции
Искомое значение – уникальность, расположенность в двух таблицах (основной и дополнительной).
Основная таблица – в которой прописывается формула. Дополнительная та, из которой значение
подтягивается в основную
Таблица (table array) – строго!!! Только со столбца, в котором располагается значение искомое!!! Далее
можно выделить хоть все столбцы до конца таблицы. При этом необходимо осознавать, что столбец
искомого значения в дополнительной таблице должен располагаться слева!!! Относительно столбца
подтягиваемого значения!!!
Номер столбца – числовое значение порядкового номера столбца, из которого значения подтягивается в
основную таблицу. Отсчёт выполняется с первого выделенного столбца!!!
Интервальный просмотр – тип поиска.
Поиск может быть осуществлен по двум направлениям:
1) Строгое соответствие (только так и никак иначе, ищем только это и ничего более). При таком типе
поиска данный аргумент необходимо заполнять значением 0!!!
2) Приблизительное – В случае, если в диапазоне отсутствует значение точное относительно искомому, то
будет найдено значение приблизительное относительно ему же (искомому), но всегда поиск будет
осуществлен только в меньшую сторону. Например, если у нас имеются два значение:
Например:
3
Ищем 90
100
Найдено будет 3 (т.к. относительно 90
значение 3 является меньшем
Московский Государственный Технический Университет им
Н.Э. Баумана [email protected]

53. ГПР

Функции Ссылки и Массивы
ГПР
ГПР - горизонтальный просмотр, т.е.
просмотр для горизонтальных таблиц, у
которых «шапка» таблице в столбце слева
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

54. ГПР: разбор аргументов функции

Функции Ссылки и Массивы
ГПР: разбор аргументов функции
Искомое значение – уникальность, расположенность в двух таблицах (основной и дополнительной). Основная
таблица – в которой прописывается формула. Дополнительная та, из которой значение подтягивается в
основную
Таблица (table array) – строго!!! Только со строки, в котором располагается значение искомое!!! Далее можно
выделить хоть все строки до конца таблицы. При этом необходимо осознавать, что строка искомого значения в
дополнительной таблице должна располагаться выше!!! Относительно строки подтягиваемого значения!!!
Номер строки – числовое значение порядкового номера строки, из которого значения подтягивается в
основную таблицу. Отсчёт выполняется с первой выделенной строки!!!
Интервальный просмотр – тип поиска.
Поиск может быть осуществлен по двум направлениям:
1) Строгое соответствие (только так и никак иначе, ищем только это и ничего более). При таком типе поиска
данный аргумент необходимо заполнять значением 0!!!
2) Приблизительное – В случае, если в диапазоне отсутствует значение точное относительно искомому, то
будет найдено значение приблизительное относительно ему же (искомому), но всегда поиск будет
осуществлен только в меньшую сторону. (аналогично ВПР. См. предыдущий слайд)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

55. ПОИСКПОЗ

Функции Ссылки и Массивы
ПОИСКПОЗ
Назначение
Искомое значение – уникальность, расположенность в двух таблицах (основной и дополнительной).
Основная таблица – в которой прописывается формула. Дополнительная та, из которой значение
подтягивается в основную
Просматриваемый массив () – строго!!! Только тот столбец или строка, в котором/ой находится искомое
значение
Тип сопоставления – тип поиска.
Поиск может быть осуществлен по двум направлениям:
1) Строгое соответствие: (только так и никак иначе, ищем только это и ничего более). При таком типе
поиска данный аргумент необходимо заполнять значением 0!!!
2) Приблизительное: 1 В случае, если в диапазоне отсутствует значение точное относительно искомому, то
будет найдено значение приблизительное относительно ему же (искомому), но всегда поиск будет
осуществлен только в меньшую сторону. Важна предварительная сортировка от А-Я
3) Приблизительное: - 1 В случае, если в диапазоне отсутствует значение точное относительно искомому,
то будет найдено значение приблизительное относительно ему же (искомому), но всегда поиск будет
осуществлен только в большую сторону. Важна предварительная сортировка от Я-А
Указывает порядковый номер значения в таблице: номер столбца, в котором
находится значение или номер строки в котором находится значение
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

56. Индекс

Функции Ссылки и Массивы
Индекс
Назначение
Массив:
1) Можно выделить как один столбец так и множество. От этого будет зависит заполняемость последнего
(третьего) аргумента данной функции (номер столбца)
Номер строки: числовой показатель номер строки, который в совокупности с указанным массивом
определит координату ячейки для возвращаемого значения. Например:
Массив: A:A
Номер строки: 4
Возвращаться в ячейку будет значение координаты A4
Возвращает в ячейку значение указанной координаты
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

57. Ошибки в формулах, причины их возникновения

Имя ошибки
#ДЕЛ/0!
Причина возникновения
Действия по исправлению
Деление на 0 недопустимо
Проверить порядок деления и значения ячеек
#ЗНАЧ
Используется недопустимый тип аргумента
Проверить значения ячеек, участвующих в
или операнда. В ячейку ссылки часто введен
вычислительном процессе
текст
#ИМЯ
Excel не может распознать имя в формуле:
адрес ячейки, имя функции,
несуществующее имя диапазона
Проверить адрес ячейки (буква в адресе
должны быть в EN), имя функции или задано ли
имя у ячейки/диапазона и правильно ли оно
прописано, заключен ли текст в «»
#ССЫЛКА!
Ячейки, участвующие в формуле были
удалены. Ссылки на ячейку указаны
неверно
Проверить ссылки на ячейки
Циклическая
ссылка
Называется последовательность ссылок, при
Проверить формулу
которой формула ссылается на саму себя
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

58. Сортировка данных по одному критерию

Выделить любую ячейку столбца, по которому необходимо выполнить сортировку данных для всей
таблице (!!!Обратить внимание!!! Не выделять весь столбец) - Вкладка Данные (Data)– Группа
Сортировка и фильтр (Sort & Filter) – Выбрать команды для сортировки по одному критерию
Сортировка данных позволяет упорядочивать данные таблицы:
Текстовые данные
в алфавитном порядке (от А до Я)
в порядке обратном алфавитному (от Я до А)
Числовые данные
от меньшего к большему
от большего к меньшему
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

59. Независимая сортировка одного столбца в таблице

1. Щелкните по ячейке нужного столбца (например, столбец Продавец)
2. На вкладке Главная в группе Редактирование выберите пункт Сортировка и фильтр, а затем
выберите одну из команд для сортировки. ( От А до Я)
Результат
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

60. Многоуровневая сортировка данных

Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

61. Сортировка данных по нескольким столбцам

1. Выделить таблицу.
2. На вкладке Данные в группе Сортировка и фильтр нажать кнопку Сортировка
3. С помощью кнопки Добавить уровень в окне Сортировка отсортировать
столбец как на рисунке ниже.
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

62. Сортировка данных по нескольким столбцам

В группе Сортировка можно выбрать тип сортировки: Значения, Цвет ячейки,
Цвет шрифта, Значок ячейки.
В группе Порядок необходимо выбрать последовательность по которой
осуществляется сортировка.
• От А до Я или От Я до А (для символьных данных); По возрастанию или По
убыванию (для числовых данных); От новых к старым или От старых к новым
(для значений даты).
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

63. Многоуровневая сортировка данных

Выделить любую ячейку в таблице - Вкладка Данные (Data) – Группа Сортировка и фильтр (Sort &
Filter) – Сортировка (Sort)
Выбрать первый уровень для сортировке, открыв список «шапки» документа
Выбрать из списка Сортировка, по какому критерию создать сортировку
По значение (values)
По цвету ячейки (cell color)
По цвету шрифта (font color)
По значку в ячейки (cell icon)
Выбрать Порядок сортировки (от А до Я или от Я до А)
Добавить уровень соответствующей кнопкой-командой (Добавить уровень (Add level))
Выполнить описанные выше действия к новому уровню
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

64. Настойка параметров сортировки для выбора столбцов/строк

Сортировать данные можно как в вертикальной таблице, так и в горизонтальной, а также с учетом
регистра. Для этого следует воспользоваться кнопкой-командой Параметры (Option) и выбрать
нужное
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

65. Удаление уровней сортировки

Для удаления уровня сортировки необходимо в диалоговом окне Сортировка выделить уровень
сортировки для удаления и воспользоваться кнопкой-командой Удалить уровень (Delete level)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

66. Фильтрация данных

Автофильтр
1. Выбрать любую ячейку таблицы.
2. На вкладке Данные, в группе Сортировка и фильтр, выбрать
кнопку Фильтр
3. Выбрать условие фильтрации.
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

67. Фильтрация данных

Расширенный фильтр
Расширенный фильтр позволяет:
1. Построить более сложные условия отбора.
2. Разместить отфильтрованные данные в этом же диапазоне.
3. Из списка повторяющихся значений выбрать только уникальные.
1. Скопировать заголовок (в ячейку А11)
2. Выбрать ячейку таблицы
3. Нажать на вкладке Данные в группе Сортировка и фильтр кнопку Дополнительно.
4. В появившемся окне Расширенного фильтра установить данный Диапазон условий
и скопировать результат в диапазон ячеек c А16 до G18
Московский Государственный Технический
Университет им. Н.Э. Баумана [email protected]

68. Фильтрация данных

Условия отбора расширенного фильтра
В условия отбора расширенного фильтра может входить
1. Несколько условий, накладываемых на один столбец,
2. Несколько условий, накладываемых на несколько столбцов,
3. Условия, создаваемые как результат выполнения формулы.
Вариант 1 В диапазоне условий 2 условия для столбца B. (Товар 1 и Товар 3)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

69. Фильтрация данных. Установка фильтра

Фильтрация – отбор значений (строк) тех данных в таблице, которые удовлетворяют выбранным
условиям пользователя в столбцах
Выделить любую ячейку в таблице – Вкладка Данные (Data) – Группа Сортировка и фильтр
Московский Государственный Технический Университет им.
(Sort&Filter)- Фильтр
Н.Э. Баумана [email protected]

70. Фильтрация данных. Отбор записей текстовых значений

Открыть фильтровую область
текстового столбца
Снять V Выделить все
Установить V напротив тех
значений, отбор которых требуется
выполнить
Если список значений большой,
можно воспользоваться строкой
Поиск (в 2007 версии отсутствует)
Дополнительные параметры
отбора устанавливаются в
Текстовых фильтрах (Text filter)
Если значения в таблице имеют
цветовые выделения
(ячейка/шрифт), то имеется
возможность установить Фильтр
по цвету (Filter by color)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

71. Фильтрация данных. Отбор записей числовых значений

Открыть фильтровую область
числового столбца
Снять V Выделить все
Установить V напротив тех
значений, отбор которых
требуется выполнить
Дополнительные параметры
отбора устанавливаются в
Числовых фильтрах (Number
filters)
Если значения в таблице
имеют цветовые выделения
(ячейка/шрифт), то имеется
возможность установить
Фильтр по цвету (Filter by
color)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

72. Фильтрация данных. Отбор записей значений дат

Открыть фильтровую область
столбца с датой
Снять V Выделить все
Установить V напротив тех
значений, отбор которых
требуется выполнить
Дополнительные параметры
отбора устанавливаются в
Фильтрах по дате (Data filters)
Если значения в таблице
имеют цветовые выделения
(ячейка/шрифт), то имеется
возможность установить
Фильтр по цвету (Filter by
color)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

73. Очистка условий фильтрации по одному условию

Открыть фильтровую область
столбца
Удалить фильтр из столбца…
(Clear filter from…)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

74. Очистка всех условий фильтрации

При необходимости очистить все установленные условия отбора значений (фильтрации), следует
воспользоваться командой Очистить
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

75. Расширенный фильтр

Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]
В появившемся окне Расширенный фильтр:
1) Установить маркер так, чтобы результат
нахождения уникальных значений
применялся в диапазоне отличном от
исходного диапазона (скопировать результат
в другое место)
2) Проверить, чтобы в строке Исходный
диапазон был указан тот диапазон, из
которого необходимо получить список
уникальных значений
3) Строка Поместить результат в диапазон
предлагает пользователю выбрать место
(ячейку), с которой необходимо начать
вставку списка уникальных значений
4) Если важно получить только уникальные
записи, необходимо установить в чек-бокс V
Только уникальные записи
5) ДИАПАЗОН УСЛОВИЙ: предварительно
должна быть создана таблица условий!!!
Советы по созданию таблицы условий:
Как можно меньше вводите с клавиатуры
значений, лучше значения копировать из
основной таблицы, т.к. должно быть полное
совпадение по символам.

76. Условное форматирование

Вкладка Главная – группа Стили – Условное форматирование:
Правила выделения ячеек (HIGHLIGHT CELLS RULES): возможность
выбрать следующие критерии:
Больше, Меньше, Между, Равно, Текст содержит, Даты,
Повторяющиеся значения
Правила отбора первых и последних значений (TOP/BOTTOM
RULES): возможность выбрать следующие критерии:
Форматирование крайних числовых значений (первые 10,
последние 10, где 10 представляет собой условное значение, а
также правила Выше среднего и Ниже среднего)
Гистограммы (DATA BARS):
Градиентная или сплошная заливка ячеек, длина которых
зависит от чисел, в них расположенных
Цветовые шкалы (COLOR SCALES):
Заливка в два или в три цвета, которая зависит от чисел,
расположенных в ячейках
Наборы значков (ICON SETS):
Установка значка, отражающего тенденцию изменения чисел в
ячейках%
Московский Государственный Технический Университет им. Н.Э. Баумана
[email protected]

77. Условное форматирование. Создание собственного правила

Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

78. Редактирование условного форматирования

Условное форматирование
Редактирование условного форматирования
Вкладка Главная (HOME)– группа Стили (STYLE) – Условное форматирование (CONDITIONAL
FORMATTING)–Управление правилами (MANAGE RULE)– при необходимости выбрать место
расположения условного форматирования (Этот лист) –выделить нужный объект условного
форматирования – Изменить/Удалить (EDIT/DELETE)
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

79. Защита ячеек

Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]
Алгоритм установки защиты
ячейки/скрытия формул
1. Выделить весь лист
2. CTRL+1
3. Защита
4. Снять V защищаемая ячейка
5. ОК
6. Выделить нужный диапазон
7. Ctrl+1
8. Защита
9. Поставить V Защищаемая
ячейка/Скрыть формулы (в
зависимости от необходимости
может быть установлена одна V
или две)
10. Вкладка Рецензирование
11. Группа Изменения
12. Защитить лист
13. При необходимости установить

80. Защита диапазонов

Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]
Алгоритм установки защиты диапазонов
1. Выделить диапазон
2. Вкладка Рецензирование
3. Группа Изменения
4. Разрешить изменения диапазонов
5. Создать
6. Задать Имя диапазона
7. Проверить ячейки для защиты
8. Ввести пароль
9. ОК
10. Подтвердить пароль
11. Повторить действия для другого
диапазона
12. По окончанию Защитить ЛИСТ

81. Защита структуры книги

Алгоритм установки защиты структуры
1. Вкладка Рецензирование
2. Группа Изменения
3. Защитить книгу
4. При необходимости ввести пароль (в случае ввода пароля
требуется его подтверждение)
5. ОК
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

82. Защита файла

Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

83. Проверка данных

Алгоритм установки Проверки данных
1. Выделить диапазон
2. Вкладка Данные
3. Группа Работа с данными
4. Проверка данных
5. Параметры (вкладка в окне Проверка вводимых значений)
6. Тип данных
• Целое число: любое целое число (не содержащее дробную часть)
• Действительное: число с дробной частью
• Список: любое перечисление: текстовое или числовое
• Дата
• Время
• Длина текста: количество разрешенных для ввода знаков в ячейку
• Другой: формула
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

84. Проверка данных

Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

85. Проверка данных

Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

86. Проверка данных

Обводка неверных данных не может быть выведена на
печать при печати документа
Обводка неверных данных «исчезает» самостоятельна
после прохождения времени Автосохранения (по
умолчанию 10 мин., но можно задать любое другое в
Параметрах Excel)
Обводка неверных данных «исчезает» после
самостоятельного сохранения документа
Обводка неверных данных может быть отключена
принудительна при помощи команды Удалить обводку
неверных данных
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

87. Проверка данных

В случае необходимости Удалить выполненную
Проверку данных, требуется:
Выделить диапазон – Данные (DATA)– Работа с
данными (Data tools) – Проверка данных (Data
validation)– Очистить все (Clear all)- ОК
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

88. Автоформат таблиц

Алгоритм действий
Вкладка Главная (HOME)– группа Стили (STYLE) –
Форматировать как таблицу (FORMAT AS TABLE) –
Выбрать нужное цветовое оформление – В
появившемся окне проверить диапазон
расположения данных – Таблица с заголовками – ОК
CTRL+T (Table)
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

89. Автоформат таблиц

Важно! Если таблица форматированная, при положении курсора внутри
таблицы на ленте появляется контекстуальная вкладка Конструктор (DESING)
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

90. Автоформат таблицы

Присвоить таблице
пользовательское имя
Отказ от
форматированной
таблицы
Вставить срез для
визуализации отборных
значений
Строка итогов для
подведения итоговых
значений
Строка итогов для
подведения итоговых
значений
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

91. Удаление дубликатов

Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

92. Удаление дубликатов

В появившемся окне Расширенный фильтр:
1) Установить маркер так, чтобы результат
нахождения уникальных значений
применялся в диапазоне отличном от
исходного диапазона (скопировать
результат в другое место)
2) Проверить, чтобы в строке Исходный
диапазон был указан тот диапазон, из
которого необходимо получить список
уникальных значений
3) Строка Поместить результат в диапазон
предлагает пользователю выбрать
место (ячейку), с которой необходимо
начать вставку списка уникальных
значений
4) Так как нам важно получить только
уникальные записи, необходимо
установить в чек-бокс V Только
уникальные записи
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

93. Промежуточные итоги

Для подведения Промежуточных итогов необходимо
предварительно задать сортировку значений!
1) Находясь в любой ячейке таблицы Данные – Структура –
Промежуточный итог
2) В появившемся окне При каждом изменении в – Прописать
Название столбца по которому требуется провести
Промежуточный итог
3) Выбрать Операционное действие
4) Добавить итоги по – выбрать столбцы таблицы, по которым
необходимо получить промежуточный итог
5) V Заменить текущие итоги - «убирает» все выполненные
предыдущие промежуточные итоги
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

94. Консолидация

Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

95. Консолидация данных

Консолидация – объединение значений из нескольких диапазонов в один новый диапазон с
выполнением определенной функции. Функция может быть (сумма, кол-во, среднее,
произведение и т.д.)
Существует различные способы консолидации данных.
Консолидация по расположению
Консолидация данных по расположению (по позициям) подразумевает, что исходные
таблицы на разных листах абсолютно идентичны. Одинаковые не только названия столбцов,
но и наименования строк. Это наиболее правильный способ объединения данных, т.к.
исходные диапазоны идеальны для консолидации.
Консолидация по категории
Консолидация данных по категориям применяется, когда исходные диапазоны имеют
неодинаковую структуру. Например, в магазинах реализуются разные товары. Какие-то
наименования повторяются, а какие-то нет.
Консолидация данных, с помощью формул.
Используется для сводного отчета.
Если необходимо сложить данные в ячейках, находящихся на различных листах
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

96. Консолидация данных

Консолидация данных по расположению
1. Выделить пустую ячейку листа, начиная с которой будет размещен
консолидируемый диапазон.
2. На вкладке Данные в группе Работа с данными выбрать кнопку
Консолидация.
3. В поле Функция выбрать функцию, которая будет применена к объединяемым
данным.
4. Поставить курсор в поле Ссылка и выделить первый диапазон консолидации
Вместе с заголовками, нажать Добавить и т.д. Повторить это для всех исходных
диапазонов.
5. В группе Использовать в качестве имен поставить флажки подписи верхней
строки (если исходные диапазоны были выделены с заголовками) и значения
левого столбца.
6. OK.
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

97. Консолидация данных

Консолидация данных по расположению
Чтобы автоматически сделать заголовки для столбцов консолидированной таблицы, ставим галочку напротив
«подписи верхней строки». Чтобы команда суммировала все значения по каждой уникальной записи крайнего
левого столбца – напротив «значения левого столбца». Для автоматического обновления объединенного отчета
при внесении новых данных в исходные таблицы – напротив «создавать связи с исходными данными».
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

98. Сводные таблицы

Сводная таблица - инструмент для анализа больших табличных данных .
Пример: Проанализировать таблицу с данными по продажам марок автомобиля в разрезе
различных фирм.
Создание сводной таблицы
1. Выделить ячейку в таблице с данными.
2. На вкладке Вставка в группе Таблицы нажать на кнопку Сводная таблица
3. Появляется окно Создание сводной таблицы. Проверить правильность выделения диапазона
данных и определить место размещения сводной таблицы. Например, указать ячейку
(например, В13 на существующем листе). Нажать кнопку ОК.
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

99. Сводные таблицы

В списке полей сводной таблицы выделить Флажком поля
для добавления в отчет. (Марка автомобиль, кол-во,
сумма, продавец, дата)
И перенести значение Марка автомобиля из Названия
строк в Название столбцов.
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

100. Сводные таблицы

Есть 4 области:
1. Фильтр отчета - сводной таблицы. Если установлен фильтр, то построение и
расчет данных сводной таблицы ведется для заданного значения.
2. Названия строк– формируют заголовки строк сводной таблицы, если
размещено несколько полей, то они размещаются в макете сверху вниз,
обеспечивая группирование данных сводной таблицы по иерархии.
3. Названия столбцов – формируют заголовки столбцов сводной таблицы, если
таких полей несколько, то они в макете размещаются слева направо,
обеспечивая группирование данных сводной таблицы по иерархии.
4. Значения – обязательная область макета для размещения полей, по которым
подводятся итоги, согласно выбранной функции.
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

101. Сводные таблицы

Группировки сводной таблицы
Создание групп дает возможность скрывать или отображать отдельные группы
1. Чтобы добавить дополнительную группировку, необходимо перетащить
соответствующие поля из списка полей сводной таблицы в область строк
или столбцов сводной таблицы.
Например, можно добавить поле Цвет в название Строк.
При работе с группами на закладке Параметры есть группа Активное поле. (С
помощью кнопок
группировок).
+и -
можно развернуть или свернуть все Уровни
Если Уровни группировок не видны, то нажать кнопку
.
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

102. Сводные таблицы

Создание сводной таблицы.
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

103. Сводные таблицы

Вычисления в сводной таблице
Excel автоматически определяет функцию, на основе которой будут
производиться итоговые вычисления (операция суммирования). Можно
сменить тип вычислений, предлагаемый Excel. Для этого:
1. Щелчок правой кнопкой мыши по ячейке сводной таблицы и выберите
команду Параметры полей значений.
2. И выберите в пользовательском поле функцию для расчета итоговых
значений. (Сумма по полю количество)
Например Количество.
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

104. Сводные таблицы

Дополнительные вычисления в сводной таблице
При необходимости можно создавать настраиваемые вычисления для отображения значений
относительно других строк и столбцов в сводной таблице.
1. Выделить любую ячейку внутри сводной таблицы
2. На вкладке Параметры в группе Вычисления нажать кнопку Вычисления.
3. Нажмите кнопку Дополнительные вычисления и выберите необходимый
вариант вычислений. (Например, процент от общей суммы).
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

105. Создание сводной таблицы

Создание вычисляемых полей сводной таблицы
1. Выделите любую ячейку внутри сводной таблицы.
2. На вкладке Параметры нажмите кнопку Вычисления.
3. Нажмите кнопку Поля, элементы и наборы и выберите команду Вычисляемое поле.
4. В окне Вставка вычисляемого поля в поле Имя ввести имя Общий итог с ндс
В поле Формула
5. ОК
=Сумма*1,18
Московский Государственный Технический Университет им. Н.Э. Баумана [email protected]

106. Сводные таблицы

Форматирование сводной таблицы
Вкладка Конструктор содержит различные стили оформления (светлый, средний, темный).
оформления стилем Темный 1
Пример
При установке флажков в Параметрах стилей сводной таблицы Чередующиеся строки и
Чередующиеся столбцы сводная таблица будет такой:
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

107. Сводные таблицы

Фильтрация сводной таблицы. Использование срезов
Срезы —кнопки, позволяющие быстро выполнять фильтрацию данных в отчетах сводных
таблиц.
1. Щелкните в любом месте отчета сводной таблицы.
2. В разделе Работа со сводными таблицами на вкладке Параметры в группе Сортировка и
фильтр нажмите кнопку Вставить срез.
3. В диалоговом окне Вставка срезов установите флажки напротив полей сводной таблицы,
для которых нужно создать срез.
4. Нажмите кнопку ОК. Для каждого из выбранных полей будет отображен срез.
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

108. Сводные таблицы

Отключение или удаление среза
Для отключения среза:
1. Щелкните в любом месте отчета сводной таблицы, от которого нужно
отключить срез. Появится раздел Работа со сводными таблицы с вкладками
Параметры и Конструктор
2. На вкладке Параметры в группе Сортировка и фильтр щелкните стрелку
Вставить срез, а затем выберите пункт Подключения к срезам. И снять
флажки.
Для удаления среза:
1. Щелкните в любом месте отчета сводной таблицы.
2. Выберите срез и нажмите клавишу Delete.
Или Щелкните срез правой кнопкой мыши и выберите пункт Удалить <имя
среза>.
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]

109. Сводные таблицы

Удаление сводной таблицы и диаграммы
Удаление таблицы
Если cводная таблица размещается на отдельном листе — удалите лист.
Если cводная таблица, размещается на листе, на котором есть другие
необходимые вам данные:
1. Щелкните в любом месте отчета сводной таблицы, который требуется
удалить.
2. На вкладке Параметры в группе Действия щелкните стрелку под надписью
Выделить и выбрать Всю cводную таблицу.
3. Нажмите кнопку Delete.
Удаление диаграммы
1. Щелкните по диаграмме.
2. Нажмите кнопку Delete.
Московский Государственный Технический Университет им.
Н.Э. Баумана [email protected]