Функции в Excel

Функции в Excel

Более сложные вычисления в таблицах Excel осуществляются с помощью специальных функций (рис. 90). Список категорий функций доступен при выборе команды Функция в меню Вставка (Insert, Function).

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

Функции Дата и время позволяют работать со значениями даты и времени в формулах. Например, можно использовать в формуле текущую дату, воспользовавшись функцией СЕГОДНЯ.

Рис. 90. Мастер функций

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

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

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

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

Текстовые функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ.

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

Функции Проверка свойств и значений предназначены для определения данных, хранимых в ячейке. Эти функции проверяют значения в ячейке по условию и возвращают в зависимости от результата значения ИСТИНА или ЛОЖЬ.

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

Создание списков для автозаполнения

Для выбора способа заполнения календарными рядами после перетаскивания необходимо щелкнуть левой кнопкой мыши по кнопке Параметры автозаполнения (см. рис. 4.14) и выбрать требуемый режим автозаполнения. В меню ряда календарных значений (рис. 4.16) можно выбрать следующие варианты заполнения:

Заполнить по рабочим дням – только рабочие дни без учета праздников;

Заполнить по месяцам – одно и то же число последовательного ряда месяцев;

Заполнить по годам – одно и то же число одного и того же месяца последовательного ряда лет.

Рис. 4.16. Меню автозаполнения при работе с датами

Список примеров некоторых данных, для которых можно использовать автозаполнение, приведен в таблице.

Начальное значение Последующие значения

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

Рис. 4.17. Автозаполнение с произвольным шагом

Создание пользовательских списков

Для удобства работы можно создать собственный список автозаполнения.

1. Введите список в смежные ячейки одного столбца или одной строки

2. Выделите ячейки со списком.

3. Щелкните значок Кнопка Microsoft Office, а затем выберите команду Параметры Excel.

4. В окне Параметры Excel выберите группу Основные. Нажмите кнопку Изменить списки.

5. В окне Списки убедитесь, что ссылка на ячейки в выделенном списке элементов отображается в поле Импорт списка из ячеек, и нажмите кнопку Импорт (рис. 4.18). Элементы выделенного списка будут добавлены в поле Списки, а его элементы будут отображаться в поле Элементы списка.

6. В окне Списки нажмите кнопку ОК.

7. В окне Параметры Excel нажмите кнопку ОК.

Рис. 4.18. Создание списка автозаполнения

Для удаления созданного списка следует в окне Списки в поле Списки выделить ненужный список и нажать кнопку Удалить.

Автоформатирование

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

(Формат). Однако перед выполнением этой команды следует или выделить блок форматируемых ячеек, или установить табличный курсор на одну из заполненных ячеек внутри блока данных. В последнем случае после выполнения команды Автоформат... блок данных будет автоматически выделен.

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

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

Выделите диапазон В8:08 и выполните команду Условное форматирование... (Формат). В появившемся диалоговом окне в группе Условие 1 во втором списке выберите значение больше, а в расположенном рядом поле наберите 250. Нажмите кнопку Формат...

, и в появившемся диалоговом окне на вкладке Вид в поле Цвет выберите светлосиний цвет. Далее в этом же окне нажмите кнопку ОК, а затем нажмите кнопку ОК в окне Условное форматирование. Убедитесь, что цвет фона ячейки С8 изменился, он стал светло-синим (предполагается, что в таблицу введены те же числа, что и на рис. 9.31). Введите в ячейку В7 значение 25. Убедитесь, что цвет фона ячейки В8 изменился, он стал светло-синим.

Вновь введите в ячейку В7 значение 14. Убедитесь, что цвет фона ячейки В8 снова стал белым. Действие 2 Снимите выделение ячеек диапазона B8:D8. Выполните команду Перейти... (Правка), и в появившемся диалоговом окне Переход нажмите кнопку Выделить... В очередном появившемся окне выберите переключатель условные форматы и нажмите кнопку ОК. Убедитесь, что выделен диапазон B8:D8, к которому применено условное форматирование.

Теперь отмените условное форматирование. Рис 9 36 ^ля этого выполните команду Условное форматирование... (Формат), а в появившемся диалоговом окне нажмите кнопку Удалить... В очередном окне убедитесь, что переключатель условие 1 включен, и нажмите кнопку ОК, а затем нажмите кнопку ОК в окне Условное форматирование.

Убедитесь, что условное форматирование (изменение фона ячеек диапазона B8:D8) отменено. Сохраните документ Вторая книга.

Условное форматирование

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

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

Первое поле со списком в окне диалога "Условное форматирование" позволяет выбрать, к чему должно применяться условие: к значению или самой формуле. Обычно выбирается параметр "Значение", при котором применение формата зависит от значений выделенных ячеек. Параметр "Формула" применяется в тех случаях, когда нужно задать условие, в котором используются данные из невыделенных ячеек, или надо создать сложное условие, включающее в себя несколько критериев. В этом случае во второе поле со списком следует ввести логическую формулу, принимающую значение ИСТИНА или ЛОЖЬ. Второе поле со списком служит для выбора оператора сравнения, используемого для задания условия форматирования. Третье поле используется для задания сравниваемого значения. Если выбран оператор "Между" или "Вне", то в окне диалога появляется дополнительное четвертое поле. В этом случае в третьем и четвертом полях необходимо указать нижнее и верхнее значения.

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

В приведенном ниже примере задан следующий формат: цвет шрифта - красный, шрифт - полужирный. Условие: если значение в ячейке превышает "100".

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

Чтобы удалить условие форматирования, выделите ячейку или диапазон и затем в меню "Формат" выберите команду "Условное форматирование". Укажите условия, которые хотите удалить, и нажмите "ОК".