Значения ошибок в формулах. Перемещение и копирование формул.

Группа С-11, 2025 года

Занятие по рабочей программе №07

Дисциплина: ОП .03 «Информационные технологии в профессиональной деятельности»

Раздел 3. Технология обработки числовой информации.

Тема: Значения ошибок в формулах. Перемещение и копирование формул.

1. Цель занятия:

   1. Дидактическая:

• сформулировать представление студентов о понятии Технология обработки информации;

• выяснить основные понятия MS EXCEL;

• раскрыть способы ввода, перемещения и копирование формул в таблицу;

• выяснить принципы и методы использования ошибок в формулах;

• систематизировать и обобщить знания по работе с электронными таблицами;

• углубить и закрепить знания по дисциплине «Информационные технологии в профессиональной деятельности».

Воспитательная:

• развивать коммуникативные способности;

• развивать аналитические способности;

• развивать творческий подход к процессу обучения.

• воспитывать самостоятельность, дисциплинированность;

• стимулировать студентов к изучению дисциплины;

• побуждать к формированию активной жизненной позиции;

• прививать уважение и любовь к будущей профессии.

Вид занятия: лекция.

Тип занятия: обобщение и систематизация знаний.

Форма проведения занятия: репродуктивная и эвристическая беседа.

Междисциплинарные связи:

Обеспечивающие ОГСЭ.04 Физическая культура, МДК.04.01 Основы управления работами спец. подразделения швейного производства.

Обеспечиваемые ОП.01 Инженерная графика, ОП.08 Общая технология отрасли, ОП.11 Экономика, организация и планирование производства, ОП.16 Маркетинг в отрасли.

Методическое обеспечение: опорный конспект.

Литература:

1. Михеева, Е.В. Практикум по информационным технологиям в профессиональной деятельности : учебное пособие. - М. : Академия, 2008. - 8 – е изд. - 256 с.- (Среднее профессиональное образование), стр. 173 - 175

2. Практикум по MS Excel - http://informatika.delayu.ru/Zadanija/Exceldannye

ХОД ЗАНЯТИЯ

1. Ознакомление с темой, целью и планом занятия.

Тема: Значения ошибок в формулах. Перемещение и копирование формул.

ПЛАН

1. Поиск ошибок в формулах.

2. Перемещение и копирование формулы.

1. Изложение и изучение нового материала.

ЛИТЕРАТУРА: [2], стр. 173 - 175

1. Поиск ошибок в формулах

Кроме неожиданных результатов, формулы иногда возвращают значения ошибок. Ниже представлены некоторые инструменты, с помощью которых вы можете искать и исследовать причины этих ошибок и определять решения.

Примечание: В лекции приводятся методы, которые помогут вам исправлять ошибки в формулах. Этот список не исчерпывающий — он не охватывает все возможные ошибки формул. Для получения справки по конкретным ошибкам поищите ответ на свой вопрос или задайте его на форуме сообщества Microsoft Excel.

1.1. Ввод простой формулы

Формулы — это выражения, с помощью которых выполняются вычисления со значениями на листе. Формула начинается со знака равенства (=). Например, следующая формула складывает числа 3 и 1:

=3+1

Формула также может содержать один или несколько из таких элементов: функции, ссылки, операторы и константы.

Части формулы

1. Функции: это специальные формулы Excel, которые выполняют определенные вычисления. Например, функция ПИ() возвращает значение числа Пи: 3,142...

2. Ссылки: это ссылки на отдельные ячейки или диапазоны. Например, A2 возвращает значение ячейки A2.

3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.

4. Операторы: оператор * (звездочка) служит для умножения чисел, а оператор ^ (крышка) — для возведения числа в степень. С помощью + и – можно складывать и вычитать значения, а с помощью / — делить их.

Примечание: Для некоторых функций требуются так называемые аргументы.

Аргументы — это значения, которые некоторые функции используют при вычислениях. Аргументы функции указываются в ее скобках (). Функция ПИ не требует аргументов, поэтому у нее пустые скобки. У некоторых функций несколько аргументов, в том числе необязательные. Аргументы разделяются точкой с запятой (;).

Например, функция СУММ требует только один аргумент, но у нее может быть до 255 аргументов (включительно).

Пример одного аргумента: =СУММ(A1:A10).

Пример нескольких аргументов: =СУММ(A1:A10;C1:C10).

1.2. Исправление распространенных ошибок при вводе формул

В приведенной ниже таблице собраны некоторые наиболее частые ошибки, которые допускают пользователи при вводе формулы, и описаны способы их исправления.

1.3. Исправление распространенных ошибок в формулах

Вы можете использовать определенные правила для поиска ошибок в формулах. Они не гарантируют исправление всех ошибок на листе, но могут помочь избежать распространенных проблем. Эти правила можно включать и отключать независимо друг от друга.

Существуют два способа пометки и исправления ошибок: последовательно (как при проверке орфографии) или сразу при появлении ошибки во время ввода данных на листе.

Ошибку можно исправить с помощью параметров, отображаемых приложением Excel, или игнорировать, щелкнув команду Пропустить ошибку. Ошибка, пропущенная в конкретной ячейке, не будет больше появляться в этой ячейке при последующих проверках. Однако все пропущенные ранее ошибки можно сбросить, чтобы они снова появились.

1.4. Включение и отключение правил проверки ошибок

1. Для Excel в Windows щелкните файл параметры формул или Для Excel на Mac выберите меню Excel параметры проверки ошибок. 

В Excel 2007 нажмите кнопку Microsoft Office  и выберите Параметры Excel  Формулы.

1. В разделе Поиск ошибок установите флажок Включить фоновый поиск ошибок. Все найденные ошибки помечаются треугольником в левом верхнем углу ячейки.

1. Чтобы изменить цвет треугольника, которым помечаются ошибки, выберите нужный цвет в поле Цвет индикаторов ошибок.

2. В разделе Правила поиска ошибок установите или снимите флажок для любого из следующих правил:

• Ячейки, содержащие формулы, которые могут привести к ошибке: формула не использует ожидаемый синтаксис, аргументы или типы данных. Значения ошибок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! и #VALUE!. Каждое из этих значений ошибок имеет разные причины и разрешается различными способами.

• Примечание: Если ввести значение ошибки прямо в ячейку, оно сохраняется как значение ошибки, но не помечается как ошибка. Но если на эту ячейку ссылается формула из другой ячейки, эта формула возвращает значение ошибки из ячейки.

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

• Ввод данных, не являющихся формулой, в ячейку вычисляемого столбца.

• Введите формулу в ячейку вычисляемого столбца, а затем нажмите клавиши CTRL +Z или нажмите кнопку "Отменить  на панели быстрого доступа.

• Ввод новой формулы в вычисляемый столбец, который уже содержит одно или несколько исключений.

• Копирование в вычисляемый столбец данных, не соответствующих формуле столбца. Если копируемые данные содержат формулу, эта формула перезапишет данные в вычисляемом столбце.

• Перемещение или удаление ячейки из другой области листа, если на эту ячейку ссылалась одна из строк в вычисляемом столбце.

• Ячейки, содержащие годы, представленные в виде двух цифр: ячейка содержит текстовую дату, которую можно неправильно представить как неправильное время при использовании в формулах. Например, дата в формуле =ГОД("1.1.31") может относиться как к 1931, так и к 2031 году. Используйте это правило для выявления дат в текстовом формате, допускающих двоякое толкование.

• Числа, отформатированные как текст или предшествуют апострофу: ячейка содержит числа, хранящиеся в виде текста. Обычно это является следствием импорта данных из других источников. Числа, хранящиеся как текст, могут стать причиной неправильной сортировки, поэтому лучше преобразовать их в числовой формат. =СУММ(A1:A10) отображается как текст.

• Формулы, несогласованные с другими формулами в регионе: формула не соответствует шаблону других формул рядом с ней. Во многих случаях формулы, смежные с другими формулами, отличаются только используемыми ссылками. В следующем примере четырех смежных формул Excel отображает ошибку рядом с формулой =СУММ(A10:C10) в ячейке D4, так как смежные формулы приращены на одну строку, а одна увеличивается на 8 строк. Excel ожидает формулу =СУММ(A4:C4).

Если используемые в формуле ссылки не соответствуют ссылкам в смежных формулах, приложение Microsoft Excel сообщит об ошибке.

• Формулы, которые пропускают ячейки в области: формула не может автоматически включать ссылки на данные, вставленные между исходным диапазоном данных и ячейкой, содержащую формулу. Это правило позволяет сравнить ссылку в формуле с фактическим диапазоном ячеек, смежных с ячейкой, содержащей формулу. Если смежные ячейки содержат дополнительные значения и не являются пустыми, Excel отображает рядом с формулой ошибку.

Например, при использовании этого правила Excel отображает ошибку для формулы =СУММ(D2:D4), поскольку ячейки D5, D6 и D7, смежные с ячейками, на которые ссылается формула, и ячейкой с формулой (D8), содержат данные, на которые должна ссылаться формула.

• Незаблокированные ячейки, содержащие формулы: формула не заблокирована для защиты. По умолчанию все ячейки на листе блокируются, поэтому их нельзя изменить при защите листа. Это поможет избежать случайных ошибок, таких как случайное удаление или изменение формул. Эта ошибка указывает, что ячейка была разблокирована, но лист не был защищен. Убедитесь, что ячейка не заблокирована.

• Формулы, ссылаемые на пустые ячейки: формула содержит ссылку на пустую ячейку. Это может привести к неверным результатам, как показано в приведенном далее примере.

Предположим, требуется найти среднее значение чисел в приведенном ниже столбце ячеек. Если третья ячейка пуста, она не используется в расчете, поэтому результатом будет значение 22,75. Если эта ячейка содержит значение 0, результат будет равен 18,2.

• Недопустимые данные, введенные в таблицу: в таблице есть ошибка проверки. Проверьте параметр проверки для ячейки, перейдя на вкладку "Данные"  data Tools  проверки данных.

1.5. Последовательное исправление распространенных ошибок в формулах

1. Выберите лист, на котором требуется проверить наличие ошибок.

2. Если расчет листа выполнен вручную, нажмите клавишу F9, чтобы выполнить расчет повторно.

Если диалоговое окно Поиск ошибок не отображается, щелкните вкладку Формулы, выберите Зависимости формул и нажмите кнопку Поиск ошибок.

1. Чтобы повторно проверить пропущенные ранее ошибки, щелкните Файл  Параметры  Формулы. Для Excel на Mac выберите меню Excel параметры проверки ошибок.

В разделе Поиск ошибок выберите Сброс пропущенных ошибок и нажмите кнопку ОК.

Примечание: Сброс пропущенных ошибок применяется ко всем ошибкам, которые были пропущены на всех листах активной книги.

Совет: Советуем расположить диалоговое окно Поиск ошибок непосредственно под строкой формул.

1. Нажмите одну из управляющих кнопок в правой части диалогового окна. Доступные действия зависят от типа ошибки.

2. Нажмите кнопку Далее.

Примечание: Если нажать кнопку Пропустить ошибку, помеченная ошибка при последующих проверках будет пропускаться.

1.6. Исправление ошибки с #

Если формула не может правильно вычислить результат, в Excel отображается значение ошибки, например #####, #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА!, #ЗНАЧ!. Ошибки разного типа имеют разные причины и разные способы решения.

Приведенная ниже таблица содержит ссылки на статьи, в которых подробно описаны эти ошибки, и краткое описание.

1.7. Просмотр формулы и ее результата в окне контрольного значения

Если ячейки не видны на листе, для просмотра их и содержащихся в них формул можно использовать панель инструментов "Окно контрольного значения". С помощью окна контрольного значения удобно изучать, проверять зависимости или подтверждать вычисления и результаты формул на больших листах. При этом вам не требуется многократно прокручивать экран или переходить к разным частям листа.

Эту панель инструментов можно перемещать и закреплять, как и любую другую. Например, можно закрепить ее в нижней части окна. На панели инструментов выводятся следующие свойства ячейки: 1) книга, 2) лист, 3) имя (если ячейка входит в именованный диапазон), 4) адрес ячейки 5) значение и 6) формула.

Примечание: Для каждой ячейки может быть только одно контрольное значение.

Добавление ячеек в окно контрольного значения

1. Выделите ячейки, которые хотите просмотреть.

Чтобы выделить все ячейки с формулами, на вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить (вы также можете нажать клавиши CTRL+G или CONTROL+G на компьютере Mac). Затем выберите Выделить группу ячеек и Формулы.

1. На вкладке Формулы в группе Зависимости формул нажмите кнопку Окно контрольного значения.

2. Нажмите кнопку Добавить контрольное значение.

1. Убедитесь, что вы выделили все ячейки, которые хотите отследить, и нажмите кнопку Добавить.

1. Чтобы изменить ширину столбца, перетащите правую границу его заголовка.

2. Чтобы открыть ячейку, ссылка на которую содержится в записи панели инструментов "Окно контрольного значения", дважды щелкните запись.

Примечание: Ячейки, содержащие внешние ссылки на другие книги, отображаются на панели инструментов "Окно контрольного значения" только в случае, если эти книги открыты.

Удаление ячеек из окна контрольного значения

1. Если окно контрольного значения не отображается, на вкладке Формула в группе Зависимости формул нажмите кнопку Окно контрольного значения.

2. Выделите ячейки, которые нужно удалить.

Чтобы выделить несколько ячеек, щелкните их, удерживая нажатой клавишу CTRL.

1. Нажмите кнопку Удалить контрольное значение.

2. Перемещение и копирование формулы

Важно помнить о возможностях изменения относительной ссылки на ячейку при переходе или копировании формулы.

• Перемещение формулы При этом ссылки на ячейки в формуле не изменяются независимо от типа ссылки на ячейку.

• Копирование формулы: При копировании формулы изменяются относительные ссылки на ячейки.

Перемещение формулы

1. Выделите ячейку с формулой, которую необходимо переместить.

2. В группе Буфер обмена на вкладке Главная нажмите кнопку Вырезать.

Формулы можно скопировать и путем перетаскивания границы выделенной ячейки в левую верхнюю ячейку области вставки. При этом будут заменяться все существующие данные.

1. Выполните одно из указанных ниже действий.

• Чтобы вировать формулу иформатирование: в группе Буфер обмена на вкладке Главная нажмите кнопку В виде вкладки.

• Чтобы вировать только формулу:в группе Буфер обмена на вкладке Главная нажмите кнопку В paste (Главная), выберите специальная ветвь ищелкните Формулы.

Копирование формулы

1. Вы выберите ячейку, содержащую формулу, которую нужно скопировать.

2. В группе Буфер обмена на вкладке Главная нажмите кнопку Копировать.

3. Выполните одно из указанных ниже действий.

• Чтобы вировать формулу и любое форматирование, в группе Буфер обмена на вкладке Главная нажмите кнопку В виде вкладки.

• Чтобы вировать только формулу, в группе Буфер обмена на вкладке Главная нажмите кнопку Вировать ,выберите специальная ветвь ,а затем щелкните Формулы.
   Примечание: В нее можно вклеить только результаты формулы. В группе Буфер обмена на вкладке Главная нажмите кнопку Вировать, выберите специальная ветвь ,а затем щелкните Значения.

1. Убедитесь, что ссылки на ячейки в формуле дают нужный результат. При необходимости переключить тип ссылки можно следующим образом:

1. Выделите ячейку с формулой.

2. В строке формул строка формул   выделите ссылку, которую нужно изменить.

3. Для переключения между сочетаниями нажмите F4.

В таблице подводится информация об обновлении типа ссылки при копировании формулы, содержащей ссылку, на две ячейки вниз и на две ячейки вправо.

Примечание: Формулы также можно копировать в смежные ячейки с помощью маркер заполнения  . После проверки на то, что ссылки на ячейки в формуле дают нужный результат в шаге 4, вы выберите ячейку со скопированной формулой и перетащите его по диапазону, который вы хотите заполнить.

Контрольные вопросы

1. Что такое функция? Что такое Мастер функций и какие способы его запуска вы знаете?

2. В какой последовательности выполняются операции в арифметическом выражении?

3. Как следует записывать аргумент тригонометрической функции, если он записан в градусах?

4. Как с помощью мыши упростить ручной набор формулы? Как увидеть формулу, записанную в ячейку? Как сделать так, чтобы в ячейке отображался не результат вычислений по формуле, а сама формула?

5. Как установить (изменить) точность отображения числа и результата вычислений?

6. Зачем копируются формулы? Опишите способы копирования формул.

ДОМАШНЕЕ ЗАДАНИЕ

1. Составить конспект лекции

2. Ответить письменно на контрольные вопросы

Перечень рекомендуемых учебных изданий, Интернет-ресурсов, дополнительной литературы.

Основные источники:

1. Гохберг, Г.С. Информационные технологии: учебник для студ. сред. проф. Образования / Гохберг, Г.С, Зафиевский, А.В., Короткин, А.А. - 5 -е изд., стер. – М. : Издательский центр «Академия», 2010. – 208 с.

2. Михеева, Е.В. Практикум по информационным технологиям в профессиональной деятельности: учебное пособие. - М. : Академия, 2008. - 8 – е изд. - 256 с.- (Среднее профессиональное образование).

3. Филимонова, Е.В. Информационные технологии в профессиональной деятельности : учебник. – Изд-е 2-е, доп. и перераб. – Ростов н/Д : Феникс, 2008. – 381. – (СПО).

Интернет - источники:

1. Электронный учебник по информатике и информационным технологиям - http://www.ctc.msiu.ru/

2. Тесты по информатике - http://www.ege.ru/

3. Каталог образовательных Интернет-ресурсов: учебно-методические пособия - www.edu/ru/modules.php

4. Дидактические материалы по информатике - http://comp-science.narod.ru/

5. Портал электронного обучения - http://www.e-learning.by/

6. Электронный учебник «Работа с MS Excel» - http://avanta.vvsu.ru/met_supply/381/Index.htm

7. Практикум по MS Excel - http://informatika.delayu.ru/Zadanija/Exceldannye

Преподаватель: Владимир Александрович Волков