Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
ТАБЛИЧНЫЙ ПРОЦЕССОР EXCEL
Введение
Табличный процессор – комплекс взаимосвязанных программ, предназначенный для обработки электронных таблиц. Электронная таблица – компьютерный эквивалент обычной таблицы, состоящей из строк и столбцов, на пересечении которых располагаются ячейки, в которых содержится числовая информация, формулы или текст. Это наиболее удобная форма представления данных. Особенность электронных таблиц заключается в возможности применения формул для описания связи между значениями различных данных, расчёт по которым выполняется автоматически. Изменение любого входного данного приводит к пересчёту всех связанных с ним значений, при этом происходит обновление всей таблицы.
В составе табличных процессоров имеются встроенные математические функции и алгоритмы статистической обработки данных, мощные средства для связи таблиц между собой, создания и редактирования электронных баз данных. Специальные средства позволяют автоматически получать и распечатывать настраиваемые отчёты с использованием десятков различных типов таблиц, графиков, диаграмм, снабжать их комментариями и графическими иллюстрациями. Кроме того, табличный процессор имеет простую систему настройки и управления, развитую систему контекстных справок, меры защиты от неправильных действий пользователя.
Применение электронных таблиц упрощает работу с данными и позволяет получать результаты без проведения расчётов вручную или специального программирования.
Одним из наиболее распространённых средств для работы с документами, имеющими табличную структуру, является программа MICROSOFT EXCEL. Получение навыков работы с табличным процессором в данных методических указаниях рассматривается в среде MS EXCEL 2003.
Введенные сокращения
Список сокращений
По мере изложения материала в данном методическом пособии используются следующие сокращения:
ЛКМ – левая кнопка мыши
2ЛКМ – двойной щелчок по левой кнопкой мыши
ПКМ – правая кнопка мыши
УМ — указатель мыши
ПКМ — правая кнопка мыши
VB — Visual Basic
Специальные символы - в данном методическом пособии используются следующие специальные символы:
à символ, указывающий на последовательность выполняемых действий.
ТЕХНОЛОГИЯ СОЗДАНИЯ И ОФОРМЛЕНИЯ ЭЛЕКТРОННЫХ ТАБЛИЦ
ЗАПУСК EXCEL
Запуск табличного процессора EXCEL можно осуществлять одним из следующих способов:
ИНТЕРФЕЙС EXCEL
Для создания Рабочей книги используется команда ФАЙЛ→СОЗДАТЬ или кнопка СОЗДАТЬ на панели инструментов стандартная. Структура Рабочей книги использует один из готовых шаблонов, который либо задаётся по умолчанию, либо его параметры задаёт пользователь. Структура рабочего листа будет соответствовать рисунку 2.1.
Рис.2.1. Окно программы EXCEL
Рабочая книга – это файл, создаваемый в табличном процессоре EXCEL. Наиболее распространённый способ хранения информации в виде файла с расширением .XLS.
Рабочая книга может содержать 255 листов различных типов, расположенных в произвольном порядке. Рабочий лист имеет матричную структуру – состоит из ячеек, расположенных на пересечении строк и столбцов. Размер рабочего листа определяется системой по умолчанию. Максимально возможное число столбцов – 256.Столбцы озаглавлены латинскими буквами от A до IV. Строки последовательно нумеруются цифрами от 1 до 65536. Листы – служат для организации и анализа данных. Можно вводить и изменять данные одновременно на нескольких листах, а также выполнять вычисления на основе данных из нескольких листов.
Ярлыки рабочих листов содержат имена рабочих листов и используются для выбора нужного листа рабочей книги. Для переименования рабочего листа необходимо: Щелкнуть ПКМ на ярлычке листа и в появившемся контекстном меню выбрать команду ПЕРЕИМЕНОВАТЬ. Добавление и удаление рабочих листов выполняется аналогичным образом, выбирая пункты ДОБАВИТЬ и УДАЛИТЬ соответственно.
Ячейка – минимальный элемент хранения данных. Образуется на пересечении столбцов и строк таблицы. Одна из ячеек всегда является активной (текущей) и выделяется рамкой. Все операции производятся в активной ячейке.
Адрес – обозначение ячейки, сочетающее в себе имя столбца и номер строки, на пересечении которых она расположена, например B15. Адреса ячеек используются при записи формул.
Строка заголовка отображает информацию об имени программы и имени активной рабочей книги. При открытии новой рабочей книги ей присваивается временное имя Книга N, где N – порядковый номер книги с момента очередного запуска программы.
Строка МЕНЮ содержит пункты, в которых тематически сгруппированы все команды, имеющиеся в распоряжении пользователя.
Панели инструментов – это набор кнопок, обеспечивающих быстрый доступ к командам, которые также доступны из окна главного меню. По умолчанию на экране видны две панели инструментов: Стандартная (содержит элементы управления файловыми операциями и редактированием) и Форматирование (элементы управления экранного отображения).
Строка формул – служит для ввода и редактирования значений или формул в ячейках или на диаграммах. На ней отображается константа или формула, содержащаяся в активной ячейке. В левой части строки формул находится раскрывающийся список – поле имени, в котором отображается адрес или имя выделенной (активной) ячейки или блока ячеек таблицы. Между полем имени и строковым полем для ввода и редактирования во время ввода данных появляются три кнопки для управления процессом ввода.
Блок (Диапазон) ячеек – прямоугольная область смежных или несмежных (расположенных в разных местах) ячеек.
Контекстное меню – содержит команды, которые применяются наиболее часто к выделенным элементам или объектам. Содержание этих меню зависит от контекста обращения к ним. Для вызова контекстного меню необходимо подвести указатель мыши к элементу или объекту, с которым вы хотите работать, и выполнить щелчок ПКМ (рис.2.2).
рис.2.2. Контекстное меню
Для Рабочей книги стандартные установки определяются командой СЕРВИС→ПАРАМЕТРЫ, путём изменения данных во вкладках диалогового окна ПАРАМЕТРЫ. К примеру, для удаления с рабочего листа сетки таблицы (чтобы увидеть результат форматирования) необходимо выполнить команду СЕРВИС ПАРАМЕТРЫ вкладка ВИД снять флажок СЕТКА.
СОХРАНЕНИЕ EXCEL
Сохранение рабочей книги возможно осуществить одним из следующих способов:
Ввод и редактирование данных может выполняться по-разному в зависимости от типа данных.
В EXCEL тип данных – это тип значения, хранящегося в ячейке. Когда данные вводятся на рабочий лист, EXCEL автоматически анализирует их и определяет тип данных.
В ячейки рабочего листа вводятся два типа данных: постоянные значения (константы) и формулы.
Формулы строятся как выражения для вычисления нового значения. Формулы содержат константы, ссылки на ячейки (адреса или имена), функции, операторы.
Константы делятся на числовые и текстовые (символьные).
Текстовые (символьные) данные имеют описательный характер и представляют собой последовательность букв, цифр, специальных символов, длинной не более 255 символов. При вводе числа, которое должно восприниматься как текст, перед числом следует поставить апостроф ( ’).
По умолчанию, веденный текст автоматически выравнивается в ячейке по левому краю.
Числовые данные могут содержать цифры от 0 до 9, а также спецсимволы:
+ – Е е ( ) . , $ % /
Числовым типом данных описывается любое число, процентное значение, а также значение денежной суммы, даты или времени.
EXCEL автоматически выравнивает числовые значения по правому краю.
Независимо от количества отображаемых разрядов числа хранятся с точностью до 15 разрядов. Если число имеет больше 15 значащих знаков, то разряды после 15-го преобразуются в нули.
В EXCEL дата и время интерпретируются как числа. Используется система отсчета дат с 1 января 1900г (этой дате соответствует число 1). Все остальные даты отсчитываются от этой начальной. При вводе значений даты или времени происходит их автоматическое распознавание, и общий формат ячейки заменяется на встроенный формат даты или времени. Этот метод хранения обеспечивает возможность производить со значениями даты и времени различные вычисления.
Ввод и редактирование данных осуществляется непосредственно в текущей ячейке или в строке формул. Место ввода отмечается курсором. Для ввода в ячейку необходимо выделить нужную ячейку и ввести с клавиатуры необходимую информацию. Вводимые символы отображаются как в ячейке, так и в строке формул. По завершению ввода необходимо нажать клавишу ENTER (при этом произойдёт переход на следующую строку), клавишу табуляции (произойдёт переход в следующий столбец) или кнопку , находящейся в строке формул (курсор останется в текущей ячейке). После этого вводимая информация будет записана в ячейку.
При необходимости отменить операцию ввода данных воспользуйтесь клавишей ESC или кнопкой , находящейся в строке формул.
Если ширина столбца не достаточна для отображения информации, помещенной в ячейку, EXCEL либо отсечет часть информации (если это текст), либо отобразит в ячейке ######. Для нормального отображения содержимого ячейки необходимо увеличить ширину столбца (пп. 2.3.1).
Основные приёмы редактирования информации в ячейке аналогичны рассмотренным выше. Наиболее быстрый и простой способ редактирования — запись новой информации поверх старой. При обнаружении каких-либо несущественных ошибок данные в ячейке лучше не переписывать, а отредактировать необходимую ее часть. Для этого нужно активизировать режим редактирования, выполнив на ячейке двойной щелчок, вследствие чего в ней появится курсор ввода. Перейти в указанный режим позволяет также функциональная клавиша F2.
Часто в операциях обработки используется не отдельная ячейка, а блок (диапазон) ячеек. Ниже приведены некоторые операции с блоками ячеек.
ВЫДЕЛЕНИЕ ДИАПАЗОНА ЯЧЕЕК
Выделенный диапазон ячеек – группа ячеек, с которой можно производить действия как с единым объектом. Активная ячейка выделенного диапазона остается неокрашенной. Остальная часть области отличается от невыделенных частей рабочего листа цветом ячеек и более широкой линией границы.
Диапазон смежных ячеек. В целом, выделение объектов в EXCEL происходит так же, как и в других приложениях Windows. Выделить диапазон смежных ячеек можно с помощью клавиатуры (нажимая клавишу SHIFT и клавиши с изображением стрелок) или мыши (щелкнув ЛКМ и перемещая указатель). Для того чтобы выделить больший диапазон, следует щелкнуть в любом из углов диапазона, затем нажать клавишу SHIFT и щелкнуть на ячейке в противоположном углу диапазона. Блок описывается двумя адресами, разделенными знаком двоеточия - адресом верхней-левой и нижней-правой ячеек.
Диапазон несмежных ячеек. Выделение блока несмежных ячеек выполняется при нажатой клавише CTRL. Операции перемещение, копирование, вставка, удаление выполняются только для блока смежных ячеек.
Вся таблица. Для выделения всей таблицы используется комбинация клавиш CTRL +* (CTRL +звездочка).
Весь рабочий лист. Если необходимо выделить весь рабочий лист, необходимо щелкнуть мышью на пересечении заголовков строк и столбцов или воспользоваться комбинацией клавиш CTRL + A. Если лист содержит данные, CTRL+A выделяет текущую область. Повторное нажатие сочетания клавиш CTRL+A позволяет выделить все ячейки листа.
Столбцы и строки. Чтобы выделить один столбец, достаточно произвести щелчок на его заголовке. Для выделения нескольких смежных столбцов следует активизировать заголовок первого столбца нужного диапазона и, удерживая ЛКМ нажатой, переместить указатель до заголовка последнего столбца диапазона. Строки выделяются аналогичным образом.
Удаление диапазона ячеек
В случае если информация в ячейке больше не нужна, ее можно удалить. Для этого следует выделить ячейки, из которых необходимо удалить информацию и нажать клавишу DELETE или с помощью команды ПРАВКА ОЧИСТИТЬ СОДЕРЖИМОЕ. При удалении содержимого ячеек параметры форматирования сохраняются. Данное обстоятельство следует учитывать и в случае необходимости удалять не только значение, но и параметры форматирования следует воспользоваться командой ПРАВКА ОЧИСТИТЬ ФОРМАТЫ.
Перемещение и копирование диапазона ячеек
При работе с EXCEL использование мыши позволяет значительно упростить процедуры копирования и перемещения информации.
Для перемещения блока ячеек с помощью мыши необходимо выполнить следующие шаги:
Для копирования блока ячеек с помощью мыши необходимо выполнить следующие шаги:
Форматирование – это процесс, который определяет вид рабочих листов и содержащихся в них данных. Возможности форматирования EXCEL позволяют управлять многими аспектами появления данных в таблице. Можно изменить используемый в ячейке шрифт, его размер, начертание, цвет. Кроме того, имеется возможность изменять используемое в ячейке выравнивание данных (как по горизонтали), так и по вертикали, задать перенос по словам и т.д. К содержащей данные ячейке можно применить разнообразные рамки, узоры и цвет фона, а также изменить сетку таблицы, отредактировав ширину столбцов и высоту строк для лучшего представления данных.
При создании таблиц часто приходиться удалять ненужные или добавлять недостающие столбцы или строки. Для корректного отображения содержимого ячейки необходимо управлять размерами столбца и строки.
EXCEL предоставляет возможность настройки ширины строк и столбцов, добавления и удаления строк и столбцов или некоторых диапазонов ячеек, а также объединения диапазона ячеек в одну ячейку.
ИЗМЕНЕНИЕ ШИРИНЫ СТОЛБЦА ИЛИ ВЫСОТЫ СТРОКИ
В случае если информация, размещаемая в ячейке, требует увеличения или уменьшения ширины столбца или изменения высоты строки, необходимо выполнить следующее:
ПРИМЕЧАНИЕ. Для автоматического изменения ширины столбца в соответствии с содержимым ячеек (автоподбор ширины), достаточно сделать двойной щелчок ЛКМ на границе заголовка столбца.
Если необходимо изменить ширину (или высоту) одного или нескольких столбцов (или строк) в соответствии с заданным значением, необходимо выполнить следующие действия:
ПРИМЕЧАНИЕ. Ширина столбца измеряется числом символов, которые помещаются в ячейку данной ширины. Высота строк измеряется в пунктах.
ДОБАВЛЕНИЕ НОВОГО СТОЛБЦА ИЛИ СТРОКИ
Для добавления в таблицу нового столбца или строки, необходимо выполнить следующие действия:
Удаление СТОЛБЦА ИЛИ СТРОКИ
Для удаления столбца или строки, следует выполнить следующие действия:
Вводимая в ячейки EXCEL информация может быть отображена на экране различными способами. Для изменения формы отображения и доступа к информации MS Excel использует средства форматирования и защиты.
Форматирование информации на рабочем листе позволяет создавать удобное представление табличных данных, делая их более привлекательными и наглядными.
EXCEL распознает тип вводимой информации и выбирает форму ее представления или формат самостоятельно. Чтобы изменить формат отображения данных в текущей ячейке или выбранном диапазоне, используют команду МЕНЮ ФОРМАТ→ЯЧЕЙКИ, команду контекстного меню ФОРМАТ ЯЧЕЕК или CTRL +1.
При выборе указанных пунктов на экране появляется диалоговое окно ФОРМАТ ЯЧЕЕК, содержащее ряд вкладок, пользование которыми достаточно просто и не требует особых комментариев (Рис.2.3).
Рис. 2.3. Диалоговое окно ФОРМАТ ЯЧЕЕК
К основным вкладкам диалогового окна Формат ячеек относятся:
Формат влияет только на внешнее представление данных в ячейке, не влияя на фактическое значение, которое Excel использует при вычислениях. Оно отображается в строке формул.
Для форматирования данных также можно воспользоваться панелью инструментов Форматирование. Функции кнопок панели можно узнать из всплывающих подсказок.
Копирование форматов
Часто бывает необходимо скопировать оформление одной ячейки (или диапазона) на другую ячейку (диапазон ячеек). Для копирования формата необходимо выполнить следующие шаги:
Если необходимо отформатировать диапазон ячеек, следует проделать пп. 1 и 2, затем выделить мышью интересующий диапазон. При отпускании клавиши мыши произойдет форматирование всех ячеек данного диапазона.
Автоформатирование
EXCEL предлагает несколько стандартных вариантов оформления таблицы, образцы которых можно предварительно посмотреть. Для выполнения автоформатирования необходимо выделить форматируемую часть таблицы (Применение автоформата возможно к диапазону, в который можно включить и весь лист) и использовать команды меню ФОРМАТ → АВТОФОРМАТ. Кнопка ПАРАМЕТРЫ служит для изменения некоторых настроек автоформата.
2 ЛР ТЕХНОЛОГИЯ ВЫЧИСЛЕНИЙ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ
Числовая обработка внесенных данных в электронную таблицу – одна из главных задач.
Формулы и функции — это основные средства EXCEL по обработке данных и извлечению полезной информации из рабочих книг.
ФОРМУЛЫ
Под формулой в электронной таблице понимают выражение, состоящее из числовых констант, ссылок на ячейки (адреса ячеек, содержимое которых используется в вычислениях) и функций, соединённых знаками математических операций.
Формула в EXCEL всегда начинается со знака равенства (=). Знак равенства свидетельствует о том, что последующие знаки составляют формулу. Элементы, следующие за знаком равенства, являются операндами, которые разделены операторами вычислений. Операндом может быть число, ссылка на ячейку, ссылка на диапазон ячеек, функция, выражение, взятое в скобки. Операторами обозначаются операции, которые следует выполнить над операндами формулы. В EXCEL включено четыре вида операторов: арифметические, текстовые, операторы сравнения и операторы ссылок (табл. 2.1).
Таблица 2.1. Операторы EXCEL
|
Оператор |
Описание |
Пример |
|
Арифметические операторы |
||
|
+ (знак плюс) |
сложение |
= A2 + 5 |
|
– (знак минус) |
вычитание |
= 10 – F2 |
|
/ (косая черта) |
деление |
= C5 / 4 |
|
* (звездочка) |
умножение |
= A5 * F4 |
|
% (знак процента) |
процент |
= 100% |
|
^ (крышка) |
возведение в степень |
=2^3 |
|
Операторы сравнения |
||
|
> (знак больше) |
больше |
A5 = 1 |
|
>= (знак больше или равно) |
не меньше |
B8 >= 3*A9 |
|
< (знак меньше) |
меньше |
A1 < 500 |
|
<= (знак меньше или равно) |
не больше |
C5 <= 10 |
|
<> (знак не равно) |
не равно |
A10 <> 5 |
|
= (знак равенства) |
равно |
A5 = 1 |
|
Текстовый оператор |
||
|
& (амперсанд) |
Оператор конкатенации |
=”Ответственный”&” Иванов И.П.” |
|
Операторы ссылок |
||
|
: (двоеточие) |
Оператор диапазона |
=СУММ (А1:D10) |
|
; (точка с запятой) |
Оператор объединения |
=СУММ(B5:B15;D5) |
|
(пробел) |
Оператор пересечения |
B7:D7 C6:C8 |
Арифметические операторы служат для выполнения таких арифметических операций над числами, как сложение, вычитание, умножение и т.п. Операторы сравнения используются для сравнения двух значений, результатом которого является логическое значение: либо ИСТИНА, либо ЛОЖЬ. Категория текстовых операторов содержит только один оператор конкатенации, который используется для объединения нескольких текстовых строк в одну строку. Операторы ссылок используются для описания ссылок на диапазоны ячеек. Оператор диапазона – задает непрерывный диапазон ячеек. Оператор объединения – объединяет несколько ссылок на ячейки или диапазоны в одну ссылку. Оператор пересечения – создает ссылку на ячейки, общие для двух диапазонов.
При вычислениях по формулам EXCEL придерживается некоторых правил, определяющих последовательность выполнения операторов. Формула вычисляется слева направо в соответствии с определенным порядком для каждого оператора в формуле. В первую очередь вычисляются функции и части, заключенные в круглые скобки, а затем выполняются операции в порядке уменьшения их приоритетов.
Формула является основным средством для анализа данных. Формулы могут ссылаться на ячейки текущего листа, листов той же книги или других книг. Ссылки на ячейки других книг называются внешними ссылками. При написании формул используются относительная и абсолютная ссылки.
Для указания абсолютной ссылки используется символ $, устанавливаемый перед наименованием столбца или строки. Различают два типа абсолютной ссылки: полная и частичная. Полная абсолютная ссылка указывается, если при копировании или перемещении адрес ячейки, содержащий исходное значение, не меняется. Для этого символ $ ставится перед наименованием столбца и номером строки, например, $B$5; $D$12. Частичная абсолютная ссылка указывается, если при копировании и перемещении не меняется номер строки или наименование столбца. При этом символ $ в первом случае становится перед номером строки, а во втором – перед наименованием столбца.
Относительная ссылка – это изменяющийся при копировании и перемещении формулы адрес ячейки, содержащий исходное значение. Изменение адреса происходит по правилу относительной ориентации ячейки с исходной формулой и ячеек с операндами.
Например: При копировании формулы «=А2+В2+$А$1» на строку ниже формула изменится и примет вид «=А3+В3+$А$1». Таким образом, относительные адреса, входящие в формулу изменяются при копировании. Абсолютные же адреса ($А$1) в таких операциях остаются неизменными, постоянными.
ПРИМЕЧАНИЕ. Чтобы иметь возможность просматривать все формулы непосредственно на рабочем листе, на вкладке ВИД диалогового окна ПАРАМЕТРЫ следует установить флажок формулы.
Копирование формул
MS Excel позволяет скопировать готовую формулу в смежные ячейки, причем адреса ячеек будут изменены автоматически. Копирование производится тем же способом, что и при автозаполнении. Для этого необходимо выделить ячейку, содержащую формулу, установить указатель мыши на черный квадрат в правом нижнем углу курсорной рамки (указатель примет форму черного крестика - маркера автозаполнения). После этого нажать левую кнопку мыши и, не отпуская, сместить указатель по горизонтали или вертикали в зависимости от того, куда распространяется формула. При копировании влево (вправо) смещение на одну ячейку по горизонтали уменьшает (увеличивает) каждый номер столбца в формуле на единицу. При копировании вверх (вниз) по вертикали уменьшает (увеличивает) каждый номер строки в формуле на единицу.
ИМЕНА В ФОРМУЛАХ
Указать адрес ячейки или диапазона — это не единственный способ сослаться на них в формуле. В EXCEL существует возможность присваивать ячейкам, диапазонам ячеек, формулам и константам имена и использовать их в качестве абсолютных ссылок. Присваивать имена можно и несмежным диапазонам, а также ячейкам, расположенным в диапазоне, которому уже дано имя.
При выборе имени для диапазона ячеек следует придерживаться определенных правил. Имя может содержать до 255 знаков и состоять из букв, цифр, символов точки и подчеркивания. Начинаться оно должно с буквы или со знака подчеркивания. Кроме того, имя не должно быть похожим на ссылку (например, таким как F2 или A$3), однако оно может состоять из нескольких слов, разделенных символами точки и подчеркивания. Регистр символов в имени EXCEL не различает.
Чтобы присвоить имя константе и формуле, ячейке или диапазону ячеек необходимо вызвать команду ВСТАВКА ИМЯ ПРИСВОИТЬ. Имя диапазона вводится в поле ИМЯ диалогового окна ПРИСВОЕНИЕ ИМЕНИ. Поле ФОРМУЛА предназначено для отображения ссылки на выделенную ячейку или диапазон ячеек в том виде, в каком она будет входить в формулу. EXCEL создает абсолютные ссылки. Имена диапазонов ячеек активной книги перечисляются в списке под полем ИМЯ диалогового окна ПРИСВОЕНИЕ ИМЕНИ.
Ввод формулы, содержащей ссылки или имена
Для ввода формулы, следует выполнить следующие шаги:
Создание ссылок на ячейки других рабочих листов или рабочих книг
В формуле могут использоваться ссылки на ячейки и диапазоны ячеек, расположенные в других рабочих листах. Для того чтобы задать ссылку на ячейку другого листа, следует расположить перед адресом ячейки имя листа, за которым следует восклицательный знак. Рассмотрим следующий пример формулы со ссылкой на ячейку другого рабочего листа (Лист2):
=Лист2!A1
Можно создавать формулы и со ссылками на ячейки другой рабочей книги. Для этого перед ссылкой на ячейку необходимо ввести имя рабочей книги (в квадратных скобках), затем — имя листа и адрес ячейки:
= [Книга1.xls]Лист1!A1 + 1
Если в имени рабочей книги присутствует один и более пробелов, имя книги (и имя рабочего листа) следует заключить в одинарные кавычки. Например:
= ' [Книга учета. xls] Лист1' !А1 + 1
Если рабочая книга, на которую задается ссылка, закрыта, необходимо добавить полный путь к файлу этой книги:
='D:\Excel\ [Книга учета.xls] Лист1’!А1+1
Создавать внешние ссылки, можно просто указывая на ячейки. Для этого файл источника должен быть открыт.
Типы ошибок
Значение ошибки зависит от типа ошибки, допущенной в формуле.
##### - ширина столбца недостаточна для размещения результатов вычисления формулы и когда полученные значения даты или времени являются отрицательными числами.
#ССЫЛКА! - формула содержит ссылку на несуществующие ячейки.
#ДЕЛ/0! - деление на нуль.
#ЧИСЛО! - нарушения правил при задании операций, принятых в математике, или в результате некорректного определения аргументов функции.
#ИМЯ? - при вводе имени допущена ошибка, и программа не может найти нужное имя ни среди имен функций, ни среди имен диапазонов.
#ПУСТО! - неверное указание пересечения диапазонов.
#Н/Д. - не заданы один или несколько аргументов функции или происходит обращение к недоступной пользовательской функции.
#ЗНАЧ! ввод аргумента или операнда недопустимого типа.
EXCEL может распознать далеко не все ошибочные ситуации. Например, если неправильно указан порядок выполнения операций, формула все равно будет вычислена, так как формальных ошибок при записи ее компонентов допущено не было. Чтобы легче было найти логическую ошибку, можно установить для рабочего листа режим отображения формул, а не результатов.
ФУНКЦИИ
Функция – встроенная в EXCEL программа с уникальным именем, для которой пользователь должен задать конкретные значения аргументов функции, стоящих в скобках после ее имени. Скобки - обязательная принадлежность функции, даже если у нее нет аргументов. Функцию (так же, как и число) можно считать частным случаем формулы. Встроенные в EXCEL функции реализуют различные типы вычислений: математические, статистические, логические, текстовые, информационные и другие.
Вызов функции в формуле, то есть ввод ее имени, скобок и аргументов, можно выполнить с клавиатуры — либо непосредственно в ячейке, либо в строке формул. При этом адреса ячеек, содержимое которых должно использоваться в качестве аргументов функции, удобно определять, выделяя ячейки с помощью мыши. Аргументами функции могут быть адреса как смежных, так и несмежных диапазонов ячеек. Адрес смежного диапазона состоит из адресов первой и последней ячеек, разделенных двоеточием, а адрес несмежного диапазона включает адреса несмежных ячеек, разделенные точкой с запятой. Оба символа разделения вставляются в формулу автоматически, при указании диапазона ячеек с помощью мыши. В случае некорректной записи функции (предположим, пользователь забыл указать обязательный аргумент, количество закрывающих скобок не соответствует количеству открывающих и т. д.) появляется сообщение об ошибке.
В одной формуле может быть несколько функций, объединенных различными знаками операций. Если в качестве аргументов функции используются другие функции, то такие функции называются вложенными.
На формулы с функциями не накладываются никакие особые ограничения. Их можно копировать обычным образом, учитывая тип ссылки (относительная или абсолютная); допускается использование в качестве аргументов ссылок на диапазоны из других листов и книг.
При работе с функциями рекомендуется использовать МАСТЕР ФУНКЦИЙ. В этом случае аргументы устанавливаются в диалоговом окне, что полностью исключает появление ошибок, вероятность возникновения которых при вводе функции с клавиатуры довольно высока.
МАСТЕР ФУНКЦИЙ вызывается с помощью команды ВСТАВКА→ФУНКЦИЯ или кнопкой Вставка функции строки формул. (Рис. 1.3).
Рис. 1.3
Все функции EXCEL сгруппированы по категориям, имена которых отображаются в списке КАТЕГОРИЯ диалогового окна МАСТЕР ФУНКЦИЙ – ШАГ 1 ИЗ 2. В поле ВЫБЕРИТЕ ФУНКЦИЮ, отображается перечень функций выбранной категории. Все функции в перечне приведены в алфавитном порядке. В нижней части окна дается краткое описание выделенной функции и приводится ее синтаксис. Если этих данных недостаточно, щелкните здесь же на ссылке СПРАВКА ПО ЭТОЙ ФУНКЦИИ, и на экране появится окно справочной подсистемы с более полной информацией.
Если не известно название требуемой функции, ее можно найти, введя в поле ПОИСК ФУНКЦИИ диалогового окна МАСТЕР ФУНКЦИЙ краткое описание выполняемых ею действий и нажав кнопку НАЙТИ, после чего в поле ВЫБЕРИТЕ ФУНКЦИЮ появится список всех подходящих функций.
После выбора необходимой функции следует нажать кнопку ОК. Появиться второе диалоговое окно МАСТЕР ФУНКЦИЙ – ШАГ 2 ИЗ 2, которое предназначено для задания аргументов функции. Оно содержит одно или несколько однострочных полей, снабженных специальной кнопкой для сворачивания панели . Если количество аргументов функции может быть произвольным, то после ввода аргументов в очередное поле, на панели появляется дополнительное поле и т.д. При активизации поля в нижней части панели появляется пояснение его назначения.
Технология задания аргументов:
Альтернативным способом является ввод аргументов вручную в строке формул.
Использование функций в качестве аргумента:
Приведем краткое описание некоторых категорий функций.
Математические и статистические функции
Математические функции позволяют выполнять простые и сложные вычисления. В категорию Математические входят тригонометрические функции; функции, выполняющие арифметические действия; функции, позволяющие работать с массивами значений или матрицами, а также функции, которые используются для получения итоговых значений в массивах данных или списках.
Статистические функции предназначены для проведения статистического анализа. С помощью статистических функций можно вычислить статистические характеристики набора данных, такие как среднее арифметическое значение, стандартное отклонение или дисперсия, построить статистический ряд, вычислить доверительные интервалы. В категории Статистические имеются также функции для проверки статистических гипотез, вычисления известных статистических распределений и проведения корреляционного и регрессионного анализа.
Описание некоторых функций представлено ниже.
Функция СУММ
Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Функция имеет следующий синтаксис: СУММ(число1;число2; ...)
где число1;число2; ... количество аргументов (от 1 до 30), сумму которых необходимо вычислить.
Аргументами функции СУММ должны быть числа, заданные в виде конкретных значений, ссылок на ячейки или диапазоны ячеек, массивов констант. Вместо ссылок можно использовать имена ячеек или диапазонов ячеек. Массив констант представляет собой массив чисел, заключенных в фигурные скобки, например {1;2;3} или {1:2:3}.
Пример2.1.
Предположим, что ячейки A2:E2 содержат числа 5, 15, 30, 40 и 50.
Формула =СУММ(A2:C2) вычисляет сумму всех чисел в ячейках от A2 до C2. Результат вычисления: 50. Если диапазону ячеек A2:С2 задать имя «данные», то сумму этих ячеек можно получить следующим образом: СУММ(данные).
Формула =СУММ(B2:E2; 15) вычисляет сумму чисел в ячейках от B2 до E2,а затем к результату прибавляется 15. Результат вычисления: 150.
Функция СУММЕСЛИ
Функция СУММЕСЛИ вычисляет сумму ячеек внутри диапазона, удовлетворяющих определённому условию. Функция имеет следующий синтаксис:
СУММЕСЛИ(диапазон; критерий; [диапазон_суммирования] )
Диапазон — проверяемый диапазон, каждая ячейка из которого проверяется на соответствие условию, указанному во втором аргументе.
Критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32(суммировать значения в ячейках, значения в которых 32), ">32"( суммировать все числа, большие 32), "яблоки"( суммировать все значения, находящиеся напротив текста "яблоки"), A2(суммировать значения в ячейках, соответствующие значению хранящемуся в ячейке A2).
Диапазон_суммирования — фактические ячейки для суммирования. Необязательный аргумент, используется тогда, когда проверяемый диапазон и диапазон суммирования находятся в разных диапазонах. Если он не указан, то в качестве диапазона суммирования используется проверяемый диапазон (первый аргумент). Если он указан, то суммируются значения из ячеек этого диапазона, находящиеся "напротив" соответствующих ячеек проверяемого диапазона.
Пример2.2.
Пусть имеется следующая таблица данных:
Формула =СУММЕСЛИ(A2:A6;"Овощи";C2:C6) просматривает диапазон A2:A6 в поисках элемента "Овощи" (аргумент критерий для функции СУММЕСЛИ) и затем складывает соответствующие значения из диапазона C2:C6 . Результат вычисления: 4 500,00р.
Функция СЧЕТ и СЧЕТЗ
Функция СЧЁТ подсчитывает количество числовых ячеек в списке аргументов. Функция СЧЁТЗ подсчитывает количество непустых значений в списке аргументов. Функции имеют следующий синтаксис:
СЧЁТ (значение1; значение2; ...)
СЧЁТЗ (значение1; значение2; ...)
Значение1, значение2, ... — это от 1 до 30 аргументов, количество которых требуется сосчитать.
Пример2.6.
Пусть имеется следующая таблица данных:
Формула =СЧЁТ(A1:A5) возвращает число 3
Формула =СЧЁТЗ(A1:A5) возвращает число 4
Функция СЧЁТЕСЛИ
Функция СЧЁТЕСЛИ подсчитывает количество ячеек внутри диапазона, удовлетворяющих определённому условию. Функция имеет следующий синтаксис:
СЧЁТЕСЛИ(диапазон; критерий)
Диапазон—проверяемый диапазон, каждая ячейка из которого проверяется на соответствие условию, указанному во втором аргументе. Из этого же диапазона происходит подсчёт количества ячеек.
Критерий—условие, на соответствие которому проверяется каждая ячейка из первого аргумента. Условие записывается аналогично функции СУММЕСЛИ.
Пример2.3.
Пусть имеется таблица с данными приведенными в примере 2.2. Диапазону ячеек A2:A6 задано имя Категория.
Формула =СЧЁТЕСЛИ (Категория;"Фрукты") просматривает диапазон Категория в поисках элемента " Фрукты " и затем подсчитывает количество соответствующих значений. Результат вычисления: 3.
Пример2.3.
Пусть ячейки B3:B6 содержат 32, 54, 75, 86 соответственно.
Формула =СЧЁТЕСЛИ(B3:B6;">55") подсчитывает те ячейки диапазона B3:B6, которые содержат значения больше 55. Результат вычисления: 3.
Функции МАКС и МИН
Функции МИН и МАКС принимают от 1 до 30 аргументов и возвращают минимальный / максимальный из них. Если в качестве аргумента передать диапазон ячеек, из диапазона будет выбрано минимальное / максимальное значение. Функции имеют следующий синтаксис:
МИН(число1; число2; ...; число30)
МАКС(число1; число2; ...; число30)
Пример2.4.
Если ячейки A1:A5 содержат числа 10, 2, 9, 27 и 7.
Формула =МАКС (A1:A5) определяет наибольшее значение в диапазоне ячеек от A1 до A5. Результат вычисления: 27
Формула =МИН(A1;A3;A5) определяет наименьшее значение в ячейках A1, A3 и A5. Результат вычисления: 7
Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ
Функции НАИБОЛЬШИЙ / НАИМЕНЬШИЙ возвращают k-ое наибольшее / наименьшее значение из множества данных. Функции имеют следующий синтаксис:
НАИБОЛЬШИЙ (массив;k)
НАИМЕНЬШИЙ (массив;k)
Массив — массив или диапазон ячеек, для которых определяется k-ое наибольшее / наименьшее значение.
k — позиция (начиная с наибольшей / наименьшей) в массиве или интервале ячеек данных.
Функции МИН и МАКС - это частный случай этих двух функций, возвращающий самые маленькие и большие значения.
Пример2.5.
Пусть ячейки A1:A5 содержат числа 10, 2, 9, 27 и 7.
Формула =НАИБОЛЬШИЙ (A1:A5;2) определяет второе наибольшее значение в диапазоне ячеек от A1 до A5. Результат вычисления: 10
Формула =НАИМЕНЬШИЙ (A1:A5;3) определяет третье наименьшее значение в диапазоне ячеек от A1 до A5. Результат вычисления: 9
Логические функции
С помощью логических функций проверяют ячейки либо их диапазоны на определенное условие. Логические функции возвращают значение ИСТИНА (если условие выполняется), либо значение ложь (если условие не выполняется). Описание некоторых функций представлено ниже.
Функция ЕСЛИ
Функция ЕСЛИ возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Функция имеет следующий синтаксис:
ЕСЛИ (лог_выражение; значение_если_истина; [значение_если_ложь])
Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.
Значение_если_истина — значение, которое возвращается, если лог_выражение равно ИСТИНА.
Значение_если_ложь — значение, которое возвращается, если лог_выражение равно ЛОЖЬ.
Пример2.7.
Формула =ЕСЛИ(A10>=100;СУММ(B5:B15);"") вычисляет сумму для диапазона ячеек B5:B15, если значение ячейки A10 >= 100, то лог_выражение имеет значение ИСТИНА. В противном случае лог_выражение имеет значение ЛОЖЬ и возвращается пустой текст (""), очищающий ячейку, которая содержит функцию ЕСЛИ.
Функция И, ИЛИ и НЕ
Три дополнительные функции — И, ИЛИ и НЕ позволяют создавать сложные логические выражения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют следующий синтаксис:
И (логическое_значение1; [логическое_значение2]; ...)
ИЛИ (логическое_значение1;[логическое_значение2]; …)
Функция И возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Функция ИЛИ возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Функция НЕ меняет на противоположное логическое значение своего аргумента. Используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине. Функция имеет следующий синтаксис:
НЕ(логическое_значение)
Логическое_значение — величина или выражение, которые могут принимать два значения: ИСТИНА или ЛОЖЬ.
Пример2.8.
Пусть ячейки А2 и B2 содержат значения 10 и 20 соответственно.
Формула =И(A2=B2;B2>15)вычисляет значение логического выражения в первом аргументе, т.е. проверяется равенство значений ячеек A2 и B2. Результатом сравнения будет логическое значение ЛОЖЬ. Затем вычисляется значение логического выражения во втором аргументе – проверяется, является ли значение в ячейке B2 большим 15. Условие удовлетворяется, поэтому результатом сравнения будет логическое значение ИСТИНА. После вычисления логических выражений в аргументах функция сравнивает эти результаты. Один из аргументов имеет значение ЛОЖЬ. Результат вычисления: ЛОЖЬ.
Формула =ИЛИ(A2=B2;B2>15) проверяет равенство значений ячеек A2 и B2. Результатом сравнения будет логическое значение ЛОЖЬ. Проверяется, является ли значение в ячейке B2 большим 15. Результатом сравнения будет логическое значение ИСТИНА. Один из аргументов имеет значение ИСТИНА. Результат вычисления: ИСТИНА.
Формула =НЕ(A2>B2) проверяет, является ли значение в ячейке A2 большим B2. Результатом сравнения будет логическое значение ЛОЖЬ. Результат вычисления: ИСТИНА.
Формула =НЕ(A2=10) проверяет, является ли значение в ячейке A2 =10. Результатом сравнения будет логическое значение ИСТИНА. Результат вычисления: ЛОЖЬ.
Текстовые функции
В Excel можно создавать формулы, позволяющие выполнять различные действия по обработке текстовой информации: определение количества символов в строке, выделение подстроки из строки, преобразование текста в числовое значение, изменение регистра, объединения нескольких строк в одну и т. д. Описание некоторых функций представлено ниже.
Функция СЦЕПИТЬ
Функция СЦЕПИТЬ объединяет несколько текстовых строк в одну. Функция имеет следующий синтаксис:
СЦЕПИТЬ (текст1; текст2;...)
текст1, текст2 , ... — это от 1 до 30 элементов текста, объединяемых в один элемент.
В качестве аргументов функции СЦЕПИТЬ можно использовать текстовые фрагменты, содержащие символы пробела, тире, запятые, числа, другие функции и т.п. Вместо функции СЦЕПИТЬ для объединения текстовых фрагментов можно использовать амперсанд (оператор &).
Пример2.9.
Пусть ячейки А1,B1 и C1 содержат значения 10, «декабря» и 2012 соответственно.
Формула =СЦЕПИТЬ ("От ";A1;" "; B1;" "; C1;" года") объединяет значения "От: ", "10", " "(пробел), "февраля", " ", "2012" , "года" в один элемент. Результат вычисления: "От 10 февраля 2012 года".
Формула ="От "&A1&" "& B1&" "& C1&" года". Результат вычисления: "От 10 февраля 2012 года".
Функция Левсимв и ПРАВСИМВ
Функции Левсимв / ПРАВСИМВ возвращают указанное число знаков с начала / конца текстовой строки. Функции имеют следующий синтаксис:
Левсимв (текст; количество_знаков)
Текст — текстовая строка, содержащая извлекаемые знаки.
Количество_знаков — количество знаков, извлекаемых функцией ЛЕВСИМВ / ПРАВСИМВ.
Значение аргумента текст может быть либо задано непосредственно в самой формуле, либо содержаться в любой ячейке с указанием в формуле ссылки на эту ячейку. Если текст задан в самой формуле, он должен быть заключен в кавычки. Значение аргумента число_знаков должно быть либо больше нуля, либо равно нулю. Если значение аргумента число_знаков больше длины текста, то функции ЛЕВСИМВ / ПРАВСИМВ возвращают весь текст.
Пример2.9.
Пусть ячейка A1содержит значение «Цена продажи».
Формула = ЛЕВСИМВ(A1;4) возвращает первые 4 символа строки A1. Результат вычисления: «Цена».
Формула = ПРАВСИМВ(A1;7) возвращает последние 7 символов строки A1. Результат вычисления: «продажи».
Функция НАЙТИ и ПОИСК
Функции НАЙТИ и ПОИСК используются для определения позиции одной текстовой строки в другой. Обе функции возвращают номер символа, с которого начинается первое вхождение искомой строки. Эти две функции работают одинаково за исключением того, что функция НАЙТИ учитывает регистр букв, а функция ПОИСК допускает использование символов шаблона. Функции имеют следующий синтаксис:
НАЙТИ(искомый_текст;просматриваемый_текст;[нач_позиция])
ПОИСК(искомый_текст;просматриваемый_текст;[нач_позиция])
искомый_текст —задает текстовую строку, которую надо найти.
просматриваемый_текст —текст, в котором производится поиск. Любым из этих аргументов может быть строка символов, заключенная в двойные кавычки, или ссылка на ячейку. нач_позиция — Необязательный аргумент, который задает позицию в просматриваемом тексте, с которой начинается поиск. Аргумент нач_позиция следует использовать в том случае, когда просматриваемый_текст содержит несколько вхождений искомого текста. Если данный аргумент опущен, Excel возвращает позицию первого вхождения.
Эти функции возвращают ошибочное значение, когда искомый_текст не содержится в просматриваемом тексте, либо нач_позиция меньше или равна нулю, либо нач_позиция превышает количество символов в просматриваемом тексте, либо нач_позиция больше позиции последнего вхождения искомого текста.
Если точная последовательность символов искомого текста неизвестна, можно использовать функцию ПОИСК и включить в строку искомый_текст символы шаблона: знак вопроса (?) и звездочку (*). Знак вопроса соответствует одному произвольно набранному символу, а звездочка заменяет любую последовательность символов в указанной позиции.
Пример 2.9.
Пусть ячейка A1 содержит значение «Цена продажи».
Формула = НАЙТИ("р"; A1) определяет позицию буквы "р" в строке " Цена продажи ". Результат вычисления: 7.
Формула = ПОИСК ("П*и";А1) ищет позицию слова или его части начинающегося на "П" и заканчивающийся на "и" в тексте, находящемся в ячейке А1. Результат вычисления: 6.
Функция ПСТР
Функция ПСТР возвращает заданное число символов из строки текста, начиная с указанной позиции. Эта функция имеет следующий синтаксис:
ПСТР (текст;нач_позиция;количество_символов)
текст - это текстовая строка, содержащая извлекаемые символы;
нач_позиция - это позиция первого символа, извлекаемого из текста (относительно начала строки);
количество_символов - это число извлекаемых символов.
Пример 2.10.
Пусть ячейка A1 содержит значение «12-105-77».
Формула = ПСТР (А1;4;3) возвращает три символа из ячейки A1, начиная с четвертой позиции. Результат вычисления: 105.
Условное форматирование
EXCEL позволяет производить форматирование ячейки с учетом хранящихся в ней данных. Такое форматирование называется условным, потому что выполняется оно на основании результатов проверки определенных условий (например, если значение в ячейке больше или меньше определенного числа). В условии могут проверяться и текстовые данные (например, соответствует ли содержимое ячейки слову «Книга»). Применяя условное форматирование (ФОРМАТ УСЛОВНОЕ ФОРМАТИРОВАНИЕ), можно влиять на атрибуты шрифта и границ, а также на заливку ячеек. Например, можно выделять определённым цветом данные отвечающие определенному условию (рис. 2.9)
Рис. 2.7. Диалоговое окно УСЛОВНОЕ ФОРМАТИРОВАНИЕ
При задании нескольких условий необходимо щёлкнуть по кнопке А ТАКЖЕ.
Создание расчетного документа с использованием справочных таблиц
При построении сложных экономических моделей обычно имеется набор данных, используемых в нескольких таблицах, как в качестве параметров расчетов, так и для построения отчетных форм. Создание и использование отдельных таблиц-справочников позволяет ускорить процесс разработки модели, повышает надежность расчетов, а также дает возможность применять специальные средства интерфейса Excel. Рассмотрим способы поиска данных в таблице (или диапазоне ячеек) с помощью различных встроенных функций Excel.
Для демонстрации возможностей встроенных функций Excel, создадим справочник «Прайс-лист» и таблицу «Продажи», которая будет получать данные из справочной таблицы.
Для добавления единицы измерения в таблицу продаж прямая ссылка на справочник приведет к некопируемой формуле. Чтобы избежать этого, используем функцию поиска ВПР().
Функция ВПР (вертикальный просмотр) используется, когда данные сведены в столбцы. Она ищет значение в крайнем левом столбце и сопоставляет его с данными в указанном столбце той же строки.
Синтаксиса формулы ВПР:
=ВПР (искомое_значение, таблица, номер_столбца, [интервальный просмотр])
Ячейка D10 содержит формулу:
=ВПР(B10;$A$3:$E$6;2;0)
Функция ВПР ищет значение по определенному номеру столбца в диапазоне поиска (в примере – второй столбец) в прямоугольном диапазоне ячеек. Последний параметр (0 или ЛОЖЬ) используется для указания искать ли ближайшее значение (ИСТИНА), либо точное совпадение (ЛОЖЬ). Первый вариант редко используется на практике, так как требует предварительно отсортированного диапазона поиска. Для аналойного поиска по горизонтальному массиву используется функция ГПР.
Поиск по двум условиям реализован в формулах выбора цены из прайс-листа для таблицы «Продажи».
Необходимо выбрать значение из ячейки на пересечении строки покупателя и столбца месяца. Реализация такого алгоритма напрямую приводит к формуле с использованием функций вычисляемых ссылок - СМЕЩ() и ПОИСКПОЗ()
Синтаксис функций:
=СМЕЩ(ссылка; смещение_по_строкам; смещение_по_столбцам)
=ПОИСКПОЗ (искомое_значение; просматриваемый_массив; [тип_сопоставления])
Ячейка E10 содержит формулу:
=СМЕЩ($A$2;ПОИСКПОЗ(B15;$A$3:$A$6;0);ПОИСКПОЗ(ДАТА(ГОД(A15);МЕСЯЦ(A15);1);$C$2:$E$2;0)+1)
Функция ПОИСКПОЗ ищет и возвращает порядковый номер элемента в списке. Функция СМЕЩ возвращает значение по относительной ссылке от определенной ячейки – в примере, от верхнего левого угла таблицы прайс-листов. Функция преобразования даты ДАТА превращает дату из таблицы продаж в дату, используемую в прайс-листе – первое число месяца.
Для вычисления цены можно воспользоваться функциями ИНДЕКС() и ПОИСКПОЗ()
Синтаксис функции:
=ИНДЕКС (массив; номер_строки; номер_столбца)
Ячейка E10 содержит формулу:
= ИНДЕКС($A$3:$E$6;ПОИСКПОЗ(B10;$A$3:$A$6);ПОИСКПОЗ(ДАТА(ГОД(A10);МЕСЯЦ(A10);1); $C$2:$E$2)+2)
Функция ИНДЕКС возвращает значение из заданного диапазона Если используются оба аргумента номер_строки и номер_столбца, то функция ИНДЕКС возвращает значение, находящееся в ячейке на пересечении указанных строки и столбца.
Использование справочников для создания пользовательского интерфейса
Создание выпадающих списков возможна через меню Данные→Проверка.
При выборе данного пункта меню появляется диалоговое окно, позволяющее организовать проверку вводимых в ячейку данных. Одной из возможностей такой проверки является выбор значений из списка.
Интерфейс с организацией выпадающего списка существенно снижает риск ошибок при вводе строковых значений в ячейку электронной таблицы.
Одной из проблем организации списка через функцию проверки является невозможность использования явной ссылки на диапазон данных, находящихся вне текущего листа. Для решения этой проблемы следует использовать именованные диапазоны, что делает более прозрачным смысл заданного условия проверки, а также снижает риск ошибки в формуле.
Для создания выпадающего списка следует:
Кроме выбора из списка, в диалоговом окне ПРОВЕРКА ВВОДИМЫХ ЗНАЧЕНИЙ можно установить проверку данных при вводе в таблицу. Ограничения мо гут накладываться как на тип представляемых в ячейке данных, так и на диапазон возможных значений.
Прежде всего, нужно определить допустимый тип данных в поле Тип дан ных. В поле Значение необходимо выбрать критерий проверки: больше, не равно и т. д. В остальных полях (их количество за висит от критерия) должны быть установлены граничные значения.
На вкладке Сообщение для ввода можно вводить текст сообщения, которое должно появляться на экране при активизации ячейки, на которую нало жены ограничения. Поле для ввода текста сообщения может содержать до 255 символов. Вкладка Сообщение об ошибке предназначена для ввода сообщения, сигнализирующего об обнаружении ошибки, допущенной при вводе данных.
В ячейках, на которые наложены ограничения, ошибочные данные могут поя виться в результате вычисления формулы.
Для установки требуемых функции проверки вводимых данных, следует выделить нужный диапазон и обратиться к соответствующей опции.
29