МИНИСТЕРСТВО ОБРАЗОВАНИЯ РЕСПУБЛИКИ БЕЛАРУСЬ
БЕЛОРУССКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
РЕФЕРАТ НА ТЕМУ
«Excel. Использование функций рабочей таблицы. Аргументы. Мастер функций. Логические, информационные функции и функции работы со ссылками и массивами »
Подготовила студентка
экономического факультета
группы «Финансы и кредит»
Плеханова Юлия Ивановна
Проверил: Позняков А.М.
г. Минск, 2008
Оглавление
Введение. - 1 -
Глава 1. Использование функций рабочей таблицы.. - 2 -
Глава 2.Ввод функций в рабочем листе Excel. Мастер функций. - 1 -
Глава 3. Логические функции. - 1 -
Глава 4.Информационные функции. - 1 -
Глава 5. Функции для работы со ссылками и массивами. - 1 -
Заключение. - 1 -
Список литературы: - 1 -
Введение
Электронная таблица Excel
для Windows
корпорации Microsoft
входит в состав знаменитого пакета Microsoft Оffice.
С момента своего появления в 1985 году она завоевала репутацию наиболее мощной и удобной электронной таблицы и до настоящего времени занимает ведущее положение на мировом рынке. Excel
считается одной из лучших разработок фирмы Microsoft
. В России Excel
является, по существу, единственной реально используемой электронной таблицей.
Благодаря мощным возможностям и удобному интерфейсу электронные таблицы применяются в самых различных областях нашей деятельности. Работа в среде Excel
не требует квалификации программиста, поэтому его пользователями являются инженеры, научные работники, экономисты, руководители любого уровня, менеджеры и все, кому приходится производить какие-либо вычисления. Именно поэтому знакомство с Excel
, как и с другими Windows –
приложениями, входящими в состав пакета Microsoft Оffice
, является составной частью общеобразовательного курса Информатики
.
Глава 1. Использование функций рабочей таблицы
Функция Excel – это специальная, заранее определенная формула, которая работает с одним или несколькими значениями и возвращает результат. Некоторые функции являются эквивалентами длинных математических формул, которые можно сделать самому. А некоторые функции в виде формул реализовать невозможно.
Для выполнения вычислений на листах книги можно использовать стандартные функции Microsoft Excel. Величины, которые используются для вычисления значений функций, называются аргументами. Величины, которые являются результатом вычисления функций, называются возвращаемыми значениями. Последовательность, в которой должны располагаться аргументы функции, называется синтаксисом функции. Чтобы использовать функцию, ее нужно ввести как часть формулы в ячейку листа. Формула должна начинаться со знака равенства (=)
, за которым следует набор величин, операторов и функций. Если функция стоит в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле. Скобки используются для обозначения начала и конца списка аргументов. Скобки должны быть парными, пробелы перед скобками или после них не допускаются. Внутри скобок должны располагаться аргументы.
Список аргументов может состоять из чисел, текста, логических величин, массивов, значений ошибок или ссылок. Задаваемые аргументы должны иметь допустимые для данного аргумента значения. Аргументы могут быть как константами, так и формулами. Эти формулы, в свою очередь, могут содержать другие функции.
Функции, являющиеся аргументом другой функции, называются вложенными
. В формулах Microsoft Excel
можно использовать до семи уровней вложения функций. Существует несколько категорий функций, которые используются в зависимости от поставленной задачи. Все функции можно подразделить на следующие группы:
- -математические,
- -статистические,
- -логические,
- -дата и время,
- -финансовые,
- -текстовые,
- -ссылки и массивы,
- -работы с базой данных,
- -проверки свойств и значений
- -инженерные,
- -информационные
Вывод: Функция Excel - это заранее определенная формула, которая работает с одним или несколькими значениями и возвращает результат. Наиболее распространенные функции Excel являются краткой записью часто используемых формул.Таким образом, в Excel используется более трехсот встроенных функций, которые выполняют широкий спектр различных вычислений. Они выполняют определенные вычисления над своими аргументами и возвращают одно или несколько значений.
Глава 2.Ввод функций в рабочем листе Excel. Мастер функций.
Можно вводить функции в рабочем листе прямо с клавиатуры или с помощью команды "Функция" меню "Вставка". При вводе функции с клавиатуры лучше использовать строчные буквы. Когда ввод функции закончен, Excel изменит буквы в имени функции на прописные, если оно было введено правильно. Если буквы не изменяются, значит, имя функции введено неверно.
Если вы выделите ячейку и выберите в меню "Вставка" команду "Функция", Excel выведет окно диалога "Мастер функций".
(Рис.2) Немного быстрее можно этого добиться, нажав клавишу со значком функции в строке формул(Рис.1)
Рис.1 Вставка функции путём нажатия значка функции на панели инструментов.
Рис.2. Мастер функций. Шаг1из 2. Выбор функции
Открыть это окно можно также с помощью кнопки "Вставка функции" на стандартной панели инструментов. В этом окне сначала выберите категорию в списке "Категория" и затем в алфавитном списке "Функция" укажите нужную функцию. Excel введет знак равенства, имя функции и пару круглых скобок. Затем Excel откроет второе окно диалога мастера функций.(Рис.3)
Рис.3 Мастер функций. Шаг 2 из 2. Диалоговое окно «Аргументы функции»
Второе окно диалога мастера функций содержит по одному полю для каждого аргумента выбранной функции. Если функция имеет переменное число аргументов, это окно диалога при воде дополнительных аргументов расширяется. Описание аргумента, поле которого содержит точку вставки, выводится в нижней части окна диалога. Справа от каждого поля аргумента отображается его текущее значение. Это очень удобно, когда вы используете ссылки или имена. Текущее значение функции отображается внизу окна диалога. Нажмите кнопку "ОК" и созданная функция появится в строке формул.(Рис.4)
Рис.4 Завершающий этап работы мастера функций. Вывод формулы.
Вывод: Использование мастера функций значительно облегчает работу с электронными таблицами.
Глава 3. Логические функции
Логические функции являются неотъемлемыми компонентами многих формул. Они используются всякий раз, когда необходимо выполнить те или иные действия в зависимости от выполнения каких-либо условий. В Excel
имеются следующие логические функции:
ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ, ЕПУСТО.
Функция ЕСЛИ.
Функция ЕСЛИ (IF) имеет следующий синтаксис: =ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)
Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае - 20:
=ЕСЛИ(А1>3;10;20)
Аргументами функции, а также результатом выполнения функции могут быть текстовые константы.Например,
==ЕСЛИ(В5>100; “Принять”;”Отказать”)
Если содержимое ячейки больше 100, то результатом выполнения функции будет значение “Принять”, в противном случае - ”Отказать” (Рис. 5).
Рис.5
Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например:
=ЕСЛИ(А1="Динамо";10;290). Эта формула возвращает значение 10, если ячейка А1 содержит строку "Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.
Функции И, ИЛИ, НЕ
Функции И (AND), ИЛИ (OR), НЕ (NOT) - позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:
=И(логическое_значение1;логическое_значение2...) =ИЛИ(логическое_значение1;логическое_значение2...)
Функция НЕ имеет только один аргумент и следующий синтаксис:
=НЕ(логическое_значение)
Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения. Приведем пример. Пусть Excel возвращает текст "Прошел", если ученик имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:
=ЕСЛИ(И(А2>4;А3<3);"Прошел";"Не прошел")
Не смотря на то, что функция ИЛИ имеет те же аргументы, что и И, результаты получаются совершенно различными. Так, если в предыдущей формуле заменить функцию И на ИЛИ, то ученик будет проходить, если выполняется хотя бы одно из условий (средний балл более 4 или пропуски занятий менее 3). Таким образом, функция ИЛИ возвращает логическое значение ИСТИНА, если хотя бы одно из логических выражений истинно, а функция И возвращает логическое значение ИСТИНА, только если все логические выражения истинны.
Рассмотрим примеры:
=ЕСЛИ(И(А3>0; D3>0); “Решение есть”; “Решения нет”))
=ЕСЛИ(ИЛИ(А3<0; D3<0); “Решения нет”; “Решение есть”))
В первом случае: если и содержимое ячейки А3>0, и содержимое ячейки D3>0, результатом будет “Решение есть”, если содержимое хотя бы одной из ячеек (А3 или В3) <=0, результатом будет - “Решения нет” (Рис. 6, 7, 8).
Рис. 6, 7, 8
Функция НЕ меняет значение своего аргумента на противоположное логическое значение и обычно используется в сочетании с другими функциями. Эта функция возвращает логическое значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и логическое значение ЛОЖЬ, если аргумент имеет значение ИСТИНА.
Например,
=НЕ(2*2=4) вернет ЛОЖЬ, так как условие 2*2=4 истинно (Рис. 9).
=НЕ(2*2=5) вернет ИСТИНА, так как условие 2*2=5 ложно (Рис.10).
Рис.9,10
Вложенные функции ЕСЛИ
Иногда бывает очень трудно решить логическую задачу только с помощью операторов сравнения и функций И, ИЛИ, НЕ. В этих случаях можно использовать вложенные функции ЕСЛИ. Например, в следующей формуле используются три функции ЕСЛИ:
Например,
=ЕСЛИ(В10=25; “Отлично”; ЕСЛИ(И(В10<25;В10>22); “Хорошо”; ЕСЛ
Выполняется функция следующим образом: если число, находящееся в ячейке В10
, равно 25
, то значением функции будет “Отлично”
; иначе – если число, находящееся в ячейке В10
меньше 25
, но больше 22
, то функция примет значение “Хорошо”
, иначе – если В10
меньше или равно 22
и больше 19
, функция примет значение “Удовлетворительно
”, иначе “Неудовлетворительно
” (Рис.11, 12, 13).
Рис.11,12,13
Следует иметь в виду, что вложенных функций ЕСЛИ() должно быть на единицу меньше, чем возможных вариантов принимаемых значений.
Функции ИСТИНА и ЛОЖЬ
Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:
=ИСТИНА() =ЛОЖЬ()
Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение "Проходите", если выражение в ячейке А1 имеет значение ИСТИНА:
=ЕСЛИ(А1=ИСТИНА();"Проходите";"Стоп"). В противном случае формула возвратит "Стоп".
Функция ЕПУСТО
Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:
=ЕПУСТО(значение)
Аргумент значение может быть ссылкой на ячейку или диапазон. Если значение ссылается на пустую ячейку или диапазон, функция возвращает логическое значение ИСТИНА, в противном случае ЛОЖЬ.
Вывод: логические выражения используются для записи условий, в которых сравниваются числа, функции, формулы, текстовые или логические значения. Любое логическое выражение должно содержать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения.
Глава 4.Информационные функции
Информационные функции и функции проверки свойств и значений применяются обычно в макросах и довольно редко - в рабочих листах. В рабочих листах эти функции используются главным образом вместе с функцией ЕСЛИ в случае, если результаты вычислений зависят от содержимого ячейки.
| Функция
|
Описание
|
| ЯЧЕЙКА |
Возвращает информацию о формате, расположении или содержимом ячейки. |
| ТИП.ОШИБКИ |
Возвращает числовой код, соответствующий типу ошибки. |
| ИНФОРМ |
Возвращает информацию о текущей операционной среде. |
| ЕПУСТО |
Возвращает значение ИСТИНА, если аргумент является ссылкой на пустую ячейку. |
| ЕОШ |
Возвращает значение ИСТИНА, если аргумент ссылается на любое значение ошибки, кроме #Н/Д. |
| ЕОШИБКА |
Возвращает значение ИСТИНА, если аргумент ссылается на любое значение ошибки. |
| ЕЧЁТН |
Возвращает значение ИСТИНА, если значение аргумента является четным числом. |
| ЕЛОГИЧ |
Возвращает значение ИСТИНА, если аргумент ссылается на логическое значение. |
| ЕНД |
Возвращает значение ИСТИНА, если аргумент ссылается на значение ошибки #Н/Д. |
| ЕНЕТЕКСТ |
Возвращает значение ИСТИНА, если значение аргумента не является текстом. |
| ЕЧИСЛО |
Возвращает значение ИСТИНА, если аргумент ссылается на число. |
| ЕНЕЧЁТ |
Возвращает значение ИСТИНА, если значение аргумента является нечетным числом. |
| ЕССЫЛКА |
Возвращает значение ИСТИНА, если значение аргумента является ссылкой. |
| ЕТЕКСТ |
Возвращает значение ИСТИНА, если значение аргумента является текстом. |
| Ч |
Возвращает значение, преобразованное в число. |
| НД |
Возвращает значение ошибки #Н/Д. |
| ТИП |
Возвращает число, обозначающее тип данных значения. |
Вывод: чаще всего информационные функции применяются в макросах.
Глава 5. Функции для работы со ссылками и массивами.
Если Вам необходимо найти какое-либо значение в таблице или определить ссылку на определенную ячейку, воспользуйтесь специальными встроенными функциями Microsoft Excel для работы со ссылками и массивами:
· АДРЕС - создает адрес ячейки в виде текста, используя номер строки и номер столбца;
· ВПР - ищет заданное значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы;
· ВЫБОР используется, чтобы выбрать одно значение из списка, в котором может быть до 29 значений;
· ГИПЕРССЫЛКА - создает ярлык, который открывает документ, расположенный в сети или в Интернет;
· ГПР - ищет заданное значение в верхней строке таблицы и возвращает значение в том же столбце из заданной строки таблицы;
· ДВССЫЛ - возвращает ссылку, заданную текстовой строкой (например, если эта функция ссылается на ячейку A1, содержимое которой представляет собой текст "B1", а в ячейке B1 хранится число 23, то функция вернет число 23);
· ДРВ – получает данные реального времени от программы, поддерживающей автоматизацию СОМ;
· ИНДЕКС - возвращает значение или ссылку на значение из указанного интервала (на основании номера строки и номера столбца в интервале);
· ОБЛАСТИ - возвращает количество непрерывных областей в ссылке;
· ПОИСКПОЗ - возвращает относительное положение элемента массива;
· ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ – извлекает данные, хранящиеся в сводной таблице;
· ПРОСМОТР - просматривает диапазон в поисках определенного значения и возвращает значение из другого столбца или строки;
· СМЕЩ - возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов;
· СТОЛБЕЦ - возвращает номер столбца по заданной ссылке;
· СТРОКА - возвращает номер строки по заданной ссылке;
· ТРАНСП - транспонирует массив значений;
· ЧИСЛСТОЛБ - возвращает количество столбцов в ссылке или массиве;
· ЧСТРОК - возвращает количество строк в ссылке или массиве.
Рассмотрим для примера две функции:
ВПР
(искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)
Функция предназначена для поиска заданного значения в крайнем левом столбце таблицы и возврата значения в той же строке из указанного столбца таблицы.
ГПР
(искомое_значение; инфо_таблица; номер_строки; интервальный_просмотр)
Функция предназначена для поиска заданного значения в первой строке таблицы и возврата значения в том же столбце из указанной строки таблицы.
Аргументы:
· Искомое_значение - значение, которое функция будет искать в первом столбце или первой строке массива. Искомое значение может быть значением, ссылкой или текстовой строкой.
· Инфо_таблица - таблица, в которой ищутся данные.
· Номер_столбца (строки) - номер столбца (строки) в массиве Инфо_таблица, из которого будет возвращаться соответствующее значение.
· Интервальный_просмотр - логическое значение, которое определяет, нужно ли, чтобы функция искала точное соответствие. Если этот аргумент - ИСТИНА или опущен, то возвращается приблизительное значение (наибольшее из значений первого столбца (строки), которые меньше требуемого), если аргумент - ЛОЖЬ, то функция ищет точное соответствие.
Предположим, что у Вас имеется база данных сотрудников. В колонке B
находятся фамилии сотрудников, в колонке C
- их имена.( Рис.14)
Рис 14. База данных сотрудников.
Если фамилия какого-либо сотрудника известна, то функция ВПР
поможет узнать его имя:
ВПР("Казаков"; B3:C11; 2; ЛОЖЬ) = Антон
, т.к. в первой колонке диапазона B3:C11
будет найдено значение в точности соответствующее первому аргументу (Казаков
). После этого функция вернет содержимое ячейки, которая находится во 2-ой колонке этого диапазона в той же строке, что и первый аргумент.
ВПР("Казаковы";B3:C11;2;ЛОЖЬ) = #Н/Д
, т.к. в первой колонке диапазона B3:C11
не найдено значение, в точности соответствующее первому аргументу (Казаковы
).
ВПР("Казаковы";B3:C11;2;ИСТИНА) = Антон
, т.к. значение Казаковы
в первой колонке диапазона B3:C11
ищется не точно, а приблизительно.
Если известен порядковый номер сотрудника в списке, Вы можете узнать его фамилию с помощью функции ГПР
:
ГПР("Фамилия"; B2:C11; 7;ЛОЖЬ) = Казаков.
Вывод: специальные формулы для работы с массивами и ссылками облегчают работу с базами данных, списками.
Заключение
Современные табличные процессоры, в частности Microsoft Excel, представляют собой чрезвычайно мощное средство по решению широкого диапазона задач: от проведения простейших расчетов до создания средств автоматизации вычислений. Основным достоинством электронной таблицы Excel является наличие мощного аппарата формул и функций. Любая обработка данных в Excel осуществляется при помощи этого аппарата В Excel используется более трехсот встроенных функций. Они выполняют определенные вычисления над своими аргументами и возвращают одно или несколько значений. Применение функций значительно расширяет возможности Excel, делает эту программу действительно универсальной.
Список литературы:
1. Информатика. Базовый курс: Учебник для вузов/ Под ред. С.В. Симоновича.– СПб.: Питер, 2001. – 638с.
2. Алексеев А., Евсеев Г., Мураховский В., Симонович С. Новейший самоучитель работы на компьютере. / Под ред. С.В. Симоновича. – Москва: ДЕСС КОМ, 2000. – 654с.
3. Экономическая информатика. Учебник для вузов/ Под ред. В.В. Евдокимова. – СПб.: Питер, 1997.
4. Комягин В.Б., Коцюбинский А.О., Excel 7.0 в примерах: Практ. Способ. – М.: Нолидж, 1996. – 432с.
5. Попов А., Excel: практическое руководство.: Москва: ДЕСС КОМ, 2001. – 301с.
6. Ефимова М.Р., Ганченко О.И., Петрова Е.В., Практикум по общей теории статистики: Учеб. пособие. – М.: Финансы и статистика, 2000. – 280с.