Тема 14. Обработка данных в электронных таблицах (Excel)

🏠 Главная / Теория / Тема 14. Обработка данных в электронных таблицах (Excel)

На ОГЭ это самое "дорогое" задание (3 балла). Нужно обработать данные в таблице с помощью формул и построить диаграмму. Задание делится на 3 части.

---

## 1. Основные формулы для ОГЭ

### Арифметические:
| Формула | Что делает | Пример |
|---------|-----------|--------|
| `=СУММ(диапазон)` | Сумма чисел | `=СУММ(B2:B100)` |
| `=СРЗНАЧ(диапазон)` | Среднее арифметическое | `=СРЗНАЧ(C2:C50)` |
| `=МАКС(диапазон)` | Максимальное значение | `=МАКС(D2:D100)` |
| `=МИН(диапазон)` | Минимальное значение | `=МИН(D2:D100)` |
| `=СЧЁТ(диапазон)` | Количество числовых ячеек | `=СЧЁТ(A2:A100)` |

### Условные:
| Формула | Что делает | Пример |
|---------|-----------|--------|
| `=СЧЁТЕСЛИ(диапазон; условие)` | Количество подходящих | `=СЧЁТЕСЛИ(B2:B100; ">50")` |
| `=СУММЕСЛИ(диапазон; условие; сумм_диапазон)` | Сумма подходящих | `=СУММЕСЛИ(B2:B100; ">50"; C2:C100)` |
| `=ЕСЛИ(условие; значение_да; значение_нет)` | Ветвление | `=ЕСЛИ(B2>50; "Да"; "Нет")` |
| `=СРЗНАЧЕСЛИ(диапазон; условие; [средн_диапазон])` | Среднее подходящих | `=СРЗНАЧЕСЛИ(C2:C100; ">0")` |

---

## 2. Условия в СЧЁТЕСЛИ и СУММЕСЛИ

| Условие | Смысл | Пример |
|---------|-------|--------|
| `">50"` | Больше 50 | `СЧЁТЕСЛИ(B2:B100; ">50")` |
| `"<0"` | Меньше нуля | |
| `">=100"` | Больше или равно 100 | |
| `"<>"` | Не пусто / не ноль | |
| `"Москва"` | Равно тексту | `СЧЁТЕСЛИ(A2:A100; "Москва")` |

> **Важно:** Условие в кавычках всегда! `">50"` — правильно. `>50` — неправильно.

---

## 3. Часть 1: Вычисление значений формулой

**Задача:** Найти сумму, среднее, максимум, количество по условию.

**Алгоритм:**
1. Кликните на ячейку, куда нужно записать ответ.
2. Введите знак `=` и нужную формулу.
3. Выберите диапазон (можно мышкой — выделите столбец).
4. Закройте скобку, нажмите Enter.
5. Результат появится в ячейке.

**Пример:** "Найти количество участников из Москвы"
```excel
=СЧЁТЕСЛИ(B2:B1000; "Москва")
```

**Пример:** "Найти среднее значение только по положительным числам"
```excel
=СРЗНАЧЕСЛИ(C2:C1000; ">0")
```

---

## 4. Часть 2: Логические функции — ЕСЛИ

**Задача:** Заполнить новый столбец значениями в зависимости от условия.

```excel
=ЕСЛИ(B2 > 100; "Высокий"; "Низкий")
```

- Если B2 > 100 → ячейка = "Высокий".
- Иначе → ячейка = "Низкий".

**Вложенные ЕСЛИ:**
```excel
=ЕСЛИ(B2 > 90; "Отлично"; ЕСЛИ(B2 > 70; "Хорошо"; "Плохо"))
```

---

## 5. Часть 3: Построение диаграммы

**Алгоритм:**
1. Выделите данные для диаграммы (обычно 2 столбца: категории и значения).
2. Вкладка **"Вставка"** → **"Диаграмма"** (или кнопка с диаграммой).
3. Выберите тип: обычно **круговая** или **гистограмма**.
4. Нажмите "Готово" или "ОК".
5. Переместите диаграмму так, чтобы левый верхний угол был в указанной ячейке (перетаскиванием).
6. Добавьте **легенду** и **подписи данных** (через правую кнопку → "Добавить подписи данных").

> **Совет:** Всегда добавляйте подписи данных — это часть требований и даёт дополнительный балл.

---

## 6. Лайфхак: использование Фильтра

Для быстрой проверки своего ответа или нахождения значений:

1. Выделите заголовки таблицы.
2. Вкладка **"Данные"** → **"Фильтр"**.
3. Нажмите стрелочку в нужном столбце → выберите условие.
4. В строке состояния Excel снизу покажется: "Среднее: X, Количество: Y, Сумма: Z".

> **Совет:** Фильтр — для проверки. Сам ответ записывайте формулой в ячейку!

---

## 7. Абсолютные и относительные ссылки

При копировании формулы вниз:
- `B2` — относительная: автоматически меняется на `B3`, `B4` и т.д.
- `$B$2` — абсолютная: всегда ссылается на B2.
- `$B2` — абсолютная строка, `B$2` — абсолютный столбец.

**Когда нужна абсолютная ссылка?** Когда в формуле есть фиксированная ячейка (например, курс валюты в C1, а цены в C2:C100).

```excel
=B2 * $C$1
```

---

## 8. Типичные ошибки

- **Условие без кавычек.** `СЧЁТЕСЛИ(B2:B100; >50)` — ошибка. Нужно `">50"`.
- **Неправильный диапазон.** Захватить лишние строки (включая заголовок) — СЧЁТЕСЛИ посчитает неверно.
- **Диаграмма не на месте.** Левый верхний угол должен быть в указанной ячейке.
- **Нет подписей данных.** Их нужно добавлять вручную после создания диаграммы.
- **Формула введена без `=`.** Без знака равенства Excel воспримет текст как строку, а не формулу.

---

## 9. Лайфхаки для ОГЭ

> **Совет:** Для СЧЁТЕСЛИ — диапазон выбирайте целый столбец (например B:B). Это надёжнее, чем B2:B100, если вы не уверены в количестве строк.

> **Совет:** После написания формулы проверьте результат фильтром. Если совпадает — всё верно.

> **Совет:** Если данных много, сначала посмотрите в строке состояния после фильтрации — это даст подсказку.

> **Совет:** Круговая диаграмма для долей (%), столбчатая — для сравнения значений. Читайте задание — тип может быть указан.

Назад к списку