Работа с формулами в Excel: относительные и абсолютные ссылки
Цели и задачи
- Цель по SMART: к концу урока научиться создавать, копировать и проверять формулы в Excel с использованием относительных и абсолютных ссылок, а также применять их для решения расчётной задачи из финансово-экономического контекста.
- Образовательная задача: повторить структуру формулы Excel и правила записи арифметических операций, раскрыть различие между относительными, абсолютными и смешанными ссылками.
- Развивающая задача: развивать алгоритмическое и критическое мышление через исследование изменения формул при копировании и поиск ошибок в расчётной модели.
- Коммуникативная задача: формулировать гипотезы, объяснять выбор типа ссылки и аргументированно обсуждать результаты в парах.
- Воспитательная задача: формировать ответственное отношение к точности расчётов, цифровой грамотности и использованию электронных таблиц в учебной и профессиональной деятельности.
Планируемые результаты
Личностные
- Осознавать практическую ценность электронных таблиц для финансового планирования, анализа данных и будущей профессиональной деятельности.
- Проявлять ответственность за корректность введённых данных и полученных результатов расчёта.
- Оценивать собственную готовность применять цифровые инструменты для решения учебных и жизненных задач.
- Понимать значение проверки формул как элемента информационной безопасности и цифровой культуры.
Метапредметные
- Планировать последовательность построения расчётной таблицы и выбирать подходящий тип ссылки.
- Анализировать изменение формул при копировании и выявлять причины появления неверных результатов.
- Работать с информацией в табличной форме, представлять результат расчёта и делать выводы.
- Сотрудничать в паре, формулировать гипотезу, проверять её экспериментально и аргументировать вывод.
- Применять навыки математической и финансовой грамотности в расчёте стоимости покупки с учётом скидки и налога.
Предметные
- Знать назначение формул, операторов арифметических действий и ссылок на ячейки в Excel.
- Различать относительные, абсолютные и смешанные ссылки и объяснять их поведение при копировании формулы.
- Уметь вводить формулы, использовать маркер автозаполнения и фиксировать ссылку с помощью знака $ или клавиши F4.
- Уметь проверять формулу по исходным данным и исправлять типичные ошибки.
- Владеть приёмами построения простой расчётной модели с постоянным параметром, например ставкой налога или процентом скидки.
Универсальные учебные действия (УУД)
Личностные УУД
- Определяют личную значимость точных расчётов для учебных проектов и будущей профессии.
- Проявляют самостоятельность при выборе способа построения формулы.
- Осознают необходимость проверки цифрового результата перед его использованием.
- Формируют ответственное отношение к данным, связанным с деньгами и планированием бюджета.
Регулятивные УУД
- Формулируют цель исследования поведения ссылок при копировании формул.
- Планируют последовательность действий: ввод данных, создание формулы, копирование, проверка.
- Сверяют полученный результат с ожидаемым и находят место ошибки.
- Оценивают степень освоения материала по чек-листу и намечают способ коррекции.
Познавательные УУД
- Выделяют структуру формулы и классифицируют ссылки по способу изменения.
- Моделируют расчётную ситуацию в электронной таблице.
- Сравнивают результаты копирования формул с относительной и абсолютной ссылкой.
- Формулируют правило на основе наблюдений и проверяют его на новом примере.
- Используют знаки $ и адреса ячеек для построения корректной расчётной модели.
Коммуникативные УУД
- Распределяют роли в паре: оператор таблицы и аналитик результата.
- Задают уточняющие вопросы о поведении формул и ссылок.
- Объясняют партнёру причину изменения или сохранения адреса ячейки.
- Аргументированно представляют общий вывод и корректно обсуждают найденную ошибку.
Подготовка учителя к уроку
- Подготовить компьютеры с установленным табличным процессором Excel или совместимым офисным приложением: один компьютер на одного или двух учеников.
- Создать пустой файл для практической работы и проверить, что отображаются формулы, адреса ячеек и строка формул.
- Подготовить демонстрационную таблицу с колонками «Товар», «Цена», «Количество», «Сумма», «Скидка», «Итог».
- Разместить на доске или стартовом слайде краткую шпаргалку: относительная ссылка A1, абсолютная ссылка $A$1, смешанные ссылки A$1 и $A1.
- Распечатать карточки исследовательского задания трёх уровней — по одной на пару, всего по числу пар в классе.
- Подготовить лист самооценки с пунктами «создаю формулу», «копирую формулу», «фиксирую ссылку», «нахожу ошибку».
- Подготовить файл с намеренной ошибкой в формуле для этапа самостоятельной работы.
- Вывести на экран условие межпредметной задачи о расчёте стоимости покупки с учётом скидки и налога.
- Проверить проектор, компьютер учителя, подключение к интерактивной доске и возможность демонстрации формул, а не только результатов.
Ход урока
Этап 1. Организационный момент и мотивация (3 мин)
Цель этапа: включить учащихся в исследовательскую деятельность и показать практическую значимость точных формул.
Время | Действие учителя | Действие учеников |
|---|---|---|
1 мин | Учитель приветствует класс и показывает на экране небольшую таблицу с ценами товаров. «Здравствуйте, ребята. Сегодня мы будем не просто вводить формулы, а исследовать, почему одна и та же формула при копировании иногда работает правильно, а иногда даёт ошибку. Откройте рабочую книгу и приготовьте тетради для фиксации правил и выводов». | Открывают табличный процессор, создают или открывают рабочую книгу, записывают дату и тему урока. |
2 мин | Учитель использует приём «Проблемный вопрос»: «Представьте, что в таблице нужно рассчитать стоимость десяти товаров, а ставка налога записана только в одной ячейке. Как сделать так, чтобы при копировании формулы ставка не “уезжала”? Предложите гипотезы, пока не открывая справку». | Индивидуально формулируют гипотезу, затем обсуждают её с соседом и предлагают варианты: «нужно закрепить ячейку», «нужно запретить изменение адреса», «можно каждый раз вводить ставку вручную». |
Завершение этапа: учитель подводит итог: «Мы увидели практическую проблему: формулу нужно копировать быстро, но отдельные данные должны оставаться постоянными. Сейчас проверим наши гипотезы экспериментом и выведем правило сами».
Этап 2. Актуализация знаний (5 мин)
Цель этапа: восстановить знания о структуре формул, адресах ячеек и копировании содержимого.
Время | Действие учителя | Действие учеников |
|---|---|---|
2 мин | Учитель показывает на доске таблицу с числами в ячейках B2 и C2. «Вспомним базовый алгоритм. Как начинается формула? Как записать сумму из ячеек B2 и C2? Как вычислить произведение цены на количество? Обратите внимание: в формуле используются адреса ячеек, а не только сами числа». | Записывают и проговаривают примеры: «формула начинается со знака =», «=B2+C2», «=B2*C2». Один ученик вводит формулу на демонстрационном компьютере. |
3 мин | Учитель предлагает мини-проверку «Верно — неверно»: «Формула =A1+B1 складывает содержимое двух ячеек. При копировании формулы вниз адрес A1 всегда останется тем же. Символ * обозначает умножение. Ссылка B3 — это адрес ячейки». После каждого утверждения просит кратко обосновать ответ. | Поднимают карточки «верно» или «неверно», объясняют: первое, третье и четвёртое утверждения верны, второе неверно, потому что относительная ссылка изменится при копировании вниз. |
Завершение этапа: учитель подводит итог: «Мы вспомнили, что формула обращается к ячейкам по адресам, а относительные адреса могут изменяться. Теперь выясним, какие ссылки можно изменять, а какие необходимо сохранять».
Этап 3. Постановка проблемы и целеполагание (4 мин)
Цель этапа: зафиксировать затруднение и совместно сформулировать цель исследования.
Время | Действие учителя | Действие учеников |
|---|---|---|
2 мин | Учитель раздаёт каждой паре карточку с формулами: в D2 записано =B2*C2, в E2 должна использоваться ставка скидки из ячейки H1. «Скопируйте формулу из D2 в D3 и сравните результат. Затем попробуйте записать формулу для скидки и скопировать её вниз. Что изменилось? Какая ячейка должна оставаться постоянной?» | В парах копируют формулу маркером автозаполнения, сравнивают формулы в строке формул и фиксируют, что ссылка H1 превращается в H2, H3 и далее. |
2 мин | Учитель организует приём «Подумай — обсуди в паре — поделись»: «Сформулируйте, чему нам необходимо научиться, чтобы таблица автоматически пересчитывалась правильно. Запишите цель одним предложением». Учитель собирает формулировки и уточняет их. | Формулируют цель: «научиться использовать относительные и абсолютные ссылки, чтобы правильно копировать формулы». Предлагают план: изучить виды ссылок, проверить их на примерах, применить в расчётной задаче. |
Посмотрите план целиком
Зарегистрируйтесь — и откройте план урока по этой теме полностью: цели, ход урока и рефлексия по ФГОС.
Завершение этапа: учитель подводит итог: «Затруднение возникло не в вычислении, а в управлении адресами ячеек. Наша задача — исследовать поведение ссылок и научиться выбирать нужный тип ссылки для каждой части формулы».
Этап 4. Открытие нового знания через исследование (10 мин)
Цель этапа: вывести правило использования относительных, абсолютных и смешанных ссылок на основе практического эксперимента.
Время | Действие учителя | Действие учеников |
|---|---|---|
3 мин | Учитель демонстрирует эксперимент. «В ячейке B2 запишите число 10, в B3 — 20. В C2 запишите формулу =B2*2 и скопируйте её в C3. Посмотрите на строку формул в C3. Как изменился адрес? Это относительная ссылка: при переносе формулы меняется относительно её нового положения». | Вводят данные, создают формулу =B2*2, копируют её вниз и называют результат: в C3 появляется формула =B3*2. |
4 мин | Учитель предлагает второй эксперимент. «В ячейке E1 запишите число 0,2 — это ставка скидки. В D2 запишите 100, в D3 — 200. В E2 создайте формулу =D2*E1 и скопируйте её вниз. Проанализируйте результат. Теперь исправьте формулу, записав =D2*$E$1, и снова скопируйте. Что изменилось?» | Наблюдают ошибочное изменение E1 на E2, затем фиксируют ссылку $E$1 и получают корректные значения скидки для обеих строк. Формулируют вывод: «абсолютная ссылка не изменяется при копировании». |
3 мин | Учитель показывает на доске таблицу: относительная ссылка A1, абсолютная $A$1, смешанные ссылки A$1 и $A1. «Знак $ фиксирует столбец или строку. Если зафиксирована строка, она не изменится при копировании по вертикали. Если зафиксирован столбец, он не изменится при копировании по горизонтали. Для быстрого переключения вариантов ссылки можно использовать клавишу F4». | Записывают схему в тетрадь, по очереди объясняют поведение четырёх ссылок и проверяют переключение ссылки F4 в строке формул. |
Запись в тетрадях
- Относительная ссылка A1 изменяется при копировании формулы.
- Абсолютная ссылка $A$1 не изменяется при копировании.
- Смешанные ссылки A$1 и $A1 фиксируют только строку или только столбец.
- Знак $ используется для фиксации строки или столбца; клавиша F4 переключает варианты ссылки.
- При построении формулы постоянный параметр, например ставка налога, обычно задают абсолютной ссылкой.
Завершение этапа: учитель подводит итог: «Мы установили правило: адрес меняется только в той части, которая не закреплена знаком $. Теперь применим это правило к реальной расчётной модели и проверим, насколько надёжно оно работает».
Этап 5. Межпредметный практикум: финансовая модель (8 мин)
Цель этапа: применить формулы и абсолютные ссылки в задаче, связанной с математикой и финансовой грамотностью.
Время | Действие учителя | Действие учеников |
|---|---|---|
2 мин | Учитель выводит на экран условие: «В таблице указаны товары, их цена и количество. В ячейке H1 записана ставка налога 20%. Рассчитайте стоимость каждой позиции, сумму скидки 10% и итоговую стоимость с налогом. Формула должна копироваться вниз без ручного исправления адресов». Учитель просит назвать столбцы и постоянные параметры. | Создают таблицу с колонками «Товар», «Цена», «Количество», «Стоимость», «Скидка», «Итог с налогом». Записывают ставки 10% и 20% в отдельные ячейки и определяют, какие ссылки должны быть абсолютными. |
4 мин | Учитель сопровождает работу вопросами: «Как вычислить стоимость позиции? Как записать скидку, если ставка находится в H2? Какую ссылку нужно использовать для ставки налога в H1? Проверьте формулу на первой строке, затем скопируйте её вниз и сравните результаты». | Вводят формулы, например =B2*C2, =D2*$H$2 и =(D2-E2)*(1+$H$1). Копируют формулы вниз, сверяют формулы в строке формул и проверяют, что H1 и H2 не изменились. |
2 мин | Учитель организует краткий обмен результатами: «Сравните модели соседней пары. Найдите одно совпадение и одну возможную ошибку. Объясните, почему в формуле стоимости ссылка относительная, а ссылка на ставку налога абсолютная». | Проверяют работу пары, используют формулировку: «цена и количество относятся к текущей строке, поэтому ссылки относительные; ставка налога общая для всех строк, поэтому ссылка абсолютная». |
Эталон решения
Для строки 2: стоимость товара вычисляется формулой $D2=B2*C2$. Скидка вычисляется формулой $E2=D2*$H$2$, где $H2$ содержит ставку скидки. Итог с налогом вычисляется формулой $F2=(D2-E2)*(1+$H$1)$, где $H1$ содержит ставку налога. При копировании вниз ссылки B2, C2 и D2 изменяются на B3, C3 и D3, а ссылки $H$2 и $H$1 остаются неизменными.
Завершение этапа: учитель подводит итог: «Мы применили формулы не в абстрактном примере, а в модели покупки. В ней относительные ссылки обеспечили работу с текущей строкой, а абсолютные сохранили общие параметры. Теперь каждый самостоятельно проверит, умеет ли он распознавать и исправлять ошибку».
Этап 6. Самостоятельная работа с самопроверкой (8 мин)
Цель этапа: проверить индивидуальное понимание правил ссылок и сформировать навык исправления формул.
Время | Действие учителя | Действие учеников |
|---|---|---|
5 мин | Учитель раздаёт карточки трёх уровней. «Выберите уровень, который соответствует вашей уверенности. На базовом уровне исправьте формулу, где при копировании изменяется ставка. На среднем уровне создайте таблицу расчёта стоимости трёх услуг с постоянной комиссией. На продвинутом уровне добавьте смешанные ссылки и объясните, когда они необходимы». | Индивидуально выполняют выбранное задание. Базовый вариант: заменяют E1 на $E$1. Средний вариант: создают формулу вида =(B2*C2)*(1-$E$1). Продвинутый вариант: строят таблицу с тарифами по столбцам и количеством по строкам, используя смешанные ссылки. |
3 мин | Учитель показывает эталон на экране и проводит самопроверку. «Сравните не только числа, но и сами формулы. Если результат совпал случайно, ошибка всё равно может сохраняться. Проверьте три пункта: знак равенства, правильные адреса, наличие знаков $ у постоянных параметров». | Сверяют формулы с эталоном, подчёркивают место возможной ошибки, исправляют её и отмечают результат в листе самооценки. |
Эталон решения
Если ставка комиссии находится в E1, а стоимость услуги в B2, формула итоговой стоимости: $C2=B2*(1-$E$1)$. При копировании вниз она преобразуется в $C3=B3*(1-$E$1)$: ссылка на стоимость меняется по строке, а ставка комиссии остаётся зафиксированной.
Завершение этапа: учитель подводит итог: «Самопроверка показала, что правильный результат зависит от структуры формулы, а не только от числа в ячейке. Осталось зафиксировать, чему мы научились, и определить следующий шаг для самостоятельной работы».
Этап 7. Рефлексия и домашнее задание (7 мин)
Цель этапа: обобщить открытое правило, оценить собственные результаты и объяснить практическую ценность формул.
Время | Действие учителя | Действие учеников |
|---|---|---|
3 мин | Учитель использует приём «Одноминутная рефлексия»: «За одну минуту завершите три предложения: “Относительная ссылка нужна, когда…”, “Абсолютная ссылка нужна, когда…”, “Сегодня я научился…”. Затем поднимите зелёную, жёлтую или красную карточку по уровню уверенности». | Заполняют три предложения, поднимают карточку самооценки и называют один результат или затруднение. |
2 мин | Учитель задаёт контрольные вопросы: «Почему формула =B2*$H$1 при копировании вниз меняется только в части B2? Что произойдёт с формулой =$B2*C$1 при копировании вправо и вниз? В какой ситуации в финансовой таблице пригодится абсолютная ссылка?» | Отвечают: «B2 относится к текущей строке, а H1 закреплена полностью»; «столбец B и строка 1 фиксированы частично»; «при общей ставке налога, комиссии или скидке». |
2 мин | Учитель сообщает домашнее задание и критерии его выполнения. «Выберите уровень, но сначала убедитесь, что понимаете обязательный минимум. В каждой работе обязательно покажите не только итоговые числа, но и формулы в строке формул». | Записывают домашнее задание, задают уточняющие вопросы, отмечают в листе самооценки пункт, который требуют повторения. |
Завершение этапа: учитель подводит итог: «Сегодня вы открыли принцип управления адресами в формулах. Именно он позволяет превращать таблицу в модель, которая пересчитывается автоматически. На следующем уроке мы продолжим работу с формулами и рассмотрим функции, логические условия или анализ данных в зависимости от выбранной задачи».
Критерии оценивания практической работы
- «5» — корректно созданы все основные формулы, правильно использованы относительные и абсолютные ссылки, формулы скопированы без ошибок, результат проверен и дано объяснение выбора ссылок.
- «4» — расчётная таблица выполнена, основное правило применено верно, но допущена одна неточность в записи формулы или оформлении, самостоятельно исправленная после проверки.
- «3» — выполнена базовая часть задания, формулы работают хотя бы для исходной строки, но при копировании допущена ошибка в фиксации постоянной ссылки или требуется помощь учителя.
- «2» — не создана рабочая формула, не различаются относительные и абсолютные ссылки, отсутствует проверка результата.
Рефлексия
Вопрос для ученика | Цель вопроса |
|---|---|
Что нового вы узнали о поведении ссылок при копировании формулы? | Выявление осознанности сформулированного правила. |
В какой части практической работы вам понадобилась абсолютная ссылка и почему? | Проверка умения связывать тип ссылки с практической задачей. |
Какую ошибку в формуле вы теперь сможете найти самостоятельно? | Диагностика навыка анализа и коррекции формул. |
Какой уровень домашнего задания вы выбираете и что нужно повторить? | Оценка готовности к самостоятельной работе и планирование коррекции. |
Завершающее слово учителя
«Сегодня мы научились управлять адресами ячеек и увидели, что формула в Excel — это алгоритм, который должен сохранять правильную логику при копировании. Типичная ошибка — оставить относительной ссылку на общую ставку скидки или налога. Проверяйте не только получившееся число, но и саму формулу в строке формул. На следующем уроке мы применим этот навык при работе с функциями и более сложными моделями данных».
Домашнее задание
Уровень | Что задать | Зачем |
|---|---|---|
Базовый (обязательный) | Создать таблицу из 4–5 товаров с ценой и количеством. Рассчитать стоимость каждой позиции, используя относительные ссылки, и применить одну общую ставку скидки через абсолютную ссылку. Скопировать формулу минимум на четыре строки. | Закрепляет структуру формулы, копирование и фиксацию постоянного параметра. При проверке смотреть на адрес ставки скидки и правильность итоговых значений. |
Средний (повышающий) | Построить расчёт семейной покупки или поездки из 5–6 позиций: стоимость, скидка, налог или комиссия. Использовать минимум две абсолютные ссылки и выполнить проверку двух результатов ручным вычислением. | Развивает финансовую грамотность и умение строить небольшую расчётную модель. При проверке смотреть на соответствие формул условию и отсутствие изменяющихся адресов постоянных параметров. |
Продвинутый (дополнительный) | Создать таблицу чувствительности: в строках указать количество товара, в столбцах — разные ставки скидки или налога, а на пересечении рассчитать итоговую стоимость. Использовать смешанные ссылки и письменно объяснить, какая часть адреса зафиксирована. | Формирует понимание смешанных ссылок, моделирования вариантов и анализа данных. При проверке смотреть на корректность $A1 и A$1, логику изменения параметров и качество объяснения. |
Контрольные вопросы перед выходом: «Чем относительная ссылка отличается от абсолютной? Что фиксирует знак $? Как записать ссылку на ячейку E1 так, чтобы она не изменялась при копировании? Почему в одной формуле могут одновременно использоваться относительная и абсолютная ссылки?»
