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

1.Правила записи формул.

2.Операторы,их приоритет.

3.Использование функций в формулах.

4.Работа с Мастером функций.

5.Ошибки в формулах.

6.Трассировка ошибок.

7.Автоматизация вычислений.

8.Формулы массивов, особенности их использования.

Ответы на контрольные опросы:

1.

Написание формулы в MSExcel подчиняется определенному синтаксису:

1. Формула начинается со знака =, за которым следуют вычисляемые элементы (операнды), разде­ленные операторами.

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

3. Пробелы в формуле недопустимы.

4. Для указания ссылок на ячейки и диапазоны следует использовать только латинские буквы.

2.

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

Текстовые операции выполняются над данными текстового или числового типа. Результат этой операции всегда имеет текстовый тип данных.

Операторы сравнения (или логические) используются для обозначения операций сравне­ния двух чисел. Результат любой логической операции (операции сравнения) всегда имеет логи­ческий тип (ИСТИНА или ЛОЖЬ).

Адресные операторы используются для группировки ячеек и диапазонов при вычислени­ях. Результатом адресной операции всегда является диапазон ячеек.

Порядок вычисления в выражениях определяется приоритетом операций.По умолчанию, MicrosoftExcel вычисляет формулу слева направо, начиная вычисление со знака равенства (=). Операции с одинаковым приоритетом выполняются слева направо. Используя синтаксис написания формулы, можно управлять процессом вычисления. Приоритет операций изменяется путем использования круглых скобок. При этом операция, указанная в скобках, будет выполняться в первую очередь. При написании формул необходимо соблюдать баланс скобок, т.е. количество открывающих скобок должно быть равно количеству закрывающих.

3.

Вставить функцию в формулу можно так: установить курсор в точку ввода и выполнить

Лента | Формулы| Вставить функцию или кнопку fxв строке формул либо Shift+ F3, либо

в поле имени строки формул выбрать имя функции из списка последних использовавшихся функ­ций либо пункт Другие функции... в этом списке для вызова диалогового окна Мастера функ­ций.

4.Мастер функций - специальное средство MSExcel, которое в режиме диалога позволяет неподготовленному пользователю определить функцию и ее аргументы.

В первом окне Мастера функций «Мастер функций - шаг 1 из 2» содержатся три списка: Поиск функции, Категория и Функция. Причем Excel автоматически предлагает кате­горию функций «10 недавно использовавшихся», а в списке Функция отображает функции, к которым пользователь чаще всего обращается.

Пользователь выбирает категорию в поле «Категория:» и имя функции из списка «Выбе­рите функцию:». В нижней части окна отображается формат выбранной функции и краткое опи­сание назначения этой функции. Переход к следующему шагу диалога Мастера функций щелчком по кнопке ОК, отмена операций кнопка «Отмена».

Во втором окне Мастера функций «Аргументы функции» введите аргументы функции. Аргументы функции можно задать как с клавиатуры, так и методом непосредственного указания. Для этого щелкнуть по кнопке, расположенной на правом крае поля аргумента, выде­лить ячейку или диапазон, повторно щелкнуть по изменившей свой вид кнопке. При этом около поля аргумента отобразиться текущее значение этого аргумента.

Перемещение по полям аргу­ментов в диалоговом окне выполняется клавишей Tab. После ввода аргументов функции их зна­чение отражается справа от поля ввода аргумента.Аргументы функции

Под полями ввода приведено краткое описание функции. В нижней части окна диалога дано также объяснение аргумента функции, в поле которого расположены курсор ввода, а также указано является ли аргумент обязательным или нет. Обязательные аргументы функции выделе­ны в описании полужирным шрифтом, необязательные - обычным. В нижней части окна диалога отображен текущий результат вычисления функции (с учетом заданных аргументов). Щелкнув по кнопке с ? можно вызвать на экран справочную информацию по текущей функции.

Вставить имя ячейки, диапазона, значения или формулу можно так: поместить курсор в точку ввода, активизировать лист, содержащий именованную ссылку, Лента | Формулы | Ис­пользовать в формуле | выбрать из списка либо Вставить имена... | в окне Вставка имени выбрать из списка | ОК | закончить формулу.

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

Сообщение об ошибке всегда начинается со знака «#».

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

6.Процесс трассировки ошибок представляет собой поиск ошибок, а также систему мер направленную на предупреждение появления ошибок.

1)способ (поиск источника ошибки).

Выделить ячейку, содержащую ошибку. Лента| Формулы, в разделе Зависимостиформулвыбрать пункт Проверканаличияошибок. На экран выводится диалоговое окно «Контрольошибок», в котором указана ячейка, содержащая ошибку, краткое объяс­нение ошибки. Нажать кнопку «Продолжить» для продолжения поиска ошибок.

Кнопка «Показать вычисления» открывает следующее диалоговое окно «Вычисление формулы» , в котором можно найти ошибку, вычисляя формулу последовательно по шагам. Проигнорировать ошибку можно нажав кнопку «Пропустить ошибку». Для исправления ошибки в формуле щелкнуть по кнопке «Изменить в строке формул».

Для анализа источников ошибок в формулах полезно просмотреть связи между активной ячейкой (с формулой) и связанными ячейками (с данными). Она представлена на экране стрел­ками слежения . Ячейка, в которой записана формула, называется зависимой ячейкой, так как она зависит от ячеек с данными, на которые ссылается. Ячейки с данными, над которыми производятся вычисления, называются влияющими, так как они оказывают влияние на результат вычисления в формуле. Стрелка слежения всегда начинается во влияющих ячейках и заканчивает­ся в зависимой ячейке. Цвет стрелки слежения - красный для ошибок #ЗНАЧ! и #ДЕЛ/0!, для остальных ошибок - синий.

Поиск ошибок в формуле следует начинать с выделения ячеек, содержащих ошибку, Лен­та | Формулы, в разделе Зависимости формул выбрать пункт Проверка наличия ошибок, выбрать Поиск ошибки.

Двойной щелчок по одному из концов стрелки позволяет быстро перейти в ячейку на ее противоположном конце. Если влияющая ячейка расположена на другом листе, стрелка будет иметь на конце кнопку в виде таблицы. Двойной щелчок по стрелке вызывает диалоговое окно перехода в котором, из списка, выбрать соответствующюю ссылку на результирующую ячейку, ОК.

Команды работы со стрелками слежения расположены на ленте на вкладке Формулы, в разделе Зависимости формул. Кнопка Влияющие ячейки отобразит стрелки слежения к ячейкам с ссылками на данные для формулы в выделенной ячейке. Кнопка Убрать стрелки к влияющим ячейкам выключит эти стрелки. Кнопка Зависимые ячейки отобразит стрелки слежения к зависимым ячейкам, то есть к ячейкам с формулами, использующими данные из выделенной ячейки. Кнопка Убрать стрелки к зависимым ячейкам выключит эти стрелки. Кнопка Убрать все стрелки отключит стрелки всех типов.

2)способ (поиск ячеек, содержащих значение ошибки).

Если на рабочем листе много формул, часто трудно отследить все ошибки.

Для поиска ячеек, содержащих формулы следует выделить диапазон поиска, выбрать вкладку Главная на ленте, выполнить команду Найти и выделить, выбрать пункт Формулы. Все ячейки, содержащие формулы, будут выделены на рабочем листе.

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

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

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

3)способ (вычисление значений Формулы по частям).

Для поиска ошибок можно производить вычисления значений формул по частям: выде­

лить ячейку с формулой, перейти в режим редактирования, выделить часть формулы, F9, выде­ленная часть формулы замениться рассчитанным значением. Для возврата к первоначальному виду нажать кнопку F9, либо кнопку Отмена в строке формул.

Если формула большая, целесообразно вычислить значение формулы по частям в окне «Вычисление формулы». Для этого выделить ячейку с ошибкой. На ленте выбрать вкладку Формулы, в разделе За­висимости формул вы­брать пункт Вычислить формулу.

4)способ (отслежи­вание контрольного зна­чения).

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

Команда Добавить контрольное значение в меню окна Окно контрольно­го значения выводит на экран окно Добавление контрольного значе­ния ,в котором для вывода на экран, следует указать ячейки, значения которых будут отслеживаться.

5)способ (проверка вводимых в ячейку данных).

Для предотвращения ввода некорректных данных, либо сообщение о получении некор­ректных результатов в ходе вы­числений можно наложить условия на: тип вводимых дан­ных и на значение вводимых данных. Выделить диапазон, для которого будут заданы ограни­чения. На ленте выбрать вкладку Данные, раздел Работа с дан­ными, команда Проверка данных... . В диалоговом окне «Проверка вводимых значе­ний » на вкладке Па­раметры задать условия про­верки: в поле Тип данных: вы­брать требуемый тип, например, целое число. В поле Значение выбрать операцию (например, меоду). В зависимости от типа данных и значения, выбранных пользователем, будут выведены

дополнительные поля (например, минимум и максимум).

На вкладке «Сообщение для ввода», ввести текст сообщения, которое будет появляться в выплывающей подсказке при указании на любую ячейку из диапазона с условиями проверки значений.

На вкладке «Сообщение об ошибке» определить - выводить ли диалоговое окно с со­общением при появлении некорректных данных? Для этого установить флажок Выводить со­общение об ошибке. В поле «Вид» выбрать один из следующих вариантов реакции пользова­теля в ответ на сообщение о вводе неверных данных в ячейку:

• Останов - повторно ввести данные, исправив ошибку (кнопка Повторить) либо отменить ввод (кнопка Отмена).

• Предупреждение - согласиться с неверными данными и продолжить работу (кнопка Да), исправить ошибку (кнопка Нет), отменить операцию ввода (кнопка Отмена).

• Сообщение - согласится с неверными данными (кнопка ОК) или отменить опера­цию ввода (кнопка Отмена). Если были заданы ограничения на значения, как опи­сано выше кнопка «Обвести неверные данные» на ленте на вкладке Данные, раздел Работа с данными, команда Проверка данных... позволяет выделить красными кружками ячейки с некорректными данными. Снять это выделение можно кнопкой «Удалить обводку неверных данных».

7.Кнопка Автосумма (AutoSum) - ∑ может использоваться для автоматического создания формулы, которая суммирует область соседних ячеек, находящихся непосредственно слева в данной строке и непосредственно выше в данном столбце.

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

2. Щелкните кнопку Автосумма - ∑ или нажмите комбинацию клавиш Alt += . Excel примет решение, какую область включить в диапазон суммирования, и выделит ее пунктирной движущейся рамкой, называемой границей.

3. Нажмите Enter для принятия области, которую выбрала программа Excel, или выберите с помощью мыши новую область и затем нажмите Enter.

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

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

Именно поэтому начиная с версии Excel 7.0 в электронную таблицу была встроена функция автовычисления. Теперь в электронных таблицах Excel имеется возможность быстрого выполнения некоторых математических операций в автоматическом режиме.

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

8.)Массивы в Excel используют для создания формул, которые возвращают некоторое множество результатов или оперируют множеством значений.

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

2. Нажмите клавиши Ctrl+Shift+Enter для фиксации ввода формулы массива. При этом Excel заключит формулу в фигурные скобки в строке формул. НЕ ВВОДИТЕ ФИГУРНЫЕ СКОБКИ ВРУЧНУЮ!

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

4. Для изменения или очистки массива надо выделить весь массив и активизировать строку формул. После изменения формулы надо нажать комбинацию клавиш Ctrl+Shift+Enter.

5. Чтобы переместить содержимое диапазона массива, надо выделить весь массив и в меню "Правка" выбрать команду "Вырезать". Затем выделите новый диапазон и в меню "Правка" выберите команду "Вставить".

6. Вырезать, очищать или редактировать часть массива не разрешается, но можно назначать разные форматы отдельным ячейкам в массиве.

Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Формула массива обрабатывает несколько наборов значений, называемых аргументами массива. Каждый аргумент массива должен включать одинаковое число строк и столбцов. Формула массива создается так же, как и другие формулы, с той разницей, что для ввода такой формулы используются клавиши CTRL+SHIFT+ENTER.

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

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

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

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

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

Формула массива, вычисляющая одно значение

При вводе формулы ={СУММ(B2:D2*B3:D3)} в качестве формулы массива она умножает «Акции» и «Цена» для каждой биржи, после чего складывает результаты этих вычислений друг с другом.

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

Например, по заданному ряду из трех значений продаж (в столбце B) и ряду из трех месяцев (в столбце A) функция ТЕНДЕНЦИЯ определяет продолжение линейного ряда объемов продаж. Для отображения всех вычисляемых значений формула введена в три ячейки столбца C (C1:C3).

Формула массива, вычисляющая несколько значений

Формула =ТЕНДЕНЦИЯ(B1:B3;A1:A3), введенная как формула массива, возвращает три значения (22196, 17079 и 11962), вычисленные по трем объемам продаж за три месяца.

Использование констант массива

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

Константы массива могут содержать числа, текст, логические значения, например ИСТИНА или ЛОЖЬ, или значения ошибки, такие как #Н/Д. Различные типы значений могут быть в одной константе массива, например {1;3;4:ИСТИНА;ЛОЖЬ;ИСТИНА}. Числа в массиве могут быть целыми, с десятичной точкой или в экспоненциальном формате. Текст должен быть взят в двойные кавычки, например "Вторник".

Константы массива не могут содержать ссылок на ячейку, столбцов или строк разной длины, формул или специальных символов $ (знак доллара), скобок или % (знак процента).

Формат констант массива

Константы массива заключены в фигурные скобки ( { } ).

Столбцы разделяются точкой с запятой (;). Например, чтобы представить значения 10, 20, 30 и 40, введите {10;20;30;40}. Такой массив констант является матрицей размерности 1 на 4 и соответствует ссылке на 1 строку и 4 столбца.

Строки разделяются двоеточиями (:). Например, чтобы представить значения 10, 20, 30, 40 и 50, 60, 70, 80, находящиеся в расположенных друг под другом ячейках, можно создать массив констант размерностью 2 на 4, причем строки будут отделены друг от друга двоеточиями, а значения в столбцах — точкой с запятой: {10;20;30;40:50;60;70;80}.

Создание формулы массива

При вводе формулы массива MicrosoftExcel автоматически заключает ее в фигурные скобки ( { } ).

Вычисление одного значения

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

1. Щелкните ячейку, в которую требуется ввести формулу массива.

2. Введите формулу.

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

Формула массива, вычисляющая одно значение

При вводе формулы ={СУММ(B2:D2*B3:D3)} в качестве формулы массива, она умножает «Акции» и «Цена» для каждой биржи, после чего складывает результаты этих вычислений друг с другом.

3. Нажмите сочетание клавиш CTRL+SHIFT+ENTER.

Вычисление нескольких значений

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

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

2. Введите формулу.

Например, по заданному ряду из трех значений продаж (столбец B) и ряду из трех месяцев (столбец A) функция ТЕНДЕНЦИЯ определяет продолжение линейного ряда объемов продаж. Для отображения всех вычисляемых значений формула введена в три ячейки столбца C (C1:C3).

Формула массива, вычисляющая несколько значений

Формула =ТЕНДЕНЦИЯ(B1:B3;A1:A3) введенная как формула массива, возвращает три значения (22196, 17079 и 11962), вычисленные по трем объемам продаж за три месяца.

3. Нажмите сочетание клавиш CTRL+SHIFT+ENTER.