Элементы тригонометрии и офисные информационные технологии. О функциях в формулах Установка ширины столбцов

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

Операторы

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

Типы операторов

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

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

Текстовый оператор конкатенации . Амперсанд (&) используется для объединения нескольких текстовых строк в одну строку.

Порядок выполнения действий в формулах

Формулы вычисляют значения в определенном порядке. Формула в Microsoft всегда начинается со знака равенства (=). Знак равенства свидетельствует о том, что последующие знаки составляют формулу. Элементы, следующие за знаком равенства, являются операндами, разделяемыми операторами вычислений. Формула вычисляется слева направо, в соответствии с определенным порядком для каждого оператора в формуле.

Приоритет оператора

Если в одной формуле используется несколько операторов, Microsoft выполняет операции в порядке, показанном в следующей таблице. Если формула содержит операторы с одинаковым приоритетом - например операторы деления и умножения - они выполняются слева направо.

спользование круглых скобок

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

5+2*3

Если же с помощью скобок изменить синтаксис, Microsoft сложит 5 и 2, а затем умножит результат на 3; результатом этих действий будет число 21.

5+2*3

В приведенном ниже примере скобки вокруг первой части формулы определяют следующий порядок вычислений: определяется значение B4+25, затем полученный результат делится на сумму значений в ячейках D5, E5 и F5.

=(B4+25)/СУММ(D5:F5)

О формулах

5+2*3

Элементы формулы

1 Функции. Функция () возвращает значение числа : 3,142...

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

4 Операторы. Оператор ^ возводит число в , а звездочка (*) выполняет умножение.

О функциях в формулах

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

Структура функции

1 Структура. Структура функции начинается со знака равенства (=), за ним следует имя функции, открывающая скобка, список аргументов, разделенных запятыми, закрывающая скобка.

2 мя функции. Для появления списка доступных функций щелкните ячейку и нажмите клавиши SHIFT+F3.

3 Аргументы. Существуют различные типы аргументов: число, текст, логическое значение ( и ), массивы, значение ошибки (например #Н/Д), или ссылки на ячейку. В качестве аргументов используются константы, формулы, или функции. В каждом конкретном случае необходимо использовать соответствующий тип аргумента.

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

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

Вложенные функции

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

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

Ограничение количества уровней вложения функций . В формулах можно использовать до семи уровней вложения функций. Когда функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, функции СРЗНА и СУММ считаются функциями второго уровня, потому что обе они являются аргументами функции . Функция, вложенная в качестве аргумента в функцию СРЗНА , будет функцией третьего уровня и так далее.

спользование ссылок

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

Стиль ссылок A1

По умолчанию Microsoft использует стиль ссылок A1, определяющий столбцы буквами (от A до IV, всего не более 256 столбцов), а строки номерами (от 1 до 65536). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, а следом номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

Обратите внимание на то, что имя листа и восклицательный знак (!) предшествуют ссылке на диапазон ячеек.

Различия между относительными и абсолютными ссылками

Относительные ссылки . Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейку, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании относительной ссылки из ячейки B2 в ячейку B3, она автоматически изменяется с =A1 на =A2.

Скопированная формула с относительной ссылкой

Абсолютные ссылки . Абсолютная ссылка ячейки в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, и для использования абсолютных ссылок надо выбрать соответствующий параметр. Например, при копировании абсолютной ссылки из ячейки B2 в ячейку B3, она остается прежней =$A$1.

Скопированная формула с абсолютной ссылкой

Смешанные ссылки . Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании смешанной ссылки из ячейки A2 в ячейку B3, она изменяется с =A$1 на =B$1.

Скопированная формула со смешанной ссылкой

Стиль трехмерных ссылок

Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

· Трехмерные ссылки могут быть использованы для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНА , СРЗНА А, С ЁТ, С ЁТЗ, МАКС, МАКСА, М Н, М НА, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, Д СП, Д СПА, Д СПР и Д СПРА.

Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. Предположим, что используется формула " =СУММ(Лист2:Лист6!A2:A5)", суммирующая содержимое ячеек с A2 по A5 с лист2 по лист6 включительно.

Вставка или копирование . Если между листом 2 и листом 6 книги вставить новые листы, Microsoft добавит в сумму содержимое ячеек с A2 по A5 на новых листах.

Удаление . Если между листом 2 и листом 6 книги удалить листы, Microsoft исключит из суммы содержимое ячеек удаленных листов.

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

Перемещение граничного листа . Если переместить лист 2 или лист 6 в новое место книги, Microsoft включит в сумму содержимое ячеек листов, находящихся между листом 2 и листом 6 включительно.

Удаление граничного листа . Если удалить лист 2 или лист 6, Microsoft включит в сумму содержимое ячеек листов, находившихся между ними.

Присвоение имени ячейкам на нескольких листах

1. В меню Вставка укажите на пункт мя и выберите команду Присвоить .

2. Введите имя в диалоговом окне Присвоить имя .

3. Если в поле Формула содержится ссылка, выделите ссылку вместе со знаком равенства (=) и нажмите клавишу BACKSPACE.

4. Введите знак равенства (= ) в поле Формула .

5. Выберите ярлычок первого листа, на который нужно сослаться.

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

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

1. Укажите ячейку, в которую следует ввести функцию.

2. Введите = (знак равенства), имя функции, а затем открывающую круглую скобку.

Функции, которые могут быть использованы в трехмерной ссылке

СУММ - складывает числа

СРЗНА - вычисляет среднее арифметическое чисел

СРЗНА А - вычисляет среднее арифметическое чисел, включая текстовые и логические значения

С ЕТ - подсчитывает количество ячеек, содержащих числа

С ЕТЗ - подсчитывает количество непустых ячеек

МАКС - находит наибольшее значение из набора значений

МАКСА - находит наибольшее значение из набора значений, включая логические значения и текстовые строки

М Н - находит наименьшее значение из набора значений

М НА - находит наименьшее значение из набора значений, включая логические значения и текстовые строки

Перемножает числа

СТАНДОТКЛОН - подсчитывает стандартное отклонение по выборке

СТАНДОТКЛОНА - подсчитывает стандартное отклонение по выборке, включая логические значения и текстовые строки

СТАНДОТКЛОНП - подсчитывает стандартное отклонение по генеральной совокупности

СТАНДОТКЛОНПА - подсчитывает стандартное отклонение по генеральной совокупности, включая логические значения и текстовые строки

Д СП - оценивает дисперсию по выборке

Д СПА - оценивает дисперсию по выборке, включая логические значения и текстовые строки

Д СПР - подсчитывает дисперсию для генеральной совокупности

Д СПРА - подсчитывает дисперсию для генеральной совокупности, включая логические значения и текстовые строки

3. Укажите ярлычок первого листа, на который нужно сослаться.

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

5. Укажите диапазон ячеек, на которые необходимо сослаться.

6. Завершите набор формулы и нажмите клавишу ENTER.

Стиль ссылок R1C1

Также можно использовать стиль ссылок, в котором нумеруются как строки, так и столбцы. Стиль ссылок R1C1 полезен при вычислении положения столбцов и строк в макросах . В стиле ссылок R1C1 Microsoft указывает положение ячейки буквой «R», за которой идет номер строки, и буквой «C», за которой идет номер столбца.

При записи макроса Microsoft записывает некоторые команды с использованием стиля ссылок R1C1. Например, если записывается такая команда как нажатие кнопки Автосумма для вставки формулы, суммирующей диапазон ячеек, Microsoft использует при записи формулы стиль ссылок R1C1, а не A1.

тобы включить или выключить стиль ссылок R1C1

1. Выберите пункт Параметры в меню Сервис и перейдите на вкладку Общие .

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

Копируемая формула

мена и заголовки

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

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

Определенное имя в формуле облегчает понимание назначения формулы. Например, формулу =СУММА(Продано_в_первом_квартале) легче опознать чем =СУММ(C20:C30).

Мена можно использовать в любом листе книге. Например, если имя «Контракты» ссылается на группу ячеек «A20:A30» первого листа рабочей книги, то это имя можно применить на любом другом листе той же рабочей книги для ссылки на эту группу.

Мя можно присвоить формуле или постоянному значению (константе). Например, имя «ПроцентнаяСтавка», которому присвоено значение 6,2 процента, можно использовать в любом месте для вычисления процентов.

Также можно ссылаться на определенное имя в другой книге, или определить имя, ссылающееся на ячейку в другой книге. Например, формула =СУММА(Продажи.xls!Контакты) ссылается на диапазон «Контракты» в книге «Продажи».

Примечание. По умолчанию имена являются абсолютными ссылками .

спользование существующих заголовков строк и столбцов в качестве имен

При создании формулы, ссылающейся на данные из листа, можно использовать заголовки строк и столбцов для указания данных. Например, для подсчета общей суммы для столбца « зделие» используется формула =СУММА( зделие).

Спользование заголовков

Если нужно сослаться на количество « зделия 3» в Восточном филиале (значение которого равно 110,00), можно использовать формулу = зделие 3 Восток. Пробел в формуле между « зделие 3» и «Восток» - это оператор пересечения диапазонов. Этот оператор предписывает Microsoft вернуть значение из ячейки, находящейся на пересечении строки, помеченной «Восток», и столбца, помеченного « зделие 3».

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

Многоуровневые заголовки . Если на листе используются заголовки столбцов и строк, эти заголовки можно использовать при создании формул, ссылающихся на данные листа. Если лист содержит многоуровневые заголовки столбцов - в которых за заголовком в одной ячейке ниже следуют один или более заголовков - можно использовать эти заголовки в формулах, ссылающихся на данные листа. Например, если в ячейке E5 находится заголовок «Запад», а в ячейке E6 - заголовок «Проект», то формула =СУММ(Запад;Проект) возвращает общее значение для столбца «Запад Проект».

Порядок заголовков . При использовании ссылок на данные с помощью многоуровневых заголовков вы ссылаетесь на данные в порядке, в котором отображаются заголовки, сверху вниз. Если заголовок «Запад» находится в ячейке F5, а заголовок «Фактически» - в ячейке F6, то можно для обращения к фактическим объемам продаж для запада использовать в формуле ссылку «Запад;Фактически». Например, чтобы вычислить среднее значение фактических объемов продаж для запада, следует использовать формулу =СРЗНА (Запад;Фактически).

спользование дат в качестве заголовков . Если с помощью диалогового окна Заголовки диапазонов создаются заголовки, содержащие год или дату, то при вводе формулы Microsoft определяет дату как заголовок путем заключения ее в одинарные кавычки. Например, пусть лист содержит два заголовка «2007» и «2008», определенных в диалоговом окне Заголовки диапазонов . При вводе формулы =СУММ(2008) год автоматически заключается в кавычки: =СУММ("1997").

Замена ссылок именами в ячейке или формуле

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

2. В меню Вставка укажите на пункт мя и выберите команду Применить .

3. Выберите необходимые имена в списке Применить имена .

зменение и удаление определенного ранее имени

Выполните одно из следующих действий.

• 1. Введите новое имя для использования в ссылках и нажмите кнопку Добавить .

     Выберите имеющееся имя и нажмите кнопку Удалить .

Удалите это имя

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

Присваивание имен константам и формулам

1. В меню Вставка укажите на пункт мя и выберите команду Присвоить .

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

3. В поле Формула введите знак равенства (= ) и формулу или константу.

1. В меню Вставка укажите на пункт мя и выберите команду Присвоить .

2. Выделите имя, для которого необходимо проверить ссылку, в списке мя .

Для выделенного имени в поле Формула отобразится ссылка, формула или константа.

Совет

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

Формулы представляют собой выражения, по которым выполняются вычисления на странице. Формула начинается со знака равенства (=). Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5.

5+2*3

Создание простой формулы: =128+345

Следующие формулы содержат операторы и константы.

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

Введите = (знак равенства).

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

Нажмите клавишу ENTER.

Создание формулы, содержащей ссылки или имена: =A1+23

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

Нажмите клавишу ENTER.

Создание формулы, содержащей функцию: =СРЕД Е(A1:B4)

Следующие формулы содержат функции.

Создание формулы с вложением функций: = (СРЕД Е(F2:F5)>50; СУММ(G2:G5);0)

Вложенные функции используют функции как один из аргументов другой функции. Следующая формула суммирует набор чисел (G2:G5), только если среднее значение другого набора чисел (F2:F5) больше 50. В противном случае она возвращает значение 0.

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

2. Для того чтобы начать формулу с функции, нажмите кнопку Вставка функции на панели формул .

3. Выберите функцию, которую нужно использовать. Можно ввести запрос с описанием операции, которую требуется выполнить (например, «сложение чисел» возвращение функции СУММ) в поле Поиск функции , или выбрать категорию в поле или категорию .

4. Введите аргументы .

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

o тобы ввести другую функцию в качестве аргумента, введите функцию в соответствующее окно аргумента. Например, можно добавить СУММ(G2:G5) в текстовое поле значение_если_истина .

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

Советы

Удаление формулы

2. Нажмите клавишу DEL.

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

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

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

нструкции

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

3. Нажмите клавишу F4, чтобы преобразовать тип ссылки. В столбце «Новая ссылка» отображается способ изменения типа ссылки при копировании формулы, содержащей эту ссылку, на две ячейки вниз или на две ячейки вправо.

Копируемая формула

3. В меню Правка выберите команду Копировать .

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

5. тобы скопировать формулу и форматирование, выберите в меню Правка команду Вставить .

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

Советы

Выделение ячеек, содержащих формулы

1. Выполните одно из следующих действий.

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

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

2. В меню Правка , выберите команду Перейти .

3. Нажмите кнопку Выделить .

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

Формулы массивов

Формулы массива и константы массива

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Столбцы разделяются точкой с запятой (;). Например, чтобы представить значения 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}.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3. змените формулу массива.

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

Выделение диапазона ячеек, содержащих формулу массива

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

2. В меню Правка , выберите команду Перейти .

3. Нажмите кнопку Выделить .

4. Установите флажок Текущий массив .

| §3.2 Организация вычислений в электронных таблицах

Уроки 19 - 20
§3.2 Организация вычислений в электронных таблицах
Относительные, абсолютные и смешанные ссылки
Встроенные функции. Логические функции

Ключевые слова:

Основным назначением электронных таблиц является организация всевозможных вычислений. Вы уже знаете, что:

вычисление - это процесс расчёта по формулам;
формула начинается со знака равенства и может включать в себя знаки операций, числа, ссылки и встроенные функции.

3.2.1. Относительные, абсолютные и смешанные ссылки

Использовать в одной формуле данные, находящиеся в разных частях электронной таблицы;
использовать в нескольких формулах значение одной ячейки.

Различают два основных типа ссылок:

1) относительные - зависящие от положения формулы;
2) абсолютные - не зависящие от положения формулы.

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

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

Рассмотрим формулу =А1ˆ2, записанную в ячейке А2. Она содержит относительную ссылку А1, которая воспринимается табличным процессором следующим образом: содержимое ячейки, находящееся на одну строку выше той, в которой находится формула, следует возвести в квадрат.

При копировании формулы вдоль столбца и вдоль строки относительная ссылка автоматически корректируется так:

Смещение на один столбец приводит к изменению в ссылке одной буквы в имени столбца;
смещение на одну строку приводит к изменению в ссылке номера строки на единицу.

Например, при копировании формулы из ячейки А2 в ячейки В2, С2 и D2 относительная ссылка автоматически изменяется и рассмотренная выше формула приобретает вид: =В1ˆ2, =С1ˆ2, =D1ˆ2. При копировании этой же формулы в ячейки АЗ и А4 получим, соответственно, =А2ˆ2, =А3ˆ2 (рис. 3.4).

Рис. 3.4. Скопированная формула с относительной ссылкой

Пример 1 . В 8 классе мы рассматривали задачу о численности населения некоторого города, ежегодно увеличивающейся на 5%. Проведём в электронных таблицах расчёт предполагаемой численности населения города в ближайшие 5 лет, если в текущем году она составляет 40 ООО человек.

Внесём в таблицу исходные данные, в ячейку ВЗ введём формулу =В2+0,05*В2 с относительными ссылками; скопируем формулу из ячейки ВЗ в диапазон ячеек В4:В7 (рис. 3.5).

Рис. 3.5. Вид таблицы расчёта численности населения в режиме отображения формул и режиме отображения значений

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

Абсолютные ссылки . Абсолютная ссылка в формуле всегда ссылается на ячейку, расположенную в определённом (фиксированном) месте. В абсолютной ссылке перед каждой буквой и цифрой помещается знак $, например $А$1. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется (рис. 3.6).

Рис. 3.6. Скопированная формула с абсолютной ссылкой

Пример 2 . Некий гражданин открывает в банке счёт на сумму 10 ООО рублей. Ему сообщили, что каждый месяц сумма вклада будет увеличиваться на 1,2%. Для того чтобы узнать возможную сумму и приращение суммы вклада через 1, 2,..., 6 месяцев, гражданин провёл следующие расчёты (рис. 3.7).

Рис. 3.7. Расчёт приращения суммы вклада

Прокомментируйте формулы в таблице на рис. 3.7.

Выполните аналогичные расчёты для начального вклада, равного 15 ООО рублям.

Смешанные ссылки . Смешанная ссылка содержит либо абсолютно адресуемый столбец и относительно адресуемую строку ($А1), либо относительно адресуемый столбец и абсолютно адресуемую строку (А$1). При изменении позиции ячейки, содержащей формулу, относительная часть адреса изменяется, а абсолютная часть адреса не изменяется.

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

Рис. 3.8. Скопированная формула со смешанной ссылкой

Чтобы преобразовать ссылку из относительной в абсолютную и наоборот, можно выделить её в строке ввода и нажать клавишу F4 (Microsoft Office Excel) или комбинацию клавиш Shift+F4 (OpenOffice Calc). Если выделить относительную ссылку, такую как А1, то при первом нажатии этой клавиши (комбинации клавиш) и для строки, и для столбца установятся абсолютные ссылки ($А$1). При втором нажатии абсолютную ссылку получит только строка (А$1). При третьем нажатии абсолютную ссылку получит только столбец ($А1). Если нажать клавишу F4 (комбинацию клавиш Shift+F4) ещё раз, то для столбца и строки снова установятся относительные ссылки (А1).

Пример 3 . Требуется составить таблицу сложения чисел первого десятка, т. е. заполнить таблицу следующего вида:

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

Внесите в ячейку В2 формулу =$А2+В$1 и скопируйте её на весь диапазон B2:J10. У вас должна получиться таблица сложения, знакомая каждому первокласснику.

3.2.2. Встроенные функции

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

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

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

СУММ (SUM) - суммирование аргументов;

МИН (MIN) - определение наименьшего значения из списка аргументов;

МАКС (МАХ) - определение наибольшего значения из списка аргументов.

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

Пример 4 . Правила судейства в международных соревнованиях по одному из видов спорта таковы:

1) выступление каждого спортсмена оценивают N судей;
2) максимальная и минимальная оценки (по одной, если их несколько) каждого спортсмена отбрасываются;
3) в зачёт спортсмену идёт среднее арифметическое оставшихся оценок.

Информация о соревнованиях представлена в электронной таблице:

1) в ячейки А10, All, А12 и А14 заносим тексты «Максимальная оценка», «Минимальная оценка», «Итоговая оценка», «Оценка победителя»;
2) в ячейку В10 заносим формулу =МАКС(ВЗ:В8); копируем содержимое ячейки В10 в ячейки C10:F10;
3) в ячейку В11 заносим формулу =МИН(ВЗ:В8); копируем содержимое ячейки В10 в ячейки C11:F11;
4) в ячейку В12 заносим формулу =(СУММ(ВЗ:В8)-В10-В11)/4; копируем содержимое ячейки В12 в ячейки C12:F12;
5) в ячейку В14 заносим формулу =MAKC(B12:F12).

Результат решения задачи:

3.2.3. Логические функции

При изучении предшествующего материала вы неоднократно встречались с логическими операциями НЕ, И, ИЛИ (not , and , or ). Построенные с их помощью логические выражения вы использовали при организации поиска в базах данных, при программировании различных вычислительных процессов.

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

Например, логическое выражение, соответствующее двойному неравенству 0<А1<10, в электронных таблицах будет записано как И(А1>0;А1<10) (AND(A1>0;A1<10)).

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

Пример 5. Вычислим в электронных таблицах значения логического выражения НЕ А И НЕ В при всех возможных значениях входящих в него логических переменных.

При решении этой задачи мы следовали известному вам алгоритму построения таблицы истинности для логического выражения. Вычисления в диапазонах ячеек С3:С6, D3:D6, Е3:Е6 проводятся компьютером по заданным нами формулам.

Для проверки условий при выполнении расчётов в электронных таблицах реализована логическая функция ЕСЛИ (IF), называемая условной функцией .

Условная функция имеет следующую структуру:

ЕСЛИ (<условие>; <действие1>; <действие2)

Здесь <условие> - логическое выражение, т. е. любое выражение, построенное с помощью операций отношения и логических операций, принимающее значение ИСТИНА или ЛОЖЬ .

Если логическое выражение истинно, то значение ячейки, в которую записана условная функция, определяет <действие1>, если ложно - <действие2> 1) .

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

Что вам напоминает структура условной функции?

Пример 6. Рассмотрим задачу о приёме в школьную баскетбольную команду: ученик может быть принят в эту команду, если его рост не менее 170 см.

Данные о претендентах (фамилия, рост) представлены в электронной таблице.

Использование условной функции в диапазоне ячеек С3:С8 позволяет вынести решение (принят/не принят) по каждому претенденту.

Функция СЧЁТЕСЛИ (COUNTIF) позволяет подсчитать количество ячеек в диапазоне, удовлетворяющих заданному условию. С помощью этой функции в ячейке С9 подсчитывается число претендентов, прошедших отбор в команду.

Функция СУММЕСЛИ (SUMMIF) суммирует значения диапазона, удовлетворяющие заданному условию.

В Единой коллекции цифровых образовательных ресурсов размещён «Интерактивный задачник. Раздел "Логические формулы в электронных таблицах"» (119424). Попытайтесь самостоятельно выполнить имеющиеся в нём задания в режимах «Тренировка» и «Зачёт».

САМОЕ ГЛАВНОЕ

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

Различают относительные, абсолютные и смешанные ссылки .

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

Функции - это заранее определённые и встроенные в электронные таблицы формулы. Использование функций позволяет упростить формулы и сделать процесс вычислений более понятным.

Вопросы и задания

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

2. Назовите основные типы ссылок.

3. Охарактеризуйте относительный тип ссылок.

4. По данным электронной таблицы определите значение в ячейке С1.

5. Дан фрагмент электронной таблицы:

6. Дан фрагмент электронной таблицы:

Определите значения в ячейках диапазона D1:D3 после копирования в них формулы из ячейки СЗ. Проверьте свои предположения на компьютере.

7. Охарактеризуйте абсолютный тип ссылок.

8. Дан фрагмент электронной таблицы:

Определите значения в ячейках С2 и СЗ после копирования в них формулы из ячейки С1. Проверьте свои предположения на компьютере.

9. Охарактеризуйте смешанный тип ссылок.

10. Дан фрагмент электронной таблицы:

Определите значения в ячейках диапазона C1:D3 после копирования в них формулы из ячейки С1. Проверьте свои предположения на компьютере.

12. О чём идёт речь в следующем высказывании: «Знак доллара "замораживает" как весь адрес, так и его отдельную часть»? Дайте развёрнутый комментарий к высказыванию, основываясь на материале параграфа. Обсудите этот вопрос в группе.

13. Для чего нужны встроенные функции?

15. Дан фрагмент электронной таблицы:

Определите значение в ячейке D3.

16. Какая из формул не содержит ошибок?

а) =ЕСЛИ ((С4>4) И (С5>4));"Принят!";"Не принят")
б) =ЕСЛИ (M(D2=0;B2/4);D3-A1;D3+A1)
в) =ЕСЛИ ((А4=0 И D1<0);1;0)
г) =ЕСЛИ (ИЛИ(А2>10;С2>10);1;"ура!")

17. В ячейке А5 электронной таблицы находится суммарная стоимость товаров, заказанных Иваном А. в интернет-магазине. Формула, позволяющая подсчитать полную стоимость заказа, с учётом стоимости его доставки, имеет вид:

ЕСЛИ(А5>=2000;А5;А5+150).

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

18. Оплата за аренду конференц-зала вычисляется по следующим правилам: каждый из первых четырёх часов аренды стоит 1000 рублей, каждый последующий час - 750 рублей. В ячейке В8 электронной таблицы находится количество полных часов аренды зала. Какая из формул позволяет подсчитать полную стоимость аренды зала?

а) =ЕСЛИ(В8<=4;В8*1000;4000+В8*750)
б) =ЕСЛИ(В8<=4;В8*1000;В8*1000+(В8-4)*750)
в) =ЕСЛИ(В8<=4;В8*1000;(В8+(В8-4)*750)
г) =ЕСЛИ(В8<=4;В8*1000;4000 +(В8-4)*750)

Лабораторная работа №6

Формат ячеек, вычисления в таблицах. Относительная и абсолютная адресация ячеек

Ввод и редактирование данных:

Ввести в ячейки текст, число. Обратить внимание на представление данных в ячейках, строке формул. Выполнить редактирование данных можно двумя способами: в ячейке (дв.щелчок) и в строке формул.

Установка ширины столбцов:

Установить указатель мыши на границу столбцов/ перетаскивание мышью.

Для установки ширины столбцов равной 2 символам необходимо: Выделить столбец/ Контекстное Меню / Ширина столбца. Повторить изменение ширины столбцов можно с помощью команды меню Главная/Формат

Использование автозаполнения:

Ввести в ячейку A1 значение 1.

Ввести в ячейку A2 значение 2.

Выделить две ячейки A1 и A2. Установить указатель мыши на маркер автозаполнения и перетащить маркер автозаполнения вниз.

Ввод формул для ячеек смежного диапазона:

Пример, ввести формулу =срзнач(C4:F4) в ячейку G4:

1. ввести =срзнач(в ячейку G4;

2. выделить указанный диапазон C4:F4 ячеек в таблице.

3. набрать).

Ввод формул для ячеек несмежного диапазона:

Пример, ввести в ячейку W4 формулу для подсчета среднего балла за год:

1. Ввести =срзнач(в ячейку W4.

2. Выделить ячейки G4,L4,R4,V4, удерживая клавишу CTRL.

3. Набрать)

Копирование формул на смежные ячейки:

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

Копирование формул на несмежные ячейки:

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

Защита данных листа с предварительным указанием диапазонов, не подлежащих блокировке:

Указать диапазоны, не подлежащие защите: Выделить диапазон/ Контекстное Меню / Формат ячеек /Защита/ снять флажок защиты.

Защитить весь лист: Рецензирование /группа Изменения / Защитить лист.

Копирование листов:

Пример, установить указатель мыши на ярлык Лист2 / Перетаскивание на ярлык Лист3 (при нажатой CTRL)

Изменение имени листа в соответствии с предметами: установить указатель мыши на ярлыке листа/ Контекстное Меню / Переименовать

Задание 1

Подготовить электронный классный журнал, включающий сведения по одному предмету. Предусмотреть 5 оценок в четверти, средние баллы за 4 четверти и за год для 10 учеников, средний балл класса за четверти и за год (см. рисунок ниже), при этом:

расчет средних баллов за четверти и за год осуществлять строго по формулам!

ввод оценок осуществлять различными способами:

· путем непосредственного ввода чисел в ячейку.

· с помощью функции СЛЧИС()

ОКРУГЛ(СЛЧИС() *3+2;0)

защитить весь лист от редактирования, оставив диапазоны оценок незащищенными.

Задание 2

Заполнить классный журнал по трем предметам, выполнив копирование таблицы на Лист2, Лист3. Изменить имена листов в соответствии с предметами.

Задание 3

Для электронного классного журнала получить итоговую таблицу по предметам для класса. Таблица должна содержать информацию о средних баллах класса за четверти, за год по всем предметам. Таблицу разместить на Листе 5.

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

Относительные ссылки

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

Абсолютные ссылки

Абсолютная ссылка – это неизменная ссылка в формуле на ячейку, расположенную в определенном месте. При перемещении ячейки с формулой адрес ячейки с абсолютной ссылкой не корректируется. Абсолютная ссылка указывается символом $.
Например, абсолютная ссылка на ячейку $A$1 указывает на неизменность адреса ячейки А1 при копировании формулы вдоль столбца или строки.

Смешанные ссылки

Смешанная ссылка – это ссылка с использованием либо абсолютной ссылки на столбец и относительной – на строку ($A1 ), либо абсолютной ссылки на строку и относительной – на столбец (A$1 ).
При этом при изменении позиции ячейки с формулой относительная ссылка строки или столбца изменяется, а абсолютная часть ссылки остается прежней.

Стиль трехмерных ссылок

Трехмерные ссылки – это ссылки на одну и ту же ячейку или диапазон ячеек, расположенные на нескольких листах одной книги. При этом трехмерная ссылка включает в себя имя листа.
Например, трехмерная ссылка Лист1:Лист5!А1 указывает на все ячейки А1, расположенные с Листа1 по Лист5.
Трехмерные ссылки нельзя использовать в формулах массива (см. далее пункт «Как создать формулу массива?»), а также сочетать с оператором.
При добавлении или удалении листов, попадающих в диапазон листов трехмерной ссылки, автоматически происходит учет всех изменений. То есть новые данные, расположенные на ячейках вставляемых или удаляемых листов, прибавляются или вычитаются.