В этой работе ты исследуешь учебные данные о прорастании семян редиса и гороха. Для каждого вида использовали бумагу, вату и грунт, по четыре контейнера на каждый субстрат. В каждом контейнере посеяли 20 семян. Результаты записали на 7-й день. Числа придуманы для тренировки.
Сортировка меняет порядок записей. Фильтр временно скрывает неподходящие строки. Расчёт по группе объединяет записи с одинаковым признаком и находит их количество, сумму или среднее.
Ниже показаны учебные схемы окон Excel для Windows с данными этой работы. Они выделяют нужные поля; расположение и названия кнопок могут немного отличаться в твоей версии. Реальное меню из справки Microsoft подписано отдельно.
1. Как устроены данные
Одна строка — один контейнер. ID, вид, субстрат и результат должны оставаться связанными при любых действиях с таблицей.
| Столбец |
Что хранится |
| A |
ID контейнера |
| B |
Вид растения |
| C |
Субстрат |
| D |
Номер повтора |
| E |
Посеяно семян |
| F |
Проросло семян |
| G |
Доля проросших |
Данные занимают Данные!A1:G25: строка 1 — заголовки, строки 2–25 — 24 наблюдения. Лист «Ответы» предназначен для ответов и итогов фильтрации, лист «Итоги» — для расчётов по группам.
2. Доля проросших семян: формула и процентный формат
Доля — число проросших семян, делённое на число посеянных. Например, 14 из 20 — это 0,7, или 70%.
- Открой лист «Данные» и выбери ячейку
G2.
- Введи формулу
=F2/E2 и нажми Enter.
- Снова выбери G2. Потяни маленький квадрат в правом нижнем углу ячейки — маркер заполнения — вниз до G25. В таблице Excel формула может заполнить столбец автоматически; проверь последнюю строку.
- Процентный формат в шаблоне уже установлен. Если вместо 70% отображается 0,7, выдели G2:G25 и нажми Главная → % в группе «Число».
Схема 1. Формула вводится в G2. Цветные рамки показывают ячейки, на которые она ссылается. Изображён исходный порядок строк.
При копировании вниз формула =F2/E2 превращается в =F3/E3, затем в =F4/E4 и так далее. Умножать дополнительно на 100 не надо: процентный формат уже показывает долю в процентах.
Если неудобно тянуть мышью, после ввода G2 выдели G2:G25 и нажми Ctrl+D — «Заполнить вниз». В «Ответы!B7» укажи число контейнеров, в «Ответы!B8» — всего посеянных семян. Для подсчёта можно использовать =СЧЁТЗ('Данные'!A2:A25) и =СУММ('Данные'!E2:E25).
3. Сортировка по двум признакам
По возрастанию — от меньшего к большему, по убыванию — от большего к меньшему. В работе сначала нужны контейнеры с самыми большими долями. При одинаковой доле располагаем ID по возрастанию.
- Очисти фильтры: должны быть видны все 24 записи.
- Выдели
A1:G25. Можно ввести этот адрес в поле имени слева от строки формул и нажать Enter.
- Открой Данные → Сортировка.
- Проверь флажок, который указывает, что данные содержат заголовки. Первый уровень: Доля проросших → Значения ячеек → По убыванию.
- Нажми Добавить уровень. Второй уровень: ID → Значения ячеек → От А до Я.
- Нажми ОК и сохрани файл в полученном порядке.
Схема 2. Окно «Сортировка»: сначала сравниваем доли, а внутри одинаковых долей — ID.
Если Excel предлагает расширить выделение, выбери «автоматически расширить выделенный диапазон». Сортировать только столбец G нельзя: значения окажутся рядом с чужими ID. Второй уровень действует только при равенстве значений первого.
4. Фильтры И и ИЛИ
Фильтр скрывает строки целиком, сохраняя их в файле. В шаблоне стрелки фильтра уже есть в заголовках. Если в обычном диапазоне их нет, выдели таблицу и выбери Данные → Фильтр.
Выбор значений. Открой стрелку нужного столбца, сними «(Выделить все)», отметь нужные значения и нажми ОК. Числовой порог. Открой стрелку столбца G, затем Числовые фильтры → больше или равно….
Реальное меню Excel: 1 — поиск, 2 — список значений, 3 — числовые условия. Изображение из справки Microsoft.
Условия в разных столбцах работают как И. Например, для отбора «Грунт И доля не менее 85%»:
- В столбце C «Субстрат» оставь только Грунт и нажми ОК.
- В столбце G задай сравнение больше или равно и значение 85%. Допустима равная ему запись
0,85 при десятичной запятой.
- Нажми ОК. Видимыми останутся строки, которые отвечают обоим условиям.
Схема 3. Два последовательных действия: выбрать грунт в C и задать порог 85% в G. Вторая строка условий диалога остаётся пустой.
«Не менее 85%» означает ≥ 85%, включая ровно 85%. Не вводи просто 85: доля 85% хранится как 0,85. Переключатель И/ИЛИ внутри диалога объединяет два условия одного столбца; для связи C и G нажимать его не нужно.
Несколько отмеченных значений в одном столбце работают как ИЛИ. Чтобы оставить бумагу или вату независимо от вида, сначала очисти все фильтры, а затем отметь оба субстрата в C:
Схема 4. В одном списке отмечены «Бумага» и «Вата». Контейнеру достаточно совпасть с любым из этих значений.
В задаче 3 выполни три независимых отбора. Перед каждым очищай предыдущие условия.
| Отбор |
Что установить |
Куда записать ID |
| А |
B: Редис; G: не менее 90% |
Ответы!B13 |
| Б |
C: Грунт; G: не менее 85% |
Ответы!B14 |
| В |
C: Бумага и Вата; остальные столбцы без ограничений |
Ответы!B15 |
Записывай только ID через запятую, с латинскими R и P, без количества и пояснений. Перед сдачей задачи 3 снова установи отбор Б и сохрани файл. Не удаляй записи и не скрывай строки вручную.
5. Как очистить фильтры
Щёлкни любую ячейку исходной таблицы. На вкладке Данные в группе «Сортировка и фильтр» нажми Очистить. Все 24 строки снова станут видимыми, а стрелки останутся в заголовках.
Схема 5. Команда «Очистить» убирает условия отбора. Перед новым независимым фильтром начни с полной таблицы.
Чтобы убрать условие только одного столбца, открой его стрелку и выбери команду удаления фильтра из этого столбца. Очистка фильтра не возвращает исходный порядок строк после сортировки.
6. Расчёты только по видимым строкам
Обычная СУММ учитывает строки, скрытые фильтром. Для видимых строк используй ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Сначала на листе «Данные» установи отбор Б, затем перейди на лист «Ответы».
В B17 — количество видимых контейнеров:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;'Данные'!A2:A25)
Код 103 считает непустые видимые ячейки. В столбце A у каждого контейнера есть ID.
В B18 — сумма проросших в видимых контейнерах:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;'Данные'!F2:F25)
Код 109 складывает видимые числовые значения. Коды 103 и 109 также пропускают строки, скрытые вручную; в этой работе отбор выполняем именно фильтром.
В B20 — сумма по всей таблице:
=СУММ('Данные'!F2:F25)
Схема 6. Лист «Ответы» после отбора Б. В строке формул показано, как рассчитана выбранная ячейка B18.
Сам фильтр должен остаться действующим на листе «Данные» при сохранении и сдаче задач 3 и 4.
7. Расчёты по субстратам: количество, сумма и среднее
Группа объединяет строки с одним признаком, например все контейнеры на бумаге. Четыре значения 14, 16, 15 и 17 дают сумму 62 и среднее 62 / 4 = 15,5. Если в группу входят оба вида растения, учитываем контейнеры обоих видов.
Очисти фильтры на листе «Данные». На листе «Итоги» в A6 записано название субстрата. Заполни первую строку расчётов:
| Ячейка |
Что вычисляем |
Формула |
B6 |
Количество контейнеров |
=СЧЁТЕСЛИМН('Данные'!$C$2:$C$25;A6) |
C6 |
Всего посеяно |
=СУММЕСЛИМН('Данные'!$E$2:$E$25;'Данные'!$C$2:$C$25;A6) |
D6 |
Всего проросло |
=СУММЕСЛИМН('Данные'!$F$2:$F$25;'Данные'!$C$2:$C$25;A6) |
E6 |
Среднее число проросших на контейнер |
=СРЗНАЧЕСЛИМН('Данные'!$F$2:$F$25;'Данные'!$C$2:$C$25;A6) |
F6 |
Доля проросших в группе |
=D6/C6 |
Формулу можно напечатать целиком. Другой способ: выбери D6, нажми ƒx слева от строки формул, найди СУММЕСЛИМН и заполни аргументы. В поле условия вводи A6, чтобы взять название группы из ячейки, а не писать слово «Бумага» вручную.
Схема 7. Аргументы СУММЕСЛИМН для ячейки Итоги!D6. Справа показан смысл выбранных диапазонов и значение условия.
Как запомнить порядок: у СУММЕСЛИМН сначала идёт диапазон чисел, затем пары «где проверить; чему должно равняться». У СЧЁТЕСЛИМН отдельного диапазона для суммирования нет: она считает подходящие строки. У СРЗНАЧЕСЛИМН первым идёт диапазон, для которого ищем среднее.
Знаки $ закрепляют диапазон при копировании. В режиме редактирования формулы можно выделить ссылку и нажимать F4, пока не появятся нужные знаки $, либо ввести их с клавиатуры.
Схема 8. Копирование из D6 в D7 и D8: исходные диапазоны закреплены, ссылка на название группы меняется.
После заполнения B6:F6 скопируй формулы вниз до строки 8. Общая доля группы — сумма проросших / сумма посеянных. Проценты разных групп не складывают.
В строке 9 собери общие итоги: в B9 — =СУММ(B6:B8), в C9 — =СУММ(C6:C8), в D9 — =СУММ(D6:D8). Среднее: в E9 формула =D9/B9. Общая доля: в F9 формула =D9/C9.
Функции с окончанием МН допускают одно или несколько условий; при нескольких условиях должны выполняться все. Условные функции учитывают весь указанный диапазон, включая скрытые строки. Предварительная сортировка им не требуется.
8. Группировка по видам растений
В таблице Итоги!A12:F15 действуй так же, но проверяй столбец B листа «Данные», где записан вид. Название группы бери из A13, затем из A14.
Например, сумма проросших для вида из A13, ячейка D13:
=СУММЕСЛИМН('Данные'!$F$2:$F$25;'Данные'!$B$2:$B$25;A13)
Заполни B13:F13, затем скопируй формулы в строку 14. В строке 15 вычисли общие итоги: количество и суммы — по строкам 13–14, среднее =D15/B15, долю =D15/C15. Общие итоги по видам и по субстратам должны совпасть: исходная таблица одна и та же.
9. Два признака одновременно
В таблице Итоги!A19:D25 каждая строка задаёт пару «вид + субстрат». В A20 записан вид, в B20 — субстрат. В C20 суммируй проросшие семена только у строк, где совпали оба признака:
=СУММЕСЛИМН('Данные'!$F$2:$F$25;'Данные'!$B$2:$B$25;A20;'Данные'!$C$2:$C$25;B20)
Схема 9. Две пары условий в окне аргументов. A20 и B20 — ссылки на ячейки листа «Итоги».
Для доли в D20 раздели результат C20 на сумму посеянных для той же пары:
=C20/СУММЕСЛИМН('Данные'!$E$2:$E$25;'Данные'!$B$2:$B$25;A20;'Данные'!$C$2:$C$25;B20)
Скопируй C20:D20 вниз до строки 25. Не заменяй знаменатель готовым числом: его нужно получить по данным. Сохраняй ссылки на названия групп.
10. Как сохранить и сдать работу
- Работай в копии файла
bioit_sort_filter.xlsx, приложенного к задачам.
- Выбери Файл → Сохранить как, задай понятное имя и оставь тип Книга Excel (*.xlsx).
- После дальнейших изменений нажимай
Ctrl+S. Загружай в ответ на задачу сохранённый XLSX.
Схема 10. Сохраняем книгу в формате XLSX. CSV не сохраняет структуру этой работы: несколько листов, формулы и фильтры.
Можно продолжать работу в одном файле: каждая задача проверяет свою часть. Задачи 3 и 4 сдавай с активным отбором Б, а перед задачей 5 очисти фильтр. Чтобы вернуться к уже сданному этапу, сохраняй отдельные копии файла.
Не меняй имена листов, заголовки и исходные значения A2:F25. Порядок строк менять можно. Расчётные ячейки заполняй формулами со ссылками на исходные данные. Для групп бери условие из указанной ячейки с названием группы.
Используй обычные адреса ячеек и ограниченные диапазоны, как в примерах. Русские и английские названия функций принимаются. В английской версии: COUNTIFS, SUMIFS, AVERAGEIFS, SUBTOTAL, SUM. Разделитель аргументов зависит от настроек Excel. Не округляй значения формул: число отображаемых знаков после запятой меняй форматом ячейки.
В задачах 5 и 6 также принимаются СЧЁТЕСЛИ, СУММЕСЛИ и СРЗНАЧЕСЛИ с одним условием. У этих функций порядок аргументов отличается: при выборе такого варианта проверь подсказку Excel. В задаче 7 используй СУММЕСЛИМН для двух условий.
Максимум — 7 баллов, по одному за каждую практическую задачу. Поля свободных пояснений на листе «Ответы» оставлены для самопроверки и не влияют на автоматический зачёт. Биологический вывод должен относиться к этим учебным наблюдениям, а не ко всем растениям.
Справка по интерфейсу: сортировка, фильтрация, автофильтр. Учебные схемы подготовлены для этой работы.