Вычисления в Excel. Формулы и функции. Формулы и функции в excel

Вычисления в Excel. Формулы и функции.

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

Формулы.

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

Если значения в ячейках, на которые есть ссылки в формулах, меняются, то результат изменится автоматически.

В качестве примера приведем формулы, вычисляющие корни квадратного трехчлена: ax2+bx+c=0. Они введены в ячейки A2 и A3 и имеют следующий вид:

=(-B1+КОРЕНЬ(B1*B1-4*A1*C1))/2/A1

=(-B1-КОРЕНЬ(B1*B1-4*A1*C1))/2/A1

В ячейках A1, B1 и C1 находятся значения коэффициентов a, b и с, соответственно. Если вы ввели значения коэффициентов a=1, b=-5 и с=6 (это означает, что в ячейках A1, B1 и C1 записаны числа 1, 5 и -6), то в ячейках A2 и A3, где записаны формулы, вы получите числа 2 и ‑3. Если вы измените, число в ячейке A1 на -1, то в ячейках с формулами вы получите числа -6 и 1.

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

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

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

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

• Формулы, использующие имена, легче воспринимаются и запоминаются, чем формулы, использующие ссылки на ячейки.

Например, формула “=Активы-Пассивы” гораздо понятнее, чем формула “=F6-D6”.

• При изменении структуры рабочего листа достаточно обновить ссылки лишь в одном месте — в определении имен, и все формулы, использующие эти имена, будут использовать корректные ссылки.

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

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

• Excel автоматически создает имена на основе заголовков строк и столбцов рабочего листа. Подробной информация о создании таких имен содержится в главе “Базы данных”.

• После того, как имя определено, вы можете:

• Заменить все соответствующие ссылки этим именем во всех местах рабочего листа.

Например, определив имя “Прибыль” как “=$F$12”, можно заменить все ссылки на ячейку $F$12 именем “Прибыль”.

• Быстро перейти на поименованную ссылку, заменить ссылки, вставить ссылку в формулу с помощью окна имени в строке формул.

studfiles.net

Лекция №1 Вычисления в Excel. Формулы и функции

Лекция № 2.1

Вычисления в Excel. Формулы и функции.

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

Формулы.

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

Если значения в ячейках, на которые есть ссылки в формулах, меняются, то результат изменится автоматически.

В качестве примера приведем формулы, вычисляющие корни квадратного трехчлена: ax2+bx+c=0. Они введены в ячейки A2 и A3 и имеют следующий вид:

=(-B1+КОРЕНЬ(B1*B1-4*A1*C1))/2/A1

=(-B1-КОРЕНЬ(B1*B1-4*A1*C1))/2/A1

В ячейках A1, B1 и C1 находятся значения коэффициентов a, b и с, соответственно. Если вы ввели значения коэффициентов a=1, b=-5 и с=6 (это означает, что в ячейках A1, B1 и C1 записаны числа 1, 5 и -6), то в ячейках A2 и A3, где записаны формулы, вы получите числа 2 и  3. Если вы измените, число в ячейке A1 на -1, то в ячейках с формулами вы получитечисла -6 и 1.

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

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

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

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

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

Например, формула “=Активы-Пассивы” гораздо понятнее, чем формула “=F6-D6”.

При изменении структуры рабочего листа достаточно обновить ссылки лишь в одном месте — в определении имен, и все формулы, использующие эти имена, будут использовать корректные ссылки.

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

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

Excel автоматически создает имена на основе заголовков строк и столбцов рабочего листа. Подробной информация о создании таких имен содержится в главе “Базы данных”.

После того, как имя определено, вы можете:

Заменить все соответствующие ссылки этим именем во всех местах рабочего листа.

Например, определив имя “Прибыль” как “=$F$12”, можно заменить все ссылки на ячейку $F$12 именем “Прибыль”.

Быстро перейти на поименованную ссылку, заменить ссылки, вставить ссылку в формулу с помощью окна имени в строке формул.

Перемещение и копирование формул. Относительные и абсолютные ссылки.

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

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

При копировании формул возникает необходимость управлять изменением адресов ячеек или ссылок. Для этого перед символами адреса ячейки или ссылки устанавливаются символы “$”. Изменяться только те атрибуты адреса ячейки, перед которыми не стоит символ “$”. Если перед всеми атрибутами адреса ячейки поставить символ “$”, то при копировании формулы ссылка не изменится.

Например, если в записи формулы ссылку на ячейку D7 записать в виде $D7, то при перемещении формулы будет изменяться только номер строки “7”. Запись D$7 означает, что при перемещении будет изменяться только символ столбца “D”. Если же записать адрес в виде $D$7, то ссылка при перемещении формулы на этот адрес не изменится, и в расчетах будут участвовать данные из ячейки D7. Если в формуле указан интервал ячеек G3:L9, то управлять можно каждым из четырех символов: “G”, “3”, “L” и “9”, помещая перед ними символ “$”.

Если в ссылке используются символы $, то она называется абсолютной, если символов $ в ссылке нет — относительной. Адреса таких ссылок называются абсолютными и относительными, соответственно.

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

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

Закупочная цена ($): - в зависимости от текущего курса $, который заносится в отдельную ячейку D1.

Сумма закупки: = Закупочная цена товара*Количество

Сумма реализации: = Розничная цена товара*Количество

Валовой доход: = Сумма реализации - Сумма закупки

Такие формулы записаны в ячейки таблицы:

Таблица 1

	A	B	C		D	E	F	G	H	I
1		Сегодня курс			3.62				Розничная цена
2									бананы		2.90руб
3									виноград		5.20руб
4									ананас		6.80руб
5	№ п/п	Наименование		Фирма поставщик	цена (руб)	цена ($)	Кол-во	Сумма закупки	Сумма реали-зации		Валовый доход
6	1	бананы		Frutis	2	=D6/kurs	200	=D6*F6	=F6*I$2		=H6-G6
7	2	бананы		SUMP	2.32	=D7/kurs	80	=D7*F7	=F7*I$2		=H7-G7
8	3	бананы		Forum	1.98	=D8/kurs	165	=D8*F8	=F8*I$2		=H8-G8
30	25	ананас		UFO	6.05	=D30/kurs	120	=D30*F10	=F30*I$4		=h50-G30

В данном примере ячейке D1 присвоено имя «kurs», которое используется во всех формулах для пересчета закупочной цены в $. Эта ячейка содержит только число (в нашем примере 3.62, был и такой курс когда-то), а пояснительный текст содержится в ячейке С1.

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

Функции.

Понятие функции.

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

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

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

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

Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой “;”. Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы. Помните о том, что при записи функции должны присутствовать открывающая и закрывающая скобки, при этом не следует вставлять пробелы между названием функции и скобками.

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

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

Типы функций

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

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

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

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

6

refdb.ru

Формулы и функции в MS Excel

Содержание

ВВЕДЕНИЕ

1.ТЕХНИЧЕСКОЕ ОПИАНИЕ ЗАДАЧИ

1.1 Достоинства и недостатки программного продукта

1.2 Требования к аппаратным и программным средствам

1.3 Алгоритм установки Excel

1.4 Актуальность темы

1.ТЕХНОЛОГИЧЕСКОЕ ОПИСАНИЕ

2.1 Формулы

2.2 Порядок ввода формул

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

2.4 Использование текста в формулах

2.5 Копирование формул

2.6 Имена ячеек для абсолютной адресации

2.7 Просмотр зависимостей

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

2.9 Функции Excel

2.10 Автовычисление итоговых функций

2.11 Использование Мастера функций

2.12 Выбор недавно использовавшихся функций

3. ТЕХНИКА БЕЗОПАСНОСТИ

3.1 Требования к помещению для эксплуатации компьютера

3.2 Требования к организации и оборудованию рабочих мест

3.3 Санитарно-гигиенические нормы работы на ПЭВМ

ЗАКЛЮЧЕНИЕ

ПЕРЕЧЕНЬ СОКРАЩЕНИЙ

Список литературы

ПРИЛОЖЕНИЯ

ВВЕДЕНИЕ

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

1. ТЕХНИЧЕСКОЕ ОПИАНИЕ ЗАДАЧИ

1.1 Достоинства и недостатки программного продукта

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

Достоинства

· Реализация алгоритмов в табличном процессоре не требует специальных знаний в области программирования.

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

· В отличие от обычного программирования, требующего строгой последовательности команд для работы программы, табличные процессоры “прощают” ошибки и незаконченность структуры.

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

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

· Весь процесс вычисления осуществляется в виде таблиц,

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

Недостатки

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

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

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

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

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

· Недостаток контроля за исправлениями повышает риск ошибок, возникающих из-за невозможности отследить, протестировать и изолировать изменения.

1.2 Требования к аппаратным и программным средствам

· Персональный компьютер с процессором Pentium 100 МГц или более мощным.

· Операционная система MicrosoftWindows 95 или более поздней версии либо MicrosoftWindowsNTWorkstation версии 4.0 с пакетом обновления 3 или более поздним.

· Оперативная память:

· 16 Мбайт памяти — для операционной системы Windows 95 или Windows 98 (Windows 2000). 32 Мбайт памяти — для операционной системы WindowsNTWorkstation версии 4.0 или более поздней.

Exсel – достаточно популярная программа, облегчающая работу с цифрами и таблицами, а также позволяющая проводить анализ достаточно больших объемов информации. Программа входит в пакет Microsoft Office. Ее можно купить на диске либо скачать с официального сайта компании Microsoft.

Для того чтобы установить Excel правильно, нужно воспользоваться автоматической установкой всего пакета Microsoft Office. Если его компоненты уже установлены (например, при выборочной установке других программ), или же пользователь в силу различных причин переустанавливает только программу Exсel, тогда нужно в процессе установки убрать все галочки напротив установленных ранее программ, оставив ее лишь напротив установки Exсel (рисунок 1.1.)

В случае, если происходит повторная установка Exсel, то в процессе инсталляции Microsoft Office самостоятельно определит недостающие компоненты и предложит пользователю их установить.

Далее произойдет установка выбранных Вами компонентов (рисунок 1.2.)

Затем появится диалоговое окно, которое сообщит Вам, что установка успешно завершена (рисунок 1.3.)

1.4 Актуальность темы

По всей вероятности, Excel — это второй по востребованности компонент Microsoft Office после приложения Word.

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

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

Добавилось множество новых функций, в то же время некоторые «лишние» предупреждения были удалены. Благодаря новым функциям упростилось разрешение целого ряда задач. По мнению ряда специалистов, из всех приложений Office XP больше всего аргументов в пользу обновления дает именно Excel 2003.

2. ТЕХНОЛОГИЧЕСКОЕ ОПИСАНИЕ

2.1 Формулы

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

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

возведение в степень и выражения в скобках;

умножение и деление;

сложение и вычитание.

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

2.2 Порядок ввода формул

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

Выделим произвольную ячейку, например А1. В строке формул введем =2+3 и нажмем Enter. В ячейке появится результат (5). А в строке формул останется сама формула (рисунок 2.1. )

Рисунок 2.1. Результат формулы

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

Введите в ячейку А1 число 10, а в ячейку А2 - число 15. В ячейке А3 введите формулу =А1+А2. В ячейке А3 появится сумма ячеек А1 и А2 - 25. Поменяйте значения ячеек А1 и А2 (но не А3!). После смены значений в ячейках А1 и А2 автоматически пересчитывается значение ячейки А3 (согласно формулы) (рисунок 2.2.)