Вычисления с помощью формул и функций.

 

Помимо различных типов данных ячейки таблицы Excel могут содержать формулы. Любая формула в Excelначинается со знака «=»!

В формулах допустимо использование следующих операторов: вычитание (-), деление (/), умножение (*), возведение в степень (^), <, >, <>(не равно), => (больше или равно), <= (меньше или равно) и, так называемых операторов связи - диапазон (:), объединение (;) и объединение текстов (&). Для ввода формулы необходимо:

1) выделить ячейку и нажать клавишу «=» (равно);

2) набрать формулу и нажать Enter. Например: =А1*В1-С1/2+D1^2.

Формула использует ссылки на ячейки А1, В1,С1 и D1. Содержимое этих ячеек подставляется в формулу и после нажатия клавиши Enter вычисляется результат. Таким образом, в ячейке показывается результат вычисления формулы, а саму формулу можно увидеть и/или отредактировать только в строке формул, предварительно выделив нужную ячейку. Следует заметить, что ссылки на ячейки в формуле лучше набирать не с клавиатуры, а с помощью мыши: для этого после нажатия клавиши «=» надо щелкнуть мышью в ячейке А1 (в строке формул автоматически появится А1), затем набрать *, щелкнуть мышью в ячейке В1 и т.д.

Формулы могут содержать ссылки не только на ячейки, но и блоки ячеек, например =А1:А4+С1:С4, что равносильно формуле =А1+А2+АЗ+ А4+С1+С2+СЗ+С4. Кроме того, в формулах можно использовать функции.

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

=СУММ(А1:А4;С1:С4) или = СУММ(А1;А2;АЗ;А4;С 1;С2;СЗ;С4);

Кнопка Автосумма (Σ) стандартной панели инструментов Стандартная облегчает использование функции суммирования.

Пример:

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

 

Для этого выделите ячейку А6, нажать кнопку Σ, затем Enter. Сумма чисел первого столбца =СУММ(А4:А5) появится в ячейке А6. Аналогичную операцию можно повторить и для остальных столбцов. (Рис. 4)

Однако удобнее применить следующий прием – прием копирования с помощью маркера заполнения: выделить ячейку А6 с записанной функцией =СУММ(А4:А5), установить указатель мыши на Маркер заполнения (маленький черный квадрат) в нижнем правом углу ячейки (указатель примет форму черного крестика) и, удерживая левую кнопку мыши, растянуть рамку вправо на соседние ячейки В6,С6, D6 и т.д. После того, как левая кнопка мыши будет отпущена, в ячейках В6, С6, D6,, К6 появятся результаты суммирования. Обратить внимание, что в ячейке В6 будет находиться формула = СУММ(В4:В5), а в ячейке С6 – формула = СУММ (С4:С5), т.е. имена ячеек (в данном случае имена столбцов) автоматически изменились. При копировании формулы вниз в именах ячеек будут меняться имена строк. Такой вид адресации ячеек называется относительной адресацией, или относительной ссылкой на ячейку.

Для того чтобы при копировании имена ячеек, входящих в формулы не менялись, необходимо использовать абсолютную адресацию, или абсолютную ссылку на ячейку. Для абсолютной адресации (ссылки) используют знак $, который ставится перед тем именем строки или именем столбца ячейки, которые необходимо зафиксировать, т.е. сделать неизменяемыми при копировании формулы. Знак $ можно ввести вручную перед именем строки и именем столбца, перейдя на латинский шрифт и нажав клавиши SHIFT+4, но целесообразней (проще и быстрее) установить курсор мыши в строке формул на название ячейки (А4) и нажать функциональную клавишу F4 один раз – произойдет фиксация имени строки и имени столбца, т.е автоматически вставиться знак $ и перед А и перед 4 ($А$4). Повторное нажатие на эту клавишу фиксирует только имя строки (A$4), третье нажатие на F4 фиксирует имя столбца ($A4), а четвертое – отмена абсолютной ссылки на ячейку (А4).

Формула суммирования ячеек в столбце А с абсолютной адресацией (ссылкой) будет выглядеть так: =СУММ($А$4:$А$5) и при копировании ее в ячейки В6, С6, D6,, К6 имена ячеек в формуле не изменятся, а результаты вычисления будут равны результату вычисления в ячейке А6 (Рис. 6).

 

Рис. 6. Автосуммирование с использованием абсолютной ссылки на ячейку.

 

Помимо Автосуммирования для написания формул с использованием функций очень удобно пользоваться Мастером функций (Рис. 7), пиктограмма которого также находится на панели инструментов Стандартная:

 

Рис. 7 Пиктограмма Мастера функций.

Щелчок на пиктограмму левой кнопкой мыши вызывает диалоговое окно Мастера функций (на Рис. 8 - слева).

 

Рис.8. Диалоговое окно Мастера функций (шаг 1) для различных версий Excel.

 

В старших версий Excel для вызова Мастера функций необходимо щелкнуть на треугольник, находящийся справа от пиктограммы Автосуммирования и выбрать из списка Другие функции (Рис. 9), тем самым, вызвав, Мастер функций (Рис. 8).

 

Рис. 9 Пиктограмма Мастера функций для старших версий Excel.

 

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

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

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

Рис.10. Аргументы функции (шаг 2).

Затем, нажав левую клавишу мыши (курсор в виде белого крестика), растянуть выделение на нужное количество ячеек. Развернуть окно и нажать ОК.

 

Пример:

Подготовить таблицы как показано на рисунке 11 для построения параболы по формуле y=а*(x-b)² + c при условии, что начальное значение аргумента (х) равно -10 и (х) изменяется от -10 до 20 с шагом аргумента = 1, а=0,5, b=5, с=10.

 

Рис. 11 Таблицы для построения параболы.

 

Открыть Лист2, и щелкнув правой кнопкой мыши по названию листа, вызвать контекстное меню и выбрать опцию Переименовать. Вместо Лист2 напечатать Парабола.

В ячейку А1 вводим – «Аргумент», в ячейку В1 – «Функция – парабола», справа в ячейку D1 заголовок таблицы начальных значений параметров так, как показано на рис. 10. Затем выделить блок ячеек D1:L1, открыть пункт меню Формат→Ячейки, выбрать закладку Выравнивание. В этом окне дается возможность выравнивать текст в ячейке как по горизонтали, так и по вертикали самой ячейки, кроме того есть возможность расположить текст в ячейке не только горизонтально, но и вертикально, а также под заданным углом (справа - ячейка Ориентация, в которой можно мышью подвигать Надпись вверх/вниз, можно задать градусы угла под которым будет располагаться текст в ячейке). Рис. 12.

 

Рис.12 Диалоговое окно Формат Ячеек. Выравнивание.

 

Ниже имеется возможность переноса по словам, т.е. переход на другую строку в ячейке (комбинация клавишей левый ALT+ENTER также является переходом на др. строку) и объединение ячеек, куда и надо установить галочку для объединения ячеек в блок D1:L1.

Закладки Шрифт, Граница и Вид дают возможность форматирования шрифта, обрамления и заливки таблицы и их функции. Выбрать закладку Шрифт и отформатировать заголовок (изменить размер, цвет и начертание шрифта). Далее подготовить таблицы для введения в них формул как показано на рисунке 11.

Ввести в ячейку А2 начальное значение аргумента х -10, в ячейку А3 – формулу, изменяющую значение аргумента на его шаг при помощи ссылок на ячейки, в которых введены их значения: =A2+$E$7. При этом ввод имен ячеек производить не руками с клавиатуры; а после знака (=) щелкнуть мышью по ячейке А2, напечатать знак (+) и щелкнуть мышью по ячейке Е7. Для ячейки А2 используется относительная ссылка, т.к. при копировании формулы вниз вдоль строк необходимо, чтобы у ячейки А2 изменялось имя строки (А2 на А3, А3 на А4 и т.д. до А32) для того чтобы предыдущее значение аргумента в каждой новой ячейке увеличивалось бы на шаг (на 1) до конечного значения равного 20. Для ячейки Е7 используется абсолютная ссылка, т.к. числовое значение шага аргумента находится в определенном месте – в ячейке Е7, следовательно, при помощи функциональной клавиши F4 надо зафиксировать имя столбца и имя строки +$E$7. Нажать клавишу ENTER и при помощи маркера заполнения (черный крестик в нижнем левом углу ячейки) скопировать формулу вниз до 32 строки (х=20).

В ячейку В2 ввести формулу параболы =$E$4*(A2-$E$5)^2+$E$6 (формула записана при помощи ссылок на ячейки, в которые введены параметры параболы; знак ^ - означает возведение в степень). Т. к. ячейки Е4, Е5, Е6 не должны изменять имена строк и столбцов (в них введены значения параметров параболы, которые не должны изменяться при копировании формулы), зафиксировать их имена при помощи функциональной клавиши F4 (установить курсор мыши на имя ячейки и щелкнуть один раз на клавишу F4). В формуле, введенной в ячейку В2, используется ссылка на ячейку А2, в В3 на А3 и т.д., поэтому надо оставить относительную ссылку на ячейку А2, чтобы при копировании формулы вниз вдоль строк эта ячейка могла менять имя строки. После введения формулы нажатием клавиши ENTER произвести выполнение формулы и затем и при помощи маркера заполнения (черный крестик в нижнем левом углу ячейки) скопировать формулу вниз до 32 строки.

Построение диаграмм.

Для построения диаграмм необходимо вызвать Мастер построения диаграмм, нажав на панели инструментов Стандартная кнопку Мастера диаграмм. Рис. 13

Рис. 13 Мастер диаграмм. (шаг 1): тип диаграммы.

 

На первом шаге (рис.13) выбирается тип диаграммы и ее вид. Для перехода ко второму шагу следует нажать кнопку Далее. На втором шаге Мастера диаграмм можно изменить или определить исходные данные – источник данных диаграммы (рис.14). Окно второго шага содержит вкладки Диапазон данных и Ряд, т. е. на втором шаге выбираются данные (блок или блоки ячеек), на основании которых строится диаграмма. Закладка Диапазон данных активна по умолчанию, в ней источник данных рассматривается как один диапазон (ряд), который можно изменить или определить (если это не сделано перед вызовом Мастера диаграмм) в ячейке Диапазон.

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

 

Рис. 14 Мастер диаграмм. (шаг 2):источник данных диаграммы.

 

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

На третьем шаге Мастера диаграмм определяются параметры диаграммы, размещенные на шести закладках окна третьего шага. (Рис.15)

 

Рис. 15 Мастер диаграмм. (шаг 3): параметры диаграммы.

 

Вкладка Заголовки содержит поля ввода для названия диаграммы и для названий осей (в плоском случае ось X обычно называют осью категорий, а ось Y – осью значений).

Вкладка Легенда содержит элементы управления для настройки легенды. Легенда – это объект диаграммы, содержащий обозначения и названия рядов данных. Если легенда должна присутствовать на диаграмме, то это необходимо отметить флажком Добавить легенду, и в группе Размещение выбрать место для первоначального размещения легенды.

Вкладки Оси и Линии сетки позволяют определить наличие или отсутствие осей и линий сетки.

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

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

На четвертом шаге Мастера диаграмм определяется место размещения диаграммы: на имеющемся листе, т.е. на одном из рабочих листов электронной таблицы, или на отдельном специальном листе диаграмм. (Рис. 16)

 

Рис. 16 Мастер диаграмм. (шаг 4): размещение диаграммы.

 

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

 

Пример:

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

Перед вызовом Мастера диаграмм рекомендуется выделить ряды данных, по которым будет строиться диаграмма, если, конечно, диаграмма не сложная и выделяются ряды данных в виде отдельных столбцов или строк. Для построения параболы необходимо выделить блок ячеек В1:В32, затем нажать кнопку для вызова Мастера диаграмм на Стандартной панели инструментов и на первом шаге выбрать тип диаграммы – График. Нажав кнопку Далее, перейти на второй шаг. На втором шаге при выбранной закладке Диапазон данных в ячейке Диапазон уже будет отражен блок выбранных ранее ячеек и в верхней части окна построен график параболы. Переключившись на закладку Ряды можно увидеть пример того же графика параболы, и что в ячейке Значение уже введен блок выбранных ранее ячеек за исключением ячейки В1, в которой введено название столбца и которая автоматически введена в ячейку Имя. И соответственно в ячейке Ряд указано название ряда, введенное в ячейку В1 - Функция – парабола. Остается только выбрать блок ячеек для введения его в ячейку Подписи оси Х. Нажав кнопку , расположенную справа от ячейки, свернуть окно и белым толстым крестиком, нажав правую кнопку мыши протащить выделение с ячейки В2 до В32. Развернуть окно и перейти на третий шаг, нажав кнопку Далее. Выбрана закладка Заголовки: название диаграммы введено автоматически, можно ввести названия осей. Далее при выборе каждой закладки внести различные изменения и обратить внимание на пример параболы, находящийся справа. Установить те характеристики графика, которые лучше отражают данные и вид параболы, и перейти на четвертый шаг, где произвести выбор размещения диаграммы.

Отредактировать диаграмму, видоизменить все элементы диаграммы можно при помощи контекстного меню, вызываемого правой кнопкой мыши. Если медленно перемещать указатель мыши по области диаграммы, то можно увидеть всплывающие подписи тех элементов диаграммы, которые доступны для изменения. Это сама область диаграммы, область построения, оси X и Y, линии сетки осей X и Y, сам график функции, легенда (условные обозначения). Видоизменение, как правило, состоит в определении другого цвета для какого-то элемента, нового типа линии или маркера. Внести изменения можно, выбрав в контекстном меню первый пункт – Формат соответствующего объекта (области построения, области диаграммы, оси, легенды, рядов данных (рис. 17)) и определив нужные параметры.

 

 

Рис. 17 Окна Форматов соответствующих объектов.

 

Пример: