Prezentatsiya_Lektsiya_9_Excel (1)

1.

КОМПЬЮТЕР 1 (БИЗНЕС) • ЛЕКЦИЯ № 9
АНАЛИЗ И ВИЗУАЛИЗАЦИЯ
ДАННЫХ В EXCEL
Формулы • функции • диаграммы и графики
Для студентов 2 курса: экономика, менеджмент, бизнес, бухгалтерский учёт

2.

Цель лекции и логика работы с данными
ВВЕДЕНИЕ
ЦЕЛИ ЗАНЯТИЯ
Понимать формулы, функции и
диаграммы Excel.
Выбирать визуализацию под
аналитическую задачу.
Представлять данные точно и
ответственно.
ЦЕПОЧКА АНАЛИЗА
1
Ввод данных
↓
2
Расчёт
↓
3
Анализ
↓
4
Визуализация
↓
5
Вывод и решение
Главный принцип: сначала корректный расчёт, затем наглядное представление результата.
2

3.

1. Формулы в Excel: основа вычислений
РАЗДЕЛ 1
Формула начинается со знака «=» и
выполняет вычисления над данными
листа.
Ссылки на ячейки
Функции
Абсолютная ссылка
Умножение количества на
цену
=B2*C2
Сумма значений диапазона
=СУММ(B2:B7)
Фиксированная ставка
=B2*(1+$F$1)
Порядок работы: выделить ячейку → ввести «=» → указать выражение → Enter.
Редактирование: F2 или двойной щелчок. Отмена ввода: Esc.
3

4.

Операторы и порядок вычислений
РАЗДЕЛ 1
Арифметические
+ − * / ^ %
=A1*20%
Сравнения
= > < >= <= <>
=A1>=B1 → ИСТИНА/ЛОЖЬ
Текстовый
&
=A2&" "&B2
Ссылочные
: ; пробел
A1:A10 — диапазон
=2+3*4 → 14
=(2+3)*4 → 20
Скобки позволяют управлять порядком операций.
Приоритет: скобки → % → степень → умножение/деление → сложение/вычитание → & → сравнение.
4

5.

Виды ссылок: относительная, абсолютная, смешанная
РАЗДЕЛ 1
Относительная: A1
Абсолютная: $A$1
Смешанная: $A1 / A$1
При копировании смещается
вместе с формулой.
Пример: =B2*C2
Не меняется при
копировании.
Применение: ставка, курс,
норматив.
Фиксируется столбец или
строка.
Применение: матричные
таблицы.
=D2*$F$1
Ставка находится в F1. При протягивании
формулы вниз ссылка $F$1 остаётся
неизменной.
Клавиша F4 переключает варианты ссылки: A1 → $A$1 → A$1 → $A1.
5

6.

Типичные ошибки формул и их проверка
РАЗДЕЛ 1
#ДЕЛ/0!
#ИМЯ?
Деление на ноль или пустую ячейку
Исправление: Проверить делитель
Неверное имя функции/диапазона
Исправление: Проверить написание
#ЗНАЧ!
#ССЫЛКА!
Несовместимые типы данных
Исправление: Проверить формат ячеек
Удалённая ячейка/строка/столбец
Исправление: Восстановить ссылку
#Н/Д
#####
Значение не найдено
Исправление: Проверить поиск и совпадение
Столбец слишком узкий
Исправление: Расширить столбец
Проверка: Формулы → Зависимости формул → Влияющие/зависимые ячейки, Показать формулы (Ctrl+`), Вычислить формулу.
6

7.

2. Мастер функций: поиск и настройка
РАЗДЕЛ 2
Функция — готовая формула с
заданным именем.
ИМЯ(аргумент1; аргумент2; …)
КАК ОТКРЫТЬ
Кнопка fx у строки формул
Shift+F3
Формулы → Вставить функцию
Автосумма: Alt+=
Алгоритм работы
1 Выбрать
ячейку
Проверить типы
данных и диапазон.
2 Найти
функцию
Проверить типы
данных и диапазон.
3 Заполнить
аргументы
Проверить типы
данных и диапазон.
4 Проверить
результат
Проверить типы
данных и диапазон.
В русской версии Excel аргументы обычно разделяются «;», в английской — «,».
7

8.

Категории функций и задачи бизнеса
РАЗДЕЛ 2
Математические
Статистические
Логические
СУММ, ОКРУГЛ, СУММПРОИЗВ
Итоги и денежные расчёты
СРЗНАЧ, МИН, МАКС, СЧЁТ
Средние и распределения
ЕСЛИ, И, ИЛИ, ЕСЛИОШИБКА
Условия и классификация
Условные итоги
Поиск и ссылки
Текст / дата / финансы
СУММЕСЛИ, СУММЕСЛИМН,
СЧЁТЕСЛИ
Итоги по группам
ВПР, ИНДЕКС, ПОИСКПОЗ,
ПРОСМОТРX
Подстановка данных
СЖПРОБЕЛЫ, ДАТА, ПЛТ, ЧПС, ВСД
Подготовка данных, кредиты,
инвестиции
8

9.

Функции в экономических расчётах
РАЗДЕЛ 2
Итоги и статистика
Условный расчёт
=СУММ(E2:E7)
=СРЗНАЧ(E2:E7)
=МАКС(E2:E7) / =МИН(E2:E7)
=ЕСЛИ(C2>=B2;"План
выполнен";"План не выполнен")
Суммирование по
условию
=СУММЕСЛИ(B2:B20;"Менеджер";E
2:E20)
Поиск цены в справочнике
Ежемесячный кредитный платёж
=ВПР(A2;Прайс!A:B;2;ЛОЖЬ)
=ПЛТ(24%/12;3*12;-120000000)
Пример кредита: 120 млн сум на 3 года под 24% годовых. ПЛТ учитывает месячную ставку и срок в месяцах.
9

10.

3. Зачем нужны диаграммы и как подготовить данные
РАЗДЕЛ 3
Динамика
Структура
Как меняется показатель во времени
Пример: Выручка по месяцам
Из каких частей состоит целое
Пример: Статьи затрат
Сравнение
Взаимосвязь
Чем отличаются объекты
Пример: Продажи по регионам
Связаны ли два показателя
Пример: Реклама и продажи
Перед построением диаграммы
У каждого столбца должен быть заголовок; пустых строк и столбцов быть не должно.
Категории — в первом столбце, ряды данных — в следующих; числа должны быть числовыми.
Не включайте строку «Всего» в диапазон диаграммы.
10

11.

Создание диаграммы и её основные элементы
РАЗДЕЛ 3
01 Выделить
данные
Вместе с заголовками
02 Вставка →
Диаграммы
Выбрать тип и подтип
03 Оформить
04 Разместить
Заголовок, оси, подписи
Alt+F1 — на листе; F11 —
отдельный лист
Основные элементы диаграммы
Заголовок
Оси X и Y
Ряд данных
О чём диаграмма?
Категории и числовая шкала
Набор значений одной серии
Легенда
Подписи / сетка
Линия тренда
Расшифровка рядов
Точное чтение значений
Общая тенденция и прогноз
11

12.

Редактирование, линия тренда и вспомогательная ось
РАЗДЕЛ 3
Редактирование
Линия тренда
Вкладки «Конструктор» и «Формат» позволяют
менять тип, цвета, данные, подписи и положение
диаграммы.
Правый щелчок по ряду → «Добавить линию
тренда». Доступны линейная, экспоненциальная,
полиномиальная и другие модели.
y ≈ 23,11x + 263,1
Комбинированная диаграмма
R² ≈ 0,93: линия хорошо описывает наблюдаемые
данные; прогноз нужно интерпретировать осторожно.
Выручка — столбцы, рентабельность — линия. Если
единицы измерения различаются, используйте
вспомогательную ось.
R² находится в диапазоне от 0 до 1; близкое к 1 значение указывает на более точное описание ряда моделью.
12

13.

4. Основные виды диаграмм
РАЗДЕЛ 4
Гистограмма /
линейчатая
Сравнение категорий
График
Круговая / кольцевая
Пример: Продажи по регионам
Динамика во времени
Пример: Выручка по месяцам
Доли в целом
Пример: Структура затрат
Диаграмма с областями
Точечная (XY)
Комбинированная
Объём и накопление
Пример: Накопленная выручка
Связь двух числовых переменных
Пример: Реклама и продажи
Показатели в разных единицах
Пример: Выручка и рентабельность
Круговая диаграмма: один ряд, положительные значения, обычно не более 5–6 секторов.
13

14.

Специальные и новые виды диаграмм
РАЗДЕЛ 4
Каскадная (водопад)
Парето
Переход от начального значения к итогу через плюсы и
минусы
Пример: Выручка → чистая прибыль
Столбцы по убыванию и накопленный процент
Пример: ABC-анализ; правило 80/20
Ящик с усами
Treemap / Sunburst
Медиана, квартили, выбросы
Пример: Разброс зарплат по отделам
Вложенная структура и доли
Пример: Расходы по статьям и подстатьям
Воронка
Биржевая / карта
Снижение числа объектов по этапам
Пример: Лиды → сделки → оплаты
Котировки или географические значения
Пример: OHLC; продажи по регионам
14

15.

Как выбрать диаграмму и не исказить вывод
РАЗДЕЛ 4
Динамика
График / область
Сравнение
Гистограмма / линейчатая
Структура
Круговая / Treemap / накопление
Взаимосвязь
Точечная (XY)
Разные единицы
Комбинированная + вспомогательная ось
Из чего сложился итог
Каскадная
Типичные ошибки
• Неподходящий тип
• 3D-эффекты
• Обрезанная ось
• Слишком много рядов
• Нет единиц измерения
• Включена строка «Итого»
Правило хорошей диаграммы: один вывод — одна диаграмма; понятный заголовок; минимум цветов;
читаемые подписи и единицы измерения; указан источник.
15

16.

Итоги и контрольные вопросы
ЗАКЛЮЧЕНИЕ
Главные выводы
Запомните соответствие
Формулы обеспечивают точность расчётов.
Ссылки определяют корректность копирования.
Функции ускоряют анализ. Диаграммы помогают
увидеть динамику, структуру, различия и
взаимосвязи.
График — динамика
Круговая — структура
Гистограмма — сравнение
Точечная — взаимосвязь
Вопросы для самопроверки
1. Чем отличаются относительная, абсолютная и смешанная ссылки?
2. Как работает функция ЕСЛИ и для чего нужны СУММЕСЛИ / СЧЁТЕСЛИ?
3. Как добавить линию тренда и что показывает R²?
4. Когда нужна вспомогательная ось?
5. Какие ошибки чаще всего делают диаграмму вводящей в заблуждение?
16