Содержание

Введение……………………………………………………………………………….….3

1. Общая характеристика MS Excel  и ее функций .....……………………………..4

1.1. Интерфейс MS Excel…………………………………………………………..4

1.1. Возможности Excel при работе с функциями…………………………………..9

1.2. Виды функций используемые в Excel………………………………………….17

2. Использование условных функций и логических выражений

при решении задач в Excel…………………………………………………………….23

2.1. Условная функция и логические выражения в  электронных

таблицах Microsoft Excel…………………………………………………………23

2.2. Решение задач в Excel с использованием условных

функций и логических выражений…………………………………………………25

Заключение……………………………………………………………………………..28

Список использованной литературы……………………………………………….29

Введение

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

Актуальность работы. Наверное, трудно сейчас представить современного человека, не владеющего компьютером. Безусловно, компьютер занимает важное место в жизни человека. Применение компьютера позволяет облегчить работу человека. данную работу я посвящу табличному процессору Excel – одному из компонентов пакета Microsoft Office 2007. Табличный процессор Excel – самый популярный на сегодняшний день табличный редактор. Он позволяет легко оперировать с цифрами, обладает удобным интерфейсом – это как компьютер “общается” с пользователем, позволяет строить различные графики, множество диаграмм, которые способствуют более полному способу представления информации и усвоения материала.

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

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

Целью исследования является изучение темы «Использование условных функций и логических выражений при решении задач в Excel».

В рамках достижения поставленной цели мною были поставлены следующие задачи:

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

2. Использование условных функций и логических выражений при решении задач в Excel

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

1. Общая характеристика MS Excel  и ее функций

1.1. Интерфейс MS Excel

Табличный процессор MS Excel (электронные таблицы) – одно из наиболее часто используемых приложений пакета MS Office, мощнейший инструмент в умелых руках, значительно упрощающий рутинную повседневную работу. Основное назначение MS Excel – решение практически любых задач расчетного характера, входные данные которых можно представить в виде таблиц. Применение электронных таблиц упрощает работу с данными и позволяет получать результаты без программирования расчётов. В сочетании же с языком программирования Visual Basic for Application (VBA), табличный процессор MS Excel приобретает универсальный характер и позволяет решить вообще любую задачу, независимо от ее характера.

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

Основные возможности электронных таблиц:

проведение однотипных сложных расчётов над большими наборами данных;

автоматизация итоговых вычислений;

решение задач путём подбора значений параметров;

обработка (статистический анализ) результатов экспериментов;

проведение поиска оптимальных значений параметров (решение оптимизационных задач);

подготовка табличных документов;

построение диаграмм (в том числе и сводных) по имеющимся данным;

создание и анализ баз данных (списков).

Окно MS Excel 2007

Запустить Excel можно, щелкнув на кнопке Пуск и выбрав далее Программы -Microsoft Office - Microsoft Office Excel 2007, или воспользовавшись ярлыком этой программы на Рабочем столе. Если ярлыка Excel на вашем Рабочем столе нет, то его можно создать для программы EXCEL.EXE, которая обычно располагается на диске С: в папке: \Program Files\Microsoft Office\Office 12\. После запуска появляется окно программы:

Рассмотрим элементы интерфейса более подробно.

Работа с лентой

В этой версии программы меню и панели инструментов выполнены в виде ленты. Команды упорядочены в логические группы, собранные на вкладках. Например, если строка формул не отображается на экране, то её можно включить, выбрав на вкладке Вид кнопку Показать или скрыть и установив там соответствующий флажок «Строка формул».

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

Некоторые группы команд, которые используются не очень часто, сразу на ленте не отображаются, а появляются только при определенных действиях пользователя. Так, например, вкладка «Работа с рисунками» появится при выделении рисунка. Если размер окна Excel недостаточно большой для отображения всех кнопок на ленте, одна или несколько групп команд (в зависимости от ширины окна) будут свернуты до одной кнопки с раскрывающимся списком. При разворачивании списка вы увидите все остальные кнопки данной вкладки.

Перемещаться по ленте можно с помощью клавиатуры. Нажмите клавишу ALT. У всех доступных команд на ленте появятся подсказки для перехода к соответствующей вкладке. Если вы нажмете одну из клавиш перехода, на выбранной вкладке будут отображены всплывающие подсказки ко всем командам этой вкладки.

Прокручивать вкладки ленты можно с помощью колесика Scroll мыши. Для этого достаточно навести мышь на ленту и прокрутить Scroll до нужной вкладки.

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

Справочная система

Для получения справки можно щелкнуть по кнопке Справка  в заголовке документа или воспользоваться «горячей» клавишей F1. Поиск справки в открывшемся окне во многом напоминает работу с браузером, в частности, с Internet Explorer.

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

Настройка строки состояния

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

Кнопка Office

Кнопка Office, расположена в левом верхнем углу экрана. Щелчок по этой кнопке отображает меню работы с файлом, а также дает доступ к настройкам параметров программы.

Панель быстрого доступа

Рядом с кнопкой Office расположена панель быстрого доступа . По умолчанию на ней находятся только три кнопки — Сохранить документ, Отменить и Вернуть действие. Щелчок по кнопке в правой части панели позволит настроить ее по вашему усмотрению. Добавляйте сюда команды, которые используете наиболее часто, и вам не придется каждый раз искать их на ленте. Если желаемой команды в меню нет, выберите пункт Другие команды. Здесь вы сможете не только найти любую команду, чтобы добавить ее на панель быстрого доступа, но и настроить для этой команды «горячие» клавиши.

Листы рабочей книги, ячейка и адрес ячейки

 Каждая рабочая книга состоит из одного или нескольких рабочих листов, ярлычки которых видны внизу. Чтобы перейти на другой лист, нужно щелкнуть по его ярлычку или щелкнуть правой кнопкой мыши на кнопке для продвижения ярлычков и выбрать из списка имен необходимый лист. Если листов слишком много, в списке появится последний пункт «Все листы». Выбрав этот пункт, вы сможете выбрать любой из листов в открывшемся диалоговом окне.

Если ярлычки не видны, зайдите в меню кнопки Office, нажмите кнопку Параметры Excel, выберите категорию «Дополнительно» и включите опцию «ярлычки листов» в группе «Показать параметры для следующей книги». Здесь же включаются и отключаются горизонтальная и вертикальная полосы прокрутки, а в группе «Показать параметры для следующего листа» — отображение границ ячеек (сетки) и заголовков строк и столбцов.

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

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

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

Количество листов в книге можно в любой момент увеличить или уменьшить. Для добавления листа следует щелкнуть по кнопке «Вставить лист» или использовать «горячие» клавиши SHIFT+F11.

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

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

Отменить удаление листа невозможно, поэтому хорошенько подумайте перед удалением.

Перемещение, копирование, удаление можно производить одновременно для нескольких листов. Для этого их необходимо выделить. Для выделения нескольких листов щелкайте по ярлычку каждого листа, удерживая нажатой клавишу CTRL. Чтобы выделить несколько листов, расположенных рядом, достаточно щелкнуть по ярлычку первого и последнего листа, удерживая нажатой клавишу SHIFT. Чтобы выделить все листы, выберите из контекстного меню любого ярлычка листа команду Выделить все листы. Если в книге выделено несколько листов, в строке заголовка рядом с именем файла появится пометка [Группа]. Чтобы снять выделение нескольких листов, щелкните по ярлычку любого невыделенного листа или выберите команду «Разгруппировать листы» из контекстного меню ярлычка любого выделенного листа.

В контекстном меню или на вкладке ленты Главная в группе Ячейки меню кнопки Формат с помощью команды « Цвет ярлычка » можно раскрашивать ярлычки листов в разные цвета. Эту операцию можно производить и с несколькими листами, предварительно выделив их. С помощью кнопки Подложка (вкладка ленты Разметка страницы, группа Параметры страницы ) можно задать рисунок в качестве фона рабочего листа.

Каждый лист — это большая таблица со множеством ячеек, в которые заносятся данные. Одна из ячеек выделена жирной рамкой. Это так называемая текущая или активная ячейка. Переходить от одной ячейки к другой можно, используя клавиши управления курсором или мышь. Годится также клавиша TAB. Без нажатой клавиши SHIFT идет передвижение вправо, с нажатой — влево.

Ячейки, расположенные рядом, образуют строку, ячейки расположенные одна под другой образуют столбец. Каждая ячейка формируется пересечением строки и столбца. Чтобы можно было отличать одну ячейку от другой, каждая ячейка имеет так называемый адрес, состоящий из номера столбца и номера строки, на пересечении которых она находится. Строки нумеруются арабскими цифрами, а столбцы латинскими буквами. Адрес или ссылка  — это имя столбца и номер строки, на пересечении которых находится ячейка. Например, ячейка, находящаяся на пересечении столбца В и строки 5, имеет адрес В5. Адреса используются при записи формул в качестве ссылок на ячейки. Рабочий лист Excel напоминает декартову систему координат, только оси располагаются немного по-другому. Адрес текущей ячейки всегда отображается в поле имен. Это крайнее левое окошко в строке формул.

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

Строк на каждом листе 1 048 576, столбцов 16 384. Так как в английском языке только 26 букв, то после Z нумерация столбцов идет сдвоенными буквами AA, AB, AC, …, GA, GB, GC, …, HX, HY, HZ, а после столбца ZZ  — строенными ААА, ААВ, ААС,…, AAZ , ABA ,…. Заканчивается она на столбце  XFD. Прокрутите текущий рабочий лист с помощью горизонтальной полосы прокрутки до конца вправо, чтобы ознакомиться с обозначениями столбцов в Excel. Чтобы быстро перейти к первой или последней строке (столбцу) рабочего листа, можно нажать клавишу CTRL и соответствующую клавишу управления курсором.

1.2. Возможности Excel при работе с функциями

MS Excel (электронные таблицы) – одно из наиболее часто используемых приложений пакета MS Office, мощнейший инструмент в умелых руках, значительно упрощающий рутинную повседневную работу. Основное назначение MS Excel – решение практически любых задач расчетного характера, входные данные которых можно представить в виде таблиц. Применение электронных таблиц упрощает работу с данными и позволяет получать результаты без программирования расчётов. В сочетании же с языком программирования Visual Basic for Application (VBA), табличный процессор MS Excel приобретает универсальный характер и позволяет решить вообще любую задачу, независимо от ее характера.

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

Назначение и область применения электронных таблиц:

- бухгалтерский и банковский учет;

- планирование распределение ресурсов;

- проектно-сметные работы;

- инженерно-технические расчеты;

- обработка больших массивов информации;

- исследование динамических процессов.

Основные функциональные возможности:

- анализ и моделирование на основе выполнения вычислений и обработки данных;

- оформление таблиц, отчетов;

- форматирование содержащихся в таблице данных;

- построение диаграмм требуемого вида;

- создание и ведение баз данных с возможностью выбора записей по заданному критерию и сортировки по любому параметру;

- перенесение (вставка) в таблицу информации из документов, созданных в других приложениях, работающих в среде Windows;

- печать итогового документа целиком или частично;

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

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

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

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

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

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

Типы функций:

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

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

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

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

Функции просмотра и ссылок  позволяет «просматривать» информацию, хранящуюся в списке или таблице, а также обрабатывать ссылки.

Финансовые

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

Например, функция ПЗ (в XP переименована в ПЛТ "плата") позволит рассчитать сумму, которую придется выплатить, взяв (или, наоборот, получить, предоставив кому-то) кредит. Причем в качестве аргументов используются Норма (норма прибыли или годовая процентная ставка), Кпер (количество периодов погашения), Выплата (фиксированные ежемесячные или ежегодные платежи - в счет погашения кредита плюс проценты (в XP это называется Пс "приведенная стоимость")) и Бс (будущая стоимость; равна нулю, если вы рассчитываете за этот период полностью отдать (получить) долг), Тип - равен 1, если выплаты происходят в начале периода, и 0, если в конце.

Обратными и родственными к ПЗ являются: функции КПЕР, позволяющая рассчитать время (количество периодов) погашения; функция БЗ (БС) - будущее значение (будущая стоимость) вклада или кредита; функция ПЛПРОЦ (ПРОЦПЛАТ) - платежи по процентам и т.д. и т.п.

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

Дата и время

Большинство функций этой категории ведает преобразованиями даты и времени в различные форматы. Две специальные функции СЕГОДНЯ и ТДАТА вставляют в ячейку текущую дату (первая) и дату и время (вторая), обновляя их при каждом вызове файла или при внесении любых изменений в таблицу. Такую ячейку стоило бы иметь в бланках счетов, самых свежих прайс-листах, каких-нибудь типовых договорах: вы просто распечатываете файл, а текущая дата ставится сама.

Логические

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

=ЕСЛИ (логическое_условие; когда_неверно)

В качестве логического условия выступают равенства и неравенства с использованием знаков > (больше), < (меньше), = (равно), >=(больше или равно), <=(меньше или равно), <> (не равно). Пример: =ЕСЛИ (С1>D1*B5; ”УРА! ”; ”УВЫ…”) - если число в ячейке С1 больше, чем произведение D1 и В1, то в нашей ячейке будет радость, а если меньше - разочарование. В функцию ЕСЛИ может быть вложена другая функция ЕСЛИ, а в нее еще одна - "и так семь раз". Пример: =ЕСЛИ (С1>100; ”УРА! ”; ЕСЛИ(Е1=1; G1; G2)) - если ячейка С1 больше ста, то в нашей ячейке будет написано "Ура! ", а если меньше либо равна - то в нее скопируется содержимое ячеек G1(при Е1, равном 1) или G2 (при Е1, не равном 1)

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

=И(логическое_условие_1; логическое_условие_2)

Всего логических условий может быть до 30 штук. Пример совместного использования функций ЕСЛИ и И:

ЕСЛИ(И(Е1>1; G2=”Ура! ”); ”Угадал”; ”Не угадал”) - если ячейка Е1 больше единицы, а в G2 находится слово "Ура! ", то в нашей ячейке окажется слово "Угадал" (истина), если же какое-то из логических условий не выполнено (ложь), получим "Не угадал".

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

Команда НЕ инвертирует, переворачивает полученное значение: была истина, станет ложь, и наоборот.

Пример: =ЕСЛИ(НЕ(С1>D1*B5); ”УРА! ”; ”УВЫ…”) - "УРА!" появляется, когда С1 не больше D1*B5.

Математические

Категория Математические (Мат. И тригонометрия) - одна из самых густонаселенных в Excel (60 команд в 5.0 и 50 в более поздних). Никаких особых хитростей в ней нет. Если SIN, значит, синус, если ASIN, значит, арксинус. Если LN - то натуральный логарифм, если LOG10 - то десятичный, а если LOG - то по произвольному основанию.

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

Ссылки и массивы

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

Текстовые

В этой группе десятка два команд! С их помощью мы сможем посчитать количество символов в ячейке, включая пробелы (ДЛСТР), узнать код символа (КОД-СИМВ), узнать, какой символ стоит первым (ЛЕВСИМВ) и последним (ПРАВСИМВ) в строке текста, поместить в активную ячейку некоторое количество символов из другой ячейке (ПСТР), поместить в активную ячейку весь текст из другой ячейки заглавными (ПРОПИСН) или сточными буквами (СТРОЧН), проверить, совпадают ли две текстовые ячейки (СОВПАД), найти некоторый текст (ПОИСК, НАЙТИ) и заменить его другим (ЗАМЕНИТЬ).

Проверка свойств и значений

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

Работа с базой данных

Функция для обсчета данных в базе были впервые выделены в отдельную категорию в Excel 2007. Здесь можно найти команды статистического обсчета (БДДИСП - дисперсия по выборке из базы, БДДИСПП - дисперсия по генеральной совокупности, ДСТАНДОТКЛ - стандартное отклонение по выборке), операции со столбцами и строками базы, количество непустых (БСЧЕТА) или (БСЧЕТ) ячеек и т.д.

Электронные таблицы.

Строки каждой таблицы пронумерованы цифрами, а столбцы - буквами. Таким образом, у каждой ячейки на листе есть свой уникальный адрес (имя). Например, выделенная рамочкой ячейка в левом верхнем углу имеет адрес А1. если же мы хотим обратиться к этой ячейке из другого листа книги, то в адрес добавится название данного листа: Лист 1! А 1 (восклицательный знак служит разделителем). Более того, если мы захотим обратиться к ячейке из другого файла, в адрес добавится еще и его имя и путь к нему.

То, что ячейка А1 выделена рамочкой, означает, что она активна и можно немедленно что-нибудь в нее ввести с клавиатуры. В каждой ячейке таблицы может находиться число, текст либо формула. Это не все, что может находиться в ячейке, но самое существенное.

Простейшая формула - это, например, сумма. Объяснив программе, что именно надо суммировать, мы в заданной ячейке получаем результат. Конечно увидим мы в ней число, но в отличие от "просто числа" это будет вычисленным. И если измениться значение каких-либо ячеек, входящих в область суммирования, то автоматически поменяется и результат.

Поставим курсор на ячейку А1 и введем число, какое нам вздумается. Закончив, нажмем Enter (или стрелку вниз) и окажемся в ячейке А2. введя таким образом пяток чисел, в ячейке А6 напишем формулу для их суммирования: =А1+А2+А3+А4+А5 и нажмем на Enter (букву А надо вводить в английской раскладке клавиатуры). Excel посчитает сумму и запишет ее в ячейку А6. Суммирование, впрочем, лучше выполнять по-другому, с привлечением к общественно полезному труду стандартных функций Excel. Для этого выделим мышкой ячейки с А1 по А5 (это и будет область суммирования), а потом нажмем кнопку суммирования (сигму) на панели инструментов. В ячейке А6 тут же появится вычисленная сумма.

Стоит теперь перейти на любую из ячеек с А1 по А5 и ввести другое число, как сумма тут же изменится. Конечно, изменится она после того, как вы закончите набор и нажмете Enter, Tab, любую стрелку или ткнем мышкой в другую ячейку.

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

Чтобы увидеть, как выглядит формула, созданная с помощью кнопки-сигмы, перейдем стрелками или встанем мышью на эту ячейку и поглядим на строку формул. Слева в отдельном окошечке (оно называется полем имени) написано: А6. Этот адрес (имя) ячейки, где мы находимся. А рядом стоит и сама формула: =СУММА(А1: А5). СУММ (в англоязычной версии SUM) - это операция суммирования, а в скобках написано, что суммируются ячейки в интервале от А1 до А5.

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

Если поставить мышкой курсор в строку формул, можно будет что-то поправить в ней вручную. А если дважды щелкнуть по ячейке А6 или нажать F2, то править можно будет прямо в самой ячейке, а в строке формул наши действия будут продублированы. Excel XP, особенно услужливый, тут же покажет желтенькую справку-подсказку с описанием синтаксиса команды. Если я захочу, чтобы в область суммирования входили также данные из столбцов В и С. Тогда я могу написать в ячейке А6 =СУММА(А1: С5), задавая суммирование по прямоугольнику, верхняя левая вершина которого - в ячейке А1, а нижняя правая - в С5.

Если нужно считать сумму по всему столбцу (вы собираетесь впоследствии добавлять новые данные в таблицу, но не желаете каждый раз переписывать формулу), то в качестве аргумента можно задать, скажем, А: А. При этом произойдет сложение всех ячеек столбца и результат будет помещен в ячейку… В какую? Ведь если сумму записать в этот же столбец (например, в ячейку А6, как мы делали до сих пор), то она тоже попадает в область суммирования. Excel должен записать в А6 вычисленную сумму, но ему тут же придется добавить и это число к сумме, записав новый результат туда же, в А6. Вместо этого Excel сообщит, что не правы, скорее всего, вы. Невозможно, напишет, допустить циклические ссылки. Так что сумму по всему столбцу или по строке (в этом случае пишем так: = СУММ(1:1)) надо помещать в другом столбце или в другой строке.

При выполнении итерационных вычислений, начиная с Excel 2007, циклические ссылки допустимы. Но это достаточно сложные материи.

Если нам нужно просуммировать только столбцы А и С, а В пропустить. Тогда мы пишем = СУММ(А1: А5; С1: С5). Двоеточие задает интервал, точка с запятой - перечисление.

На самом деле все описанные выше операции можно проделать и мышкой. Например, встать в ячейку D6, где будет находиться сумма, нажать на сигму - в ячейке сразу возникнет незаполненная формула = СУММ(), а потом мышкой выделить либо прямоугольный блок А1: А5 (сумма по трем столбцам), либо сперва ячейки с А1 по А5, а затем, держа нажатой клавишу Ctrl, - с С1 по С5 (сумма по двум столбцам вразбивку). Можно даже разом выделить весь столбец - щелкнув мышкой по его имени, например по букве А вверху столбца. Строка выделяется аналогично - щелчком по ее номеру слева.

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

Иногда возникает необходимость в формуле использовать данные из другой таблицы. Так, для того чтобы прибавить к нашей сумме ячейку А1, находящуюся на четвертом листе, пишем: = СУММ(А1: А5) +Лист4! А1.

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

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

Когда мы выделяем мышкой ячейки, расположенные не в столбик, а в строку, сумма появится не внизу, а справа от выделенной группы ячеек. Формула будет выглядеть примерно так: =СУММ(А1: D1). Если мы выделим мышкой прямоугольную область, например первые пять ячеек столбцов А, В и С, и нажмем на сигму. Это позволит вычислить сумму по каждому из столбцов и напишет в свободные ячейки внизу.

Если же нам хочется подобным образом посчитать сумму по каждой строке этого прямоугольника, то, выделяя блок А1: С5, надо захватить и пустой столбик D(А1: D5). Тогда при нажатии кнопки суммирования в столбце D окажутся суммы, вычисленные по строкам. Если выделить, кроме блока с данными, также пустой столбец справа и пустую строку снизу и нажать на сигму, то Excel напишет нам суммы и по строкам, и по столбцам.

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

Мастер диаграмм

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

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

И наконец, задаем, куда диаграмму следует поместить - на отдельный лист или на один из имеющихся. Жмем кнопку Готово и получаем то, что заказали.

Готовую диаграмму, если она таки совсем оправдала наши ожидания, можно еще подредактировать. Щелкаем по любому элементу диаграммы, и он выделяется (отмечается черными квадратиками), щелкаем по пустому месту внутри диаграммы, и она выделится вся. Теперь можно выделенный элемент или всю диаграмму скопировать или удалить (Del), передвинуть мышью в нужное место, уменьшить или растянуть (за черные квадратики).

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

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

Диаграмма динамически связана с исходными данными. Если мы что-то в таблице поменяли, то изменится и диаграмма. А если что-то испортили (возникла ошибка), то и диаграмма испортится, и в ней появятся сообщения об ошибках.

1.3. Виды функций используемые в Excel

Математические функции

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

Функция СУММ

Функция СУММ суммирует множество чисел.  Эта функция имеет следующий синтаксис: =СУММ(числа).

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

Поскольку СУММ является очень популярной функцией, на стандартной  панели инструментов имеется специальная кнопка для ввода этой функции. Если вы выделите ячейку и нажмете кнопку Автосумма (кнопка с символом (), Ехсеl создаст формулу =СУММ() и предложит ячейки для суммирования. Например, если  выделить ячейку   С16  в  листе,   представленном  ранее на рис. 1, и затем нажать кнопку Авто сумма, Excel предложит формулу =СУММ(С4:С15) и выведет подвижную рамку вокруг диапазона ячеек, использованного в формуле в качестве аргумента.

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

Функция ОКРУГЛ

Функция ОКРУГЛ округляет число, задаваемое ее аргументом, до указанного количества десятичных разрядов и имеет следующий синтаксис: =ОКРУГЛ(число;количество_цифр)

Аргумент число может быть числом, ссылкой на ячейку, в которой содержится число, или формулой, возвращающей числовое значение. Аргумент количество_цифр, который может быть любым положительным или отрицательным целым числом, определяет, сколько цифр будет округляться. Задание отрицательного аргумента количество_цифр округляет до указанного количества разрядов слева от десятичной запятой, а задание аргумента количество_цифр равным 0 округляет до ближайшего целого числа. Excel округляет цифры, которые меньше 5, с недостатком (вниз), а цифры, которые больше или равны 5, с избытком (вверх). Следующая таблица содержит несколько при меров действия функции ОКРУГЛ.

Функции ОКРУГЛВНИЗ и ОКРУГЛВВЕРХ имеют такой же синтаксис, как и функция ОКРУГЛ. Как указывают их имена, они округляют значения вниз (с недостатком) или вверх (с избытком).

Округление в сравнении с форматированием

Не смешивайте функцию ОКРУГЛ с фиксированными форматами, например 0 и 0,00, которые имеются на вкладке Число окна диалога Формат ячеек. При использовании этих форматов содержимое в ячейке округляется до заданного

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

Функции ЦЕЛОЕ и ОТБР

Функция ЦЕЛОЕ округляет число вниз до ближайшего целого и имеет следующий синтаксис: =ЦЕЛОЕ(число).

Аргумент число — это число, для которого вы хотите найти следующее наи меньшее целое. Например, рассмотрим формулу: =ЦЕЛОЕ(100,01)

Эта формула возвратит значение 100, как и следующая формула, несмотря на то, что число 100,99999999 уже практически равно 101: =ЦЕЛОЕ(100,99999999)

Если число отрицательное, функция ЦЕЛОЕ также округляет это число вниз до ближайшего целого. Например, следующая формула возвращает значе ние -101: =ЦЕЛОЕ(-100,99999999)

Функция ОТБР отбрасывает все цифры справа от десятичной за пятой независимо от знака числа. Необязательный аргумент количество_цифр задает позицию, после которой производится усечение. Эта функция имеет следующий синтаксис: =ОТБР(число; количество_цифр)

Если второй аргумент опущен, он принимается равным нулю. Например, следующая формула возвращает значение 13: =ОТБР(13,978)

Функции СЛЧИС и СЛУЧМЕЖДУ

Функция СЛЧИС генерирует случайные числа, равномерно распределенные между 0 и 1, и имеет следующий синтаксис: =СЛЧИС()

Функция СЛЧИС является одной из функций Excel, которые не имеют аргументов. Как и для всех функций, у которых отсутствуют аргументы, после имени функции необходимо вводить круглые скобки.

Значение функции СЛЧИС изменяется при каждом пересчете листа. Если установлено автоматическое обновление вычислений, значение функции' СЛЧИС изменяется каждый раз при вводе данных в этом листе.

Функция СЛУЧМЕЖДУ, которая доступна, если установлена надстройка, Пакет анализа, предоставляет больше возможностей, чем  СЛЧИС. Для функции СЛУЧМЕЖДУ можно задать интервал генерируемых случайных целочисленных значений. Эта функция имеет следующий синтаксис: =СЛУЧМЕЖДУ(начало;конец)

Аргумент начало задает наименьшее целое число, а аргумент конец — наибольшее целое число, которое может возвратить функция. Например, следующая формула может возвратить любое целое число от 123 до 456 (включая и оба эти значения): =СЛУЧМЕЖДУ(123;456)

Функция ПРОИЗВЕД

Функция ПРОИЗВЕД перемножает все числа, задаваемые ее аргументами, и имеет следующий синтаксис: =ПРОИЗВЕД(число1; число2;...). Функция  ПРОИЗВЕД  может  иметь до  30   аргументов.   Excel   игнорирует любые пустые ячейки, текстовые и логические значения.

Функция КОРЕНЬ

Функция КОРЕНЬ возвращает положительный квадратный корень числа и имеет следующий синтаксис: =КОРЕНЬ(число)

Аргумент число должен быть положительным числом. Например, следующая функция возвращает значение 2: =КОРЕНЬ(4)

Если   число   отрицательное,    КОРЕНЬ   возвращает   ошибочное   значение: =#ЧИСЛО!.

Функция ЕЧИСЛО

Функция ЕЧИСЛО определяет, является ли значение число и имеет следующий синтаксис: =ЕЧИСЛО(значение)

Предположим, что вы хотите узнать, является ли значение в ячейке А5 числом. Следующая формула возвращает значение ИСТИНА, если ячейка А5 содержит число или формулу, возвращающую число; в противном случае она возвращает ЛОЖЬ: =ЕЧИСЛО(А5)

Логарифмические функции

Excel поддерживает пять встроенных логарифмических функций: LOG10, LOG, LN, ЕХР и СТЕПЕНЬ. В этом разделе я расскажу только про функции LOG, LN и ЕХР. Надстройка Пакет анализа предоставляет еще несколько дополнительных и более сложных логарифмических функций.

Функция LOG

Функция LOG возвращает логарифм положительного числа по заданному основанию. Эта функция имеет следующий синтаксис: =LOG(число; основание')

Например,  следующая  формула  возвращает значение 2,321928095,  то есть логарифм 5 по основанию 2: =LOG(5; 2)Если вы не укажете аргумент основание, Excel примет его равным 10.

Функция LN

Функция LN возвращает натуральный (по основанию е) логарифм положительного числа, указанного в качестве аргумента. Эта функция имеет следующий синтаксис: =LN(число). Например, следующая формула возвращает значение 0,693147181: =LN(2)

Функция ЕХР

Функция ЕХР вычисляет значение константы е (приблизительно 2,71828183), возведенной в заданную степень. Эта функция имеет следующий синтаксис: =ЕХР(число)Например,     следующая     формула     возвращает     значение     7,389056099 (12,718281828 x 2,718281828): =ЕХР(2). Функция   ЕХР  является  обратной  по отношению к  LN.   Например, пусть ячейка А1 содержит формулу =LN(8), тогда следующая формула возвращает значение 8: =ЕХР(А1)

Тригонометрические функции

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

Функция ПИ

Функция ПИ возвращает значение константы ( с точностью до 14 деся тичных знаков: 3,14159265358979. Эта функция имеет следующий синтаксис: =ПИ(). Функция ПИ не имеет аргументов, но мы все равно должны вводить после имени функции круглые скобки.

Функции РАДИАНЫ и ГРАДУСЫ

Тригонометрические функции используют углы, выраженные в радианах, a не в градусах. Измерение углов в радианах основывается на константе ( и при этом 180 градусов равны ( радиан. Excel предоставляет две функции, РАДИАНЫ и ГРАДУСЫ, чтобы облегчить работу с тригонометрическими функциями. Вы можете преобразовать радианы в градусы, используя функцию ГРАДУСЫ, которая имеет следующий синтаксис: =ГРАДУСЫ(угол). Здесь угол — это число, представляющее собой угол, измеренный в радианах. Для преобразования градусов в радианы используется функция РАДИАНЫ которая имеет следующий синтаксис: =РАДИАНЫ(угол). Здесь угол — это число, представляющее собой угол, измеренный в градусах. Например, следующая формула возвращает значение 180: =ГРАДУСЫ(3,1415927). В то же время следующая формула возвращает значение 3,1415927: =РАДИАНЫ(180)

Функция SIN

Функция SIN возвращает синус угла и имеет следующий синтаксис: =SIN(число) Здесь число — это угол в радианах. Например, следующая формула возвра щает значение 0,997494987: =SIN(1,5)

Функция COS

Функция COS вычисляет косинус угла и имеет следующий синтаксис: =COS(число)

Здесь число — это угол в радианах. Например, следующая формула возвра щает значение 0,070737202: =COS(1,5)

Функция TAN

Функция TAN вычисляет тангенс угла и имеет следующий синтаксис: =TAN(число). Здесь число — это угол в радианах. Например, следующая формула возвращает тангенс угла 1,5 радиана, равный 14,10141995: =TAN(1,5)

 Инженерные функции

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

Логические функции

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

Логические выражения

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

=А1>А2;=5-3<5*2;=СРЗНАЧ(В1:В6);=СУММ(6; 7; 8);=С2="Среднее" =СЧЁТ(А1:А10);=СЧЁТ(В1:В10);=ДЛСТР(А1)=10

Любое логическое выражение должно содержать, по крайней мере, один опе ратор сравнения, который определяет отношение между элементами логичес кого выражения. Например, в логическом выражении А1>А2 оператор больше (>) сравнивает значения в ячейках А1 и А2. Следующая таблица содержит список операторов сравнения Excel.

Список операторов сравнения Microsoft Excel.                                 

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0). Например, следующее логическое выражение возвращает значение ИСТИНА, если значение в ячейке Z1 равно 10, и ЛОЖЬ, если Z1 содержит любое другое значение: =Z1=10

Функция ЕСЛИ

Функция ЕСЛИ имеет следующий синтаксис:

=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)

Например, следующая формула возвращает число 5, если значение в ячей ке А6 меньше 22: =ЕСЛИ(А6<22;5;10). В противном случае формула возвращает 10.

В качестве аргументов функции ЕСЛИ можно использовать другие функции. Например, следующая формула возвращает сумму значений в ячейках от А1 до А10, если эта сумма положительна: =ЕСЛИ(СУММ(А1:А10)>0;СУММ(А1:А10); 0). В противном случае формула возвращает 0.

Функции И, ИЛИ и НЕ

Три дополнительные функции — И, ИЛИ и НЕ - по зволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения: =, >, <, >=, <= и <>. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют следующий синтаксис:

=И(логическое_значение1;логическое_значение2;... ;логическое_значениеЗО) =ИЛИ(логическое_значение1;логическое_значение2;... ;логическое_значениеЗО) Функция НЕ имеет только один аргумент и следующий синтаксис: =НЕ(логическое_значенне)

Аргументы функций И, ИЛИ и НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Вложенные функции ЕСЛИ

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

=ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100);"Обычно";

ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Увы!")))

Если значение в ячейке А1 является целым числом, формула читается следу ющим образом: «Если значение в ячейке А1 равно 100, возвратить строку Всегда. В противном случае, если значение в ячейке А1 находится между 80 и 100 (точнее, от 80 до 99 включительно), возвратить строку Обычно. В про тивном случае, если значение в ячейке А1 находится между 60 и 80 (от 60 до 79 включительно), возвратить строку Иногда. И наконец, если ни одно из этих условий не выполняется, возвратить строку Увы!».

Всего допускается до семи уровней вложения функций ЕСЛИ, но при этом, конечно, должно соблюдаться ограничение по максимальной длине значения в ячейке (255 символов).

Функции ИСТИНА и ЛОЖЬ

Функции ИСТИНА и ЛОЖЬ предоставляют альтернатив ный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом: =ИСТИНА(), =ЛОЖЬ(). Например, предположим, что ячейка В5 содержит логическое выражение, тогда следующая формула возвратит строку Внимание!, если логическое вы ражение в ячейке В5 имеет значение ЛОЖЬ: =ЕСЩВ5=ЛОЖЬ(); "Внимание!"; "ОК"). Иначе формула возвратит строку ОК.

2. Использование условных функций и логических выражений

при решении задач в Excel

2.1. Условная функция и логические выражения в  электронных

таблицах Microsoft Excel

Условная функция. Общий вид условной функции следующий:

ЕСЛИ(<условие>; <выражение1>; <выражение2>)

Условие — это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ. <выражение 1> и <выражение 2> могут быть числами, формулами или текстами.

Условная функция, записанная в ячейку таблицы, выполняется так: если условие истинно, то значение данной ячейки определит <выражение 1>, в противном случае — <выражение 2>.

Логические выражения. Логические выражения строятся с помощью операций отношения (<, >, <= (меньше или равно), >= (больше или рано), =, <>(не равно)) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ). Результатом вычисления логического выражения являются логические величины ИСТИНА или ЛОЖЬ.

Существуют особенности записи логических операций в табличных процессорах: сначала записывается имя логической операции (И, ИЛИ, НЕ), а затем в круглых скобках перечисляются логические операнды.

И (логическое_значение1;                     Возвращает значение ИСТИНА, если все аргументы

Логическое_значение2; …)                   имеют значение ИСТИНА;   возвращает значение 

(AND)                                                      ЛОЖЬ, если хотя бы один  аргумент имеет значение ЛОЖЬ; например,   И(2+2=4; 2+3=5) равняется ИСТИНА, если  ячейка B4 содержит число между 1 и 100, то  И(1равняется ИСТИНА

ИЛИ(логическое_значение1;                Возвращает значение ИСТИНА, если хотя бы  один

Логическое_значение2; …)                  аргументов имеет значение ИСТИНА  и возвращает

(OR)                                                        значение ЛОЖЬ, если все  аргументы имеют  значение ЛОЖЬ;  например, ИЛИ(2+2=4; 2+3=6) возвращает ИСТИНА; если ячейка B4 содержит число меньше 1 или больше 100, то ИЛИ(1100) возвращает   ИСТИНА.

НЕ (логическое_зачение) (NOT)         Меняет на противоположное логическоезначение своего аргумента; например  НЕ(2+2=5) возвращает ИСТИНА; если ячейка  B4 содержит число меньше 1 или больше 100, то  НЕ(ИЛИ(1100)) возвращает ЛОЖЬ

Вложенные логические функции ЕСЛИ.

Формат записи:

        =ЕСЛИ(усл.1; выражение В; ЕСЛИ(усл.2; выражение С; ЕСЛИ(…))).

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

Пример 1. Найти работников, у которых имеются одновременно задолжен-ности по обоим видам кредита, и удержать от начисленной им суммы 20% в счет погашения кредитов. С остальных работников, имеющих задолженность по какому-либо одному виду кредита, удержать 10% от начисленной им суммы. Работникам, не имеющим задолженности по кредиту, проставить в графе “Удержано”- Б/К. В нашем примере логическая функция будет иметь следующий вид:

        =ЕСЛИ(И(С3>0;D3>0);B3*0,2;ЕСЛИ(И(С3=0;D3=0);”б/к”;B3*0,1))

        Данная логическая функция означает, что если одновременно задолженности по потребительскому кредиту на жилищное строительство больше нуля, то необходимо удержать 20% с начисленной суммы, если обе задолженности одновременно равны нулю, то необходимо вывести “б/к”, в противном случае необходимо удержать 10% от начисленной суммы.

Пример 2. Разработать таблицу, содержащую следующие сведения об абитуриентах: фамилия, оценки за экзамены по математике, русскому и иностранному языкам, сумма баллов за три экзамена и информацию о зачислении: если сумма баллов больше или равна проходному баллу и оценка за экзамен по математике — 4 или 5, то абитуриент зачислен в учебное заведение, в противном случае — нет.

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

Первоначально следует подготовить таблицу в следующем виде:

В ячейке С1 будет храниться значение проходного балла — 13. Формула в ячейке ЕЗ вычисляет сумму баллов за три экзамена: ВЗ + СЗ + D3. Формула в ячейке F3 задается с помощью условной функции: ЕСЛИ(И(ЕЗ>=$С$1;ВЗ>3);”ДА”;”НЕТ”)

Условие, записанное с помощью логической операции И, можно расшифровать так: сумма баллов (ЕЗ) >= проходному баллу (С1). И оценка за экзамен по математике (ВЗ) > 3. Если условие выполняется, то в клетке F3 будет отображаться текст — ДА, в противном случае — НЕТ.

Для проходного балла в формуле используется абсолютный адрес $С$1, так как проходной балл является одинаковым и неизменным для всех абитуриентов. После заполнения 3-ей строки формулами, можно произвести копирование соответствующих формул в нижние строки. Формулы в столбцах Е и F после копирования будут выглядеть так:

После ввода исходных данных получим таблицу в режиме отражения значений:

2.2.Решение задач в Excel с использованием условных функций и логических выражений

Задача

Компания по снабжению электроэнергией взимает плату плату с клиентов по тарифу: k рублей за 1 КВт/ч и m рублей за каждый КВт/ч сверх нормы, которая составляет 50 Квт/ч. Услугами компании пользуются 10 клиентов. Подсчитать плату для каждого клиента.

Решение:  

Задача

10 спортсменов-многоборцев принимают участие в соревнованиях по 5 видам спорта. По каждому виду спорта спортсмен набирает определенное количество очков. Спортсмену присваивается звание мастер, если он набрал в сумме не менее k очков. Сколько спортсменов получило звание мастера?

Решение:

Заключение

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

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

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

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

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

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

В качества аргументов в формуле обычно используются числа и адреса ячеек. Для обозначения арифметических операций могут использоваться следующие символы: + (сложение); – (вычитание); * (умножение); / (деление).

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

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

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

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

Список использованной литературы

1. Microsoft Office Excel 2003. Учебный курс / В. Кузьмин, - СПб.: Питер: Издательская группа BHV, 2009.
2. Excel 2003. Эффективный самоучитель / В.В. Серогородский, - СПб.: Наука и техника, 2005.
3. Excel: Сборник примеров и задач / С.М. Лавренов, - М.: Финансы и статистика, 2009.
4. Васильева В.С. Персональный компьютер. Быстрый старт. – СПб.: БХВ-Петербург, 2010.
5. Ефимова М.Р., Бычкова С.Г. Социально-экономическая статистика. – М.: Финансы и статистика – 2010.
6. Колесникова И.И. Социально-экономическая статистика. – М.: Новое знание – 2002.
7. Куликов Е. Прикладной статистический анализ. – М.: Радио и связь, 2009.
8. Макаров А.А., Кулаичев А.П., Синева И.С. Использование программ обработки данных в преподавании курсов теории вероятностей, математической и прикладной статистики и информатики. Методические рекомендации (выпуск 1). – М.: МГУ, 2009.
9. Мидлтон М. Р. Анализ статистических данных с использованием Microsoft Excel для Office XP. – М.: Бином: 2010.
10. Минько А.А. Статистический анализ в Microsoft Office Excel. Профессиональная работа. М.: Вильямс, 2010.
11. Мхитарян В.С. Пакет анализа MS Excel в экономико-статистических расчетах. Юнити-Дана, 2010.
12. Статистика / Под ред. Ефимова М.Р. – М.: ИНФРА-М, 2009.
13. Чекотковский Э. В. Графический анализ статистических данных в Microsoft Excel 2000. – М.: Диалектика, 2009. 464 с.
14. Чернова Т.В. Экономическая статистика. – Таганрог: Изд-во ТРТУ, 2005.