Работа с формулами и встроенными функциями. Форматирование в Excel.Ввод параметров форматирования

Представление «1» в различных форматах

Формат	Представление
Общий
Числовой	1,00
Денежный	1,00р
Финансовый	1,00р
Процент	100%
Экспоненциальный	1,00Е+00
Текстовый

Структура или порядок элементов в формуле определяет конечный результат вычислений. Формулы в Excel подчиняются определенному синтаксису, или порядку, согласно которому в формулу включается знак равенства (=), за которым идут вычисляемые элементы (операнды), разделенные операторами. В качестве операндов могут выступать постоянные значения (константы), ссылки на ячейки или диапазоны ячеек, заголовки, имена или функции.

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

Параметры форматирования вводятся в пустые выделенные ячейки перед их заполнением. В Excel 2003 это осуществляется с помощью меню «Формат», пункт «Ячейки», в Excel 2007 – вкладка «Главная», пункт «Ячейки».

Форматирование в Excel может выполняться как над отдельной ячейкой, так и над диапазоном ячеек, например, строка (С4:Н4), столбец (С4:С12) или блок (С4:Н15).

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

· группа смежных (соседних) ячеек — переместить указатель мыши от левой верхней ячейки выделяемой группы до правой нижней при нажатой левой кнопке или выделение левой верхней ячейки и щелчок по правой нижней ячейке при нажатой клавише Shift;

· группа несмежных ячеек — выделить первую из требуемых ячеек и далее последовательно все оставшиеся выделять щелчком по левой клавише мыши при нажатой клавише Ctrl;

· строка — щелчок по номеру строки;

· столбец —щелчок по названию столбца;

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

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

В Excel действия выполняются слева направо — с учетом приоритета операторов, начиная от знака равенства (=). Порядком вычисления можно управлять с помощью скобок, группируя действия, которые должны выполняться в первую очередь. Например, результатом следующей формулы

=4+2*3

будет число 10, поскольку Excel выполняет умножение до сложения. В данной формуле число 2 умножается на 3, а затем к результату добавляется число 4.

Если же с помощью скобок изменить синтаксис,

=(4+2)*3

Excel сложит 4 и 2, а затем умножит полученную сумму на 3; результатом этих действий будет число 18.

Полная реализация свойств Excel возможна, если в активной ячейке при вводе формулы использовать не числовые данные, а ссылки на ячейки, где эти данные расположены. Например, пусть в ячейке А1 содержится число 4, в ячейке В1 – число 2, в ячейке С4 – число 3 (эти ячейки являются исходными). Пусть ячейкой ввода будет Е5 (зависимая ячейка). Вышеприведенная формула (=4+2*3) примет вид

= А1+В1*С4

После завершения ввода (кнопка ВВОД – зеленая «птичка» на строке формул) в ячейке Е5 появится результат вычисления приведенной формулы (10). Если число, хранящееся в ячейке А1, например, заменить на 10. то наличие формулы в ячейке Е5 автоматически изменит ее значение на 16.

Ссылки A1, B1 и С4 в формуле можно делать, непосредственно вводя с клавиатуры текст формулы, однако, чтобы избежать ошибок, следует после знака равенства сделать щелчок по ячейке А1 (в формуле появится ссылка A1), набрать знак +, сделать щелчок по В1, ввести * и сделать щелчок по ячейке С4.

tudopedia.net/11_30397_obshchie-printsipi-raboti-s-formulami.html

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

Если активизировать ячейку Е5 из предыдущего примера и протянуть ее за маркер заполнения (МЗ) вниз по столбцу, то:

· в ячейку Е6 автоматически введется формула =А2+В2*С5,

· в ячейку Е7 автоматически введется формула =А3+В3*С6,

· в ячейку Е8 автоматически введется формула =А4+В4*С7 и т.д.

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

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

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

Бывает, что один из элементов формулы не должен модифицироваться, например ссылка С4. В этом случае ссылка С4 должна быть записана так – $C$4 (знаки $$ вводятся автоматически при нажатии клавиши F4). Тогда при протяжке в ячейках: Е6, Е7, Е8 и т.д. наша формула будет принимать вид:

· Е6= А2+В2*$C$4

· Е7= А3+В3*$C$4

· Е8= А4+В4*$C$4

При любом протягивании МЗ активной ячейки ссылки А1 и В1 модифицируются (относительные ссылки), а ссылка $C$4 – не модифицируется. Такие ссылки называются абсолютными. В ссылке может быть зафиксирован один из её элементов. Например, $C4 или C$4. Такие ссылки называются смешанными. Знак $ определяет, какой элемент ссылки не модифицируется.

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

= А1+В1*ЛИСТ3!С4

Используемый лист может иметь любое название. Если в названии листа есть пробелы, то оно (название) заключается в кавычки. Связывание двух ячеек можно упростить, для чего на листе 1 в ячейке Е5 набрать знак = затем через корешок обратиться к Листу 3 и щелкнуть по ячейке С4.

Техника копирования, перемещения, удаления ячеек, содержащих формулу такая же, как ячеек содержащих данные (см. 6.2.2.) Но если в формуле содержатся относительные ссылки, то при копировании и перемещении они модифицируются. Рассмотрим на примере. Пусть в ячейке Е1 содержится формула

= Al+$Bl+C$l+$D$l.

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

=А4+$B4+E$l+$D$l.

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

Вызывается функция, как правило, с помощью Мастера функций (вкладка «Вставка») или кнопка fx в центре строки формул.

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

Например:

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

Диалоговое окно Мастер функций облегчает ввод функций при создании формул, содержащих функции (рис.4).

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

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

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

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

«если среднее значение блока С5:С9 больше 40, ТО суммируются данные блока F2:F5; ИНАЧЕ 0»

Дата добавления: 2017-01-14