Практика — Лабораторная работа №3: основы работы в MS Excel

Разбор темы простыми словами

Excel — программа для работы с таблицами. Каждая клетка таблицы называется ячейкой, у неё есть адрес из буквы столбца и номера строки: например, ячейка D7 — это столбец D, строка 7. Эта практическая работа показывает четыре группы приёмов: как оформить таблицу, как отфильтровать данные, как посчитать формулами и как подсветить нужные ячейки.

1. Оформление таблиц

• Выравнивание по вертикали (меню «Главная → Выравнивание») — прижать текст к верху, низу или центру ячейки. Внутри одного столбца выравнивание лучше делать одинаковым, иначе таблица выглядит неаккуратно.
• Границы ячеек. Те светло-серые линии сетки, что видны на экране, — это просто разметка, на печати их нет. Чтобы у таблицы были настоящие рамки, границы задают вручную: «Главная → Шрифт → Границы», а через «Другие границы» можно включить линию отдельно слева, справа, сверху, снизу.
• Объединение ячеек — несколько соседних ячеек превращаются в одну; так делают общий заголовок над столбцами таблицы.
• Перенос текста. Если длинный текст не помещается, увеличение высоты строки не помогает. Помогают два способа: поставить галочку «Переносить по словам» (Формат ячеек → Выравнивание) или вручную перенести строку сочетанием Alt + Enter.

💡 Понятнее: запомните разницу — «Переносить по словам» Excel делает сам по ширине ячейки, а Alt + Enter — это ручной перенос ровно там, где вы захотели.

2. Фильтры

Фильтр позволяет временно показать только те строки, которые отвечают условию, а остальные спрятать. Включается так: выделить строку-заголовок таблицы → «Главная → Редактирование → Сортировка и фильтр → Фильтр». На заголовках появятся кнопки-«треугольнички», через которые можно отобрать данные (например, всех студентов одного факультета или всех, кто родился после определённой даты). Столбец с включённым отбором помечается значком воронки; самые гибкие условия задаются через «Настраиваемый фильтр…». Фильтры можно ставить сразу на несколько столбцов.

3. Формулы

Любая формула начинается со знака «=». После него можно писать арифметику (=A2+B2) или вызывать готовую функцию. Список всех функций — по кнопке «Вставить функцию».

Сложить диапазон:

=СУММ(D3:D5)

📐 Разбор формулы:

• = — знак начала формулы; без него Excel считает, что вы просто пишете текст.
• СУММ — функция сложения всех чисел внутри скобок.
• D3:D5 — диапазон ячеек: двоеточие означает «от D3 до D5 включительно», то есть D3, D4 и D5.

Как работает: Excel берёт все значения в указанном диапазоне и складывает их. Можно перечислить несколько диапазонов через точку с запятой: =СУММ(D3:D5;F3:F5). Главная польза — если число в одной из ячеек изменится, сумма пересчитается сама.

Среднее значение:

=СРЗНАЧ(D3:D5)

📐 Разбор формулы:

• = — начало формулы.
• СРЗНАЧ — функция «среднее значение» (сумма чисел, делённая на их количество).
• D3:D5 — диапазон, по которому считаем среднее.

Как работает: складывает значения диапазона и делит на их число. Результат может получиться дробным (доли копеек), поэтому разрядность уменьшают: «Главная → Число → Уменьшить разрядность» либо через «Формат ячеек → Число → Числовой → 2 знака после запятой» — тогда значение округляется до двух знаков по правилам арифметики.

Посчитать количество по условию:

=СЧЁТЕСЛИ(D3:D5;">"&$D$7)

📐 Разбор формулы:

• = — начало формулы.
• СЧЁТЕСЛИ — функция «посчитать, сколько ячеек подходят под условие».
• D3:D5 — диапазон, в котором ведём подсчёт.
• ; — точка с запятой отделяет диапазон от условия.
• ">" — само условие «больше», записанное в кавычках как текст.
• & — знак склейки: приклеивает к знаку «больше» значение из ячейки.
• $D$7 — абсолютный (закреплённый) адрес ячейки D7, в которой лежит среднее значение.

Как работает: формула считает, сколько ячеек диапазона D3:D5 больше, чем число в D7. Запись ">"&$D$7 собирает условие «> (значение из D7)». Закрепление $D$7 важно: если в таблицу добавить строку, ссылка на ячейку со средним не «съедет».

⚠️ Уточнение: в работе сначала показан «неоптимальный» вариант =СЧЁТЕСЛИ(D3:D5;">21666,67") с вписанным вручную числом. Он плох тем, что среднее — вычисляемая величина: как только данные изменятся, жёстко вписанное «21666,67» станет неверным. Поэтому в условии ссылаются на ячейку ($D$7), а не на число.

Относительная и абсолютная адресация (ключевая идея). Когда формулу «растягивают» за уголок (маленький квадратик в правом нижнем углу ячейки), Excel копирует её в соседние ячейки. При этом:

• Относительный адрес (A2, D3) при копировании сдвигается вместе с формулой: формула в C2 =A2+B2, протянутая вниз, в C3 станет =A3+B3.
• Абсолютный адрес ($A$2) закреплён знаками доллара и при копировании не меняется. Быстро поставить доллары можно клавишей F4.

💡 Понятнее: знак $ — это «булавка», которой вы прикалываете ссылку к конкретной ячейке. $A$2 приколото намертво; без булавок адрес свободно «едет» при копировании.

Сложить значения по условию:

=СУММЕСЛИ(C3:C6;"СГФ";D3:D6)

📐 Разбор формулы:

• = — начало формулы.
• СУММЕСЛИ — функция «сложить только те значения, которые отвечают условию».
• C3:C6 — диапазон, в котором Excel проверяет условие (здесь — столбец с факультетами).
• "СГФ" — само условие в кавычках: берём строки, где написано «СГФ».
• D3:D6 — диапазон, из которого берутся числа для сложения (здесь — стоимость обучения).

Как работает: Excel проходит по столбцу факультетов C3:C6, находит строки со значением «СГФ» и складывает из столбца D3:D6 только соответствующие им суммы. То есть «сложи стоимость обучения всех студентов факультета СГФ».

Дата через несколько рабочих дней (для дедлайнов):

=РАБДЕНЬ($B$16;-7;Weekends)

📐 Разбор формулы:

• = — начало формулы.
• РАБДЕНЬ — функция, возвращающая дату, отстоящую от заданной на нужное число рабочих дней (выходные она пропускает).
• $B$16 — закреплённая ячейка с исходной датой (в примере — дата защиты, 6 июня).
• -7 — на сколько рабочих дней сдвинуться: минус означает «за 7 рабочих дней ДО» этой даты; без минуса было бы «через 7 рабочих дней после».
• Weekends — необязательный третий параметр: именованный диапазон с дополнительными выходными (государственными праздниками), которые тоже надо пропустить.

Как работает: функция отсчитывает 7 рабочих дней назад от даты в B16, пропуская субботы, воскресенья и перечисленные в «Weekends» праздники, и выдаёт крайнюю дату. Удобно для дедлайнов вида «не позднее, чем за N рабочих дней до». Имя диапазону задают так: выделить ячейки с датами праздников → правой кнопкой → «Присвоить имя» → назвать (например, Weekends); потом им управляют через «Формулы → Диспетчер имён».

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

Это автоматическая подсветка ячеек, которые отвечают условию: цветом заливки, полужирным шрифтом и т. п. Делают так: выделить ячейки → «Главная → Стили → Условное форматирование → Создать правило → Использовать формулу для определения форматируемых ячеек» → ввести условие и выбрать оформление.

=D3>$D$8

📐 Разбор формулы (правило подсветки):

• = — начало формулы-условия.
• D3 — первая (верхняя) ячейка выделенного диапазона; относительный адрес, чтобы правило применилось к каждой ячейке по очереди.
• > — знак «больше».
• $D$8 — закреплённая ячейка, в которой лежит среднее значение, с которым сравниваем.

Как работает: для каждой ячейки диапазона Excel проверяет, больше ли она значения в D8; если да — ячейка раскрашивается выбранным цветом. D3 относительная (правило «едет» по всем ячейкам), $D$8 абсолютная (сравниваем всегда с одной и той же ячейкой среднего). Похоже задаётся и подсветка по тексту — например, выделить полужирным строки факультета «СГФ», сравнивая ячейку столбца факультетов. Управлять готовыми правилами можно через «Условное форматирование → Управление правилами».

Лабораторная работа №3. Основы работы в MS Excel

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

Оформление таблиц

• Выравнивание по вертикали (Главная → Выравнивание): текст можно прижать к верхнему краю, к середине или к нижнему. Рекомендация — держать выравнивание единообразным хотя бы внутри одного столбца.
• Границы ячеек (Главная → Шрифт → Границы): серая сетка по умолчанию на печать не выводится, поэтому рамки для печатного документа задают вручную. Через «Другие границы» (Формат ячеек → Граница) можно включать отдельные линии — слева, справа, сверху, снизу; «внешние границы» обрамляют весь выделенный блок.
• Объединение ячеек: выделить ячейки → правой кнопкой → Формат ячеек → вкладка «Выравнивание» → галочка «Объединение ячеек». Быстрее — кнопка «Объединить и поместить в центре» на панели «Выравнивание» (есть и вариант объединить без выравнивания). Снять объединение — теми же способами. Используется для заголовков над столбцами.
• Перенос текста в ячейке: увеличение высоты строки проблему не решает. Два рабочих способа: галочка «Переносить по словам» (Формат ячеек → Выравнивание) или ручной перенос Alt + Enter при наборе.
• Направление текста: кнопка поворота текста в меню «Главная → Выравнивание».

Использование фильтров

На примере таблицы с информацией о студентах. Выделяем строку-заголовок → «Главная → Редактирование → Сортировка и фильтр → Фильтр». На заголовках появляются кнопки с треугольниками; набор условий зависит от типа данных в столбце, самые полные настройки — через «Настраиваемый фильтр…». Примеры запросов из работы:

• вывести всех студентов, родившихся после 01.01.1980;
• вывести всех студентов факультета ФЭМБИ;
• вывести студентов, которые платят за обучение 20000 и больше.

Столбец с активным фильтром помечен воронкой; снять отбор — через «Удалить фильтр». Фильтры можно комбинировать на нескольких столбцах одновременно.

Использование формул

Формула всегда начинается с «=». Полный список функций — кнопка «Вставить функцию»; функции разбиты по категориям (математические, финансовые, по дате, текстовые, статистические и др.). Разобранные функции:

СУММ — сумма диапазона. Пример: посчитать общую сумму оплаты за обучение в ячейке D6:

=СУММ(D3:D5)

📐 Разбор формулы:

• = — начало формулы;
• СУММ — сложение чисел в скобках;
• D3:D5 — диапазон от D3 до D5 включительно.

Как работает: складывает все значения диапазона; несколько диапазонов перечисляются через «;». Результат пересчитывается автоматически при изменении исходных чисел.

СРЗНАЧ — среднее значение. Пример: средняя стоимость обучения в D7:

=СРЗНАЧ(D3:D5)

📐 Разбор формулы:

• = — начало формулы;
• СРЗНАЧ — среднее (сумма, делённая на количество);
• D3:D5 — диапазон усреднения.

Как работает: делит сумму диапазона на число значений. Дробный результат приводят к 2 знакам после запятой кнопкой «Уменьшить разрядность» или через Формат ячеек → Число → Числовой.

СЧЁТЕСЛИ — количество ячеек по условию. Задача: сколько студентов платят больше средней стоимости.

• Неоптимальный вариант — с вписанным числом: =СЧЁТЕСЛИ(D3:D5;">21666,67"). Минус: при изменении данных среднее поменяется, а жёстко вписанное число — нет, и формула станет неверной.
• Оптимальный вариант — со ссылкой на ячейку среднего:

=СЧЁТЕСЛИ(D3:D5;">"&$D$7)

📐 Разбор формулы:

• = — начало формулы;
• СЧЁТЕСЛИ — подсчёт ячеек, отвечающих условию;
• D3:D5 — где считаем;
• ; — отделяет диапазон от условия;
• ">" — условие «больше» (как текст, в кавычках);
• & — склейка условия со значением ячейки;
• $D$7 — закреплённый адрес ячейки со средним.

Как работает: считает ячейки диапазона, которые больше значения в D7. Абсолютная ссылка $D$7 не «ломается» при добавлении строк (нужно лишь расширить диапазон, например до D3:D6).

Копирование формул (автозаполнение). Однотипные формулы быстро создаются протягиванием за уголок выделенной ячейки (курсор становится тонким чёрным крестиком). Пример: в C2 ввести =A2+B2, затем протянуть вниз — каждая ячейка столбца C получит свою формулу.

📐 Разбор формулы:

• = — начало формулы;
• A2, B2 — относительные адреса складываемых ячеек;
• + — сложение.

Как работает: =A2+B2 складывает два числа; при протягивании вниз адреса сдвигаются (в C3 станет =A3+B3) — это и есть относительная адресация.

Относительная и абсолютная адресация. Относительный адрес (A2) при копировании сдвигается; абсолютный ($A$2) закреплён и не меняется. Доллары быстро ставятся клавишей F4. Это нужно, когда во всех формулах надо ссылаться на одну и ту же ячейку.

СУММЕСЛИ — сумма по условию. Задача: просуммировать стоимость обучения всех студентов факультета СГФ:

=СУММЕСЛИ(C3:C6;"СГФ";D3:D6)

📐 Разбор формулы:

• = — начало формулы;
• СУММЕСЛИ — сложить значения, отвечающие условию;
• C3:C6 — диапазон проверки условия (столбец факультетов);
• "СГФ" — условие (строки со значением «СГФ»);
• D3:D6 — диапазон суммирования (стоимость обучения).

Как работает: Excel находит в C3:C6 строки «СГФ» и складывает соответствующие им суммы из D3:D6.

РАБДЕНЬ — дата через N рабочих дней. Полезно для дедлайнов «не позднее, чем за N рабочих дней до». Пример: дата защиты 6 июня записана в B16, ищем крайнюю дату сдачи за 7 рабочих дней до неё; ячейке задают формат «Дата»:

=РАБДЕНЬ($B$16;-7)

📐 Разбор формулы:

• = — начало формулы;
• РАБДЕНЬ — дата, сдвинутая на число рабочих дней (выходные пропускаются);
• $B$16 — закреплённая исходная дата;
• -7 — 7 рабочих дней назад (минус = «до»; без минуса = «после»).

Как работает: отсчитывает 7 рабочих дней назад от даты в B16, пропуская субботы и воскресенья.

Чтобы учесть ещё и государственные праздники, их даты выписывают в отдельные ячейки, выделяют и через правую кнопку → «Присвоить имя» создают именованный диапазон (например, Weekends), а затем добавляют его в формулу:

=РАБДЕНЬ($B$16;-7; Weekends)

📐 Разбор формулы:

• =, РАБДЕНЬ, $B$16, -7 — как выше;
• Weekends — имя диапазона с дополнительными выходными (праздниками), которые функция тоже пропустит.

Как работает: теперь, кроме суббот и воскресений, при отсчёте пропускаются и перечисленные праздники, поэтому итоговая дата сдвигается. Границы диапазона редактируются через «Формулы → Определённые имена → Диспетчер имён».

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

Автоматически выделяет ячейки, отвечающие условию (цвет заливки, шрифт и т. п.).

Пример 1 — подсветить цветом стоимость обучения выше средней: выделить ячейки → «Главная → Стили → Условное форматирование → Создать правило → Использовать формулу для определения форматируемых ячеек», ввести условие, выбрать заливку:

=D3>$D$8

📐 Разбор формулы:

• = — начало формулы-условия;
• D3 — первая ячейка диапазона (относительный адрес — правило применяется к каждой ячейке);
• > — «больше»;
• $D$8 — закреплённая ячейка со средним значением.

Как работает: каждая ячейка сравнивается со средним в D8; если больше — заливается выбранным цветом.

Пример 2 — выделить полужирным стоимость обучения студентов факультета СГФ: правило строится по ячейке столбца факультетов (например, C3 — факультет студента из первой строки диапазона). Готовые правила меняются через «Условное форматирование → Управление правилами».

Итоговое задание

Создать документ Excel (например, для отдела кадров или группы студентов) и выполнить:

1. Использовать ячейки с типами данных «Числовой», «Текстовый», «Дата».
2. Создать фильтры и выполнить 3 любых запроса; в файле Word описать каждый: задача, формула/условие, исходная таблица и результат.
3. Применить формулы, в том числе СУММ, СЧЁТЕСЛИ, СУММЕСЛИ, СРЗНАЧ.
4. Найти в мастере функций ещё одну любую функцию и тоже использовать её.
5. Создать два правила условного форматирования и описать их в отчёте Word.

Результат сдачи: файл Excel с таблицей и файл Word с отчётом (по заданиям 2 и 5); оба файла загружаются вместе, упакованные в архив (Zip).