Мастер функции в Excell

Тема: мастер функции в Excel

 

Введение

1.Рабочая книга. Лист. Ячейка 5

2. Понятие функции. Мастер функций 6

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

4. Редактирование  формул 10

5. Различные  виды функций 10

Заключение 19

 

 

ВВЕДЕНИЕ

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

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

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

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

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

 

1.Рабочая книга. Лист. Ячейка

 
Прежде, чем мы перейдем непосредственно к теме данной работы необходимо, на мой взгляд, вспомнить те понятия с которых, собственно и начинается работа с программой Excel. Итак, каждый файл Excel называется рабочей книгой. То есть, рабочая книга – это документ (файл), который мы открываем, сохраняем, копируем, удаляем… Каждая рабочая книга содержит три листа рабочих таблиц. Для того чтобы ориентироваться в них, в Excel предусмотрены ярлыки с именами рабочих листов от Лист1 до Лист3, похожие на закладки на обрезанных полях блокнота. Каждый лист в рабочей книге, в свою очередь, разбит приблизительно на 16 миллионов ячеек, в каждую из которых можно вводить данные.

Рис. 1 Экран программы Excel 2002

 

На рисунке 1

 

Как мы видим, на рисунке 1 по краям рабочей таблицы Excel находится рамка с обозначениями строк и столбцов: столбцам (всего их 256) соответствуют буквы, а строкам – числа (от 1 до 65536). И столбцы и строки имеют большое значение, поскольку именно они составляют адрес ячейки, например А1. Подобная система адресации ячеек – это пережиток, унаследованный от VisiCalc. Но, кроме системы А1, Excel 2000 поддерживает еще более старую, но в тоже время более корректную систему адресации ячеек R1C1. В ней пронумерованы и строки (rows) и столбцы (columns) рабочей таблицы, причем номер строки предшествует номеру столбца.

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

 

2. Понятие функции. Мастер функций.

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

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

• Как числовое значение (например, 89 или – 5,76),
• Как координату ячейки (это наиболее распространенный вариант),
• Как диапазон ячеек (например, С3:F3).

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

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

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

 

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

Безусловно, функцию можно ввести, набрав ее прямо в ячейке. Однако Excel предоставляет на стандартной панели инструментов кнопку Вставка функции. В открывшемся диалоговом окне (см. рис.2) Мастер функций – шаг 1указывается нужная функция, затем Excel выводит диалоговое окно Аргументы функции, в котором необходимо ввести аргументы функции (рис. 3).

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

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

• 10 недавно использовавшихся,
• полный алфавитный перечень,
• финансовые,
• дата и время,
• математические,
• статистические,
• ссылки и массивы,
• работа с базой данных,
• текстовые,
• логические,
• проверка свойств и значений.

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

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

Чтобы выбрать аргументы, поместим точку вставки в поле Число1 и щелкнуть на ячейке электронной таблицы (или перетащить мышь, выделив нужный диапазон). После этого в текстовом поле Число1 появится адрес ячейки (либо адрес диапазона) и одновременно в окне справа – числовое значение, введенное в эту ячейку, а также внизу диалогового окна после слова Значение отразится итоговое значение функции.

В пользователя всегда есть возможность уменьшить диалоговое окно до размера поля Число1 и кнопки максимизации. Для этого достаточно щелкнуть по кнопке минимизации, находящейся справа от поля. Есть возможность и просто перетащить окно на другое место.

Если необходимо просуммировать содержимое нескольких ячеек, либо диапазонов, то нужно нажать клавишу или щелкнуть в поле Число2 чтобы переместить в него курсор (Excel реагирует на это

Рис. 2. Выбор функции

 

Рис. 3. Диалоговое окно Аргументы функции

 
 

списка аргументов – появляется текстовое поле Число3). В поле Число2 указываем вторую ячейку (или диапазон).

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

 

4. Редактирование формул

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

После щелчка на кнопке Изменить формулу Excel открывает диалоговое окно Аргументы функции, в котором можно отредактировать аргументы, указав новые ссылки на ячейки или диапазоны ячеек в текстовых полях Число1, Число2 и т.д. если необходимо заменить текущий аргумент, то его необходимо выделить, а затем удалить, нажав клавишу 

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

 

5. Различные виды функций

Завершая рассказ о функциях, мы рассмотрим некоторые из них более подробно. Итак, один из инструментов этоАвтосумма, он находится на стандартной панели инструментов (обозначен греческой буквой S). Этот инструмент не только вводит функцию СУММ, но и выбирает диапазон ячеек текущего столбца или строки, содержимое которых, как он полагает, пользователь намерен просуммировать, и автоматически задает их в качестве аргументов функции. Но главное то, что в девяти случая из десяти Excel точно выбирает диапазон ячеек для суммирования.

Итак, по умолчанию Автосумма помещает в текущую ячейку функцию СУММ, однако это можно изменить. Чтобы с помощью этого инструмента можно было поместить другую функцию (СРЗНАЧ, МАКС или МИН), необходимо щелкнуть на кнопке со стрелкой вниз справа от него и выбрать в раскрывающемся меню имя нужной функции. При щелчке на кнопкеДополнительно Excel откроет диалоговое окно Вставка функции.

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

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

Иногда требуется вычислить среднее значение. Здесь можно выделить несколько случаев:

1. Вычисление среднего значения ячеек, расположенных непрерывно в одной строке или одном столбце. Для этого необходимо сделать следующие шаги
• Выделите ячейку снизу или справа от чисел, среднее значение которых требуется найти.
• Нажмите на панели инструментов Стандартные стрелку рядом с кнопкой Автосумма, а затем выберите команду Среднееи нажмите клавишу ВВОД.
2. Вычисление среднего значения ячеек, расположенных вразброс. Для выполнения этой задачи используется функцияСРЗНАЧ, которая возвращает среднее (арифметическое) своих аргументов. Причем аргументов может быть от 1 до 30, и они должны быть либо числами, либо именами, массивами или ссылками, содержащими числа.
3. Вычисление среднего взвешенного значения. Для этого используются функции СУММПРОИЗВ и СУММ. Итак, функцияСУММПРОИЗВ перемножает соответствующие элементы заданных массивов и возвращает сумму произведений. Массивов, чьи компоненты нужно перемножить, а затем сложить может быть от 2 до 30 массивов.

Однако следует помнить, что аргументы, которые являются массивами, должны иметь одинаковые размерности. Если это не так, то функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!. А также то, что СУММПРОИЗВ трактует нечисловые элементы массивов как нулевые.

Функция СУММ, как уже упоминалось выше, суммирует все числа в интервале ячеек. Причем, учитываются числа, логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.

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

Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают значения ошибок.

4. Вычисление среднего значения всех чисел, кроме нулевых (0). Для выполнения этой задачи используются функцииСРЗНАЧ и ЕСЛИ.

Excel 2002 позволяет  также производить действия и  над матрицами. Для этого присутствуют  функции МОБР, МОПРЕД, МУМНОЖ.

Функция МОБР возвращает обратную матрицу для матрицы, хранящейся в массиве. В строке формул она отражена какМОБР(массив), где массив — это числовой массив с равным количеством строк и столбцов.

Причем массив может быть задан по разному: как диапазон ячеек, например A1:C3; как массив констант, например {1;2;3: 4;5;6: 7;8;9}; или как имя диапазона или массива.

Если какая-либо из ячеек в массиве пуста или содержит текст, то функция МОБР возвращает значение ошибки #ЗНАЧ!.МОБР также возвращает значение ошибки #ЗНАЧ!, если массив имеет неравное число строк и столбцов.

Обратные матрицы, как и определители, обычно используются для решения систем уравнений с несколькими неизвестными. Произведение матрицы на ее обратную — это единичная матрица, то есть квадратный массив, у которого диагональные элементы равны 1, а все остальные элементы равны 0.

В качестве примера того, как вычисляется обратная матрица, рассмотрим массив из двух строк и двух столбцов A1:B2, который содержит буквы a, b, c и d, представляющие любые четыре числа. В следующей таблице приведена обратная матрица для A1:B2:

Таблица 1

Обратная матрица для А1:В2

	Столбец A	Столбец B
Строка 1	d/(a*d-b*c)	b/(b*c-a*d)
Строка 2	c/(b*c-a*d)	a/(a*d-b*c)

МОБР производит вычисления с точностью до 16 значащих цифр, что может привести к небольшим численным ошибкам округления.

МОПРЕД возвращает определитель матрицы (матрица хранится в массиве).

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