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

Использование формул и функций в Excel

Все формулы в Excel начинаются со знака равенства «=». Они оперируют с числами, текстом, названиями ячеек, функциями. Главные отличия от обычных математических формул – формула в Excel задается одной строкой, вместо переменных используются названия ячеек. Для вычислений применяются следующие операции:

сложение – «+»;
вычитание – «-»;
умножение – «*»;
деление – «/»;
возведение в степень – «^».

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

=2+6/3

=B1-B3

=A1+1000

=(1+3)/(2+2)

Пример простейших вычислений:

Применение функций

Для автоматизации расчетов применяются разнообразные функции. Их можно вставлять нажатием кнопки «Вставить функцию». Альтернативный вариант – нажатие комбинации Shift+F3 (для ноутбуков Shift+Fn+F3). Появляется диалоговое окно, в котором надо выбрать категорию. Далее определяется конкретная функция, задаются ее аргументы, нажимается «ОК».

Вот пример пошагового вычисления квадратного корня числа. Вызвали диалоговое окно, выбрали раздел «Математические», далее «КОРЕНЬ»:

Задали аргумент (в данном случае это B1):

Нажали «ОК»:

Математические вычисления

Одна из самых востребованных математических функций – СУММ, она предназначена для суммирования значений ячеек. СУММ может работать с целым диапазоном:

=СУММ(B1:B3)

Здесь сложили B1, B2 и B3.

Также применяется суммирование отдельных ячеек:

=СУММ(B1;B3)

А здесь сложили B1 и B3.

Аналог СУММ – ПРОИЗВЕД, позволяет перемножать значения. Пример перемножения диапазона:

=ПРОИЗВЕД(B1:B3)

В категории «Математические» также представлены средства для тригонометрических вычислений, для работы с целыми числами, для округления дробей.

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

В разделе «Логические» есть средства для работы с логическими значениями. Самый простой вариант – присвоить ячейке значение ИСТИНА или ЛОЖЬ:

=ИСТИНА()

=ЛОЖЬ()

Можно инвертировать содержимое:

=НЕ(B1)

ЕСЛИ позволяет выстраивать сложные конструкции. Применяется в таком формате:

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

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

равно – «=»;
больше – «>»;
меньше – «<»;
больше или равно – «>=»;
меньше или равно – «<=»;
не равно – «<>».

Примеры операций сравнения: 2<3, B1<>B4, F5>=10.

Пример использования ЕСЛИ:

=ЕСЛИ(2>=1;10;20)

Обработка текста

В Excel имеются средства и для несложных операций с текстовыми величинами. ДЛСТР возвращает длину текстового аргумента, например, =ДЛСТР(«Волга впадает в Каспийское море») даст результат 31.

НАЙТИ осуществляет поиск одного текста в другом и возвращает номер позиции первого вхождения. Если ввести =НАЙТИ(«ас»;»Василий»;1), то получим 2.

ПОДСТАВИТЬ заменяет в тексте один фрагмент другим. =ПОДСТАВИТЬ(«Все нормально!»;»е»;»ё») даст результат «Всё нормально!». Если в качестве третьего аргумента указать пустую строку, то фрагмент будет просто удален из всего текста.

Чтобы объединить несколько строк в одну, можно использовать СЦЕПИТЬ. =СЦЕПИТЬ(«Добрый «;»день») создаст небольшую фразу из двух слов.

Дата и время

В Excel много удобных средств для обработки времени и дат. В приведенном ниже примере в A1 поместили текущую дату с помощью формулы =СЕГОДНЯ(), потом разбили ее на составные части. Для этого применили конструкции =ДЕНЬ(A1), =МЕСЯЦ(A1), =ГОД(A1). Результат:

Чтобы получить текущее время, наберите =ТДАТА(), затем измените формат ячейки правой кнопкой мыши («Формат ячеек…» -> «Число» -> «Время»), выберите удобное представление. Из текущего времени также можно выделить составные части (используя СЕКУНДЫ, МИНУТЫ, ЧАСЫ).

composs.ru

Вставка функции - Excel

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

Использование диалогового окна Вставка функции помогут вам вставьте правильное формулу и аргументы вашим потребностям. (Чтобы открыть диалоговое окно Вставка функции, нажмите кнопку

Поиск функции

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

Выбор категории или

В раскрывающемся списке выполните одно из следующих действий.

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

Выберите функцию

Выполните одно из действий, указанных ниже.

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

Справка по этой функции

Отображает соответствующий раздел справки в окне справки для выбранной функции в поле Выберите функцию.

support.office.com

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

Формула представляет собой выражение, которое вычисляет значение ячейки. Функции – это предопределенные формулы и они уже встроены в Excel.

Например, на рисунке ниже ячейка А3 содержит формулу, которая складывает значения ячеек А2 и A1.

Ещё один пример. Ячейка A3 содержит функцию SUM (СУММ), которая вычисляет сумму диапазона A1:A2.

=SUM(A1:A2)=СУММ(A1:A2)

Ввод формулы

Чтобы ввести формулу, следуйте инструкции ниже:

Выделите ячейку.
Чтобы Excel знал, что вы хотите ввести формулу, используйте знак равенства (=).
К примеру, на рисунке ниже введена формула, суммирующая ячейки А1 и А2.

Совет: Вместо того, чтобы вручную набирать А1 и А2, просто кликните по ячейкам A1 и A2.

Измените значение ячейки A1 на 3.

Excel автоматически пересчитывает значение ячейки A3. Это одна из наиболее мощных возможностей Excel.

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

Когда вы выделяете ячейку, Excel показывает значение или формулу, находящиеся в ячейке, в строке формул.

1. Чтобы отредактировать формулу, кликните по строке формул и измените формулу.

Нажмите Enter.

Приоритет операций

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

Сперва Excel умножает (A1*A2), затем добавляет значение ячейки A3 к этому результату.

Другой пример:

Сначала Excel вычисляет значение в круглых скобках (A2+A3), потом умножает полученный результат на величину ячейки A1.

Копировать/вставить формулу

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

Введите формулу, показанную ниже, в ячейку A4.
Выделите ячейку А4, кликните по ней правой кнопкой мыши и выберите команду Copy (Копировать) или нажмите сочетание клавиш Ctrl+C.
Далее выделите ячейку B4, кликните по ней правой кнопкой мыши и выберите команду Insert (Вставить) в разделе Paste Options (Параметры вставки) или нажмите сочетание клавиш Ctrl+V.
Ещё вы можете скопировать формулу из ячейки A4 в B4 протягиванием. Выделите ячейку А4, зажмите её нижний правый угол и протяните до ячейки В4. Это намного проще и дает тот же результат!

Результат: Формула в ячейке B4 ссылается на значения в столбце B.

Вставка функции

Все функции имеют одинаковую структуру. Например:

SUM(A1:A4)СУММ(A1:A4)

Название этой функции — SUM (СУММ). Выражение между скобками (аргументы) означает, что мы задали диапазон A1:A4 в качестве входных данных. Эта функция складывает значения в ячейках A1, A2, A3 и A4. Запомнить, какие функции и аргументы использовать для каждой конкретной задачи не просто. К счастью, в Excel есть команда Insert Function (Вставить функцию).

Чтобы вставить функцию, сделайте следующее:

Выделите ячейку.
Нажмите кнопку Insert Function (Вставить функцию).

Появится одноименное диалоговое окно.
Отыщите нужную функцию или выберите её из категории. Например, вы можете выбрать функцию COUNTIF (СЧЕТЕСЛИ) из категории Statistical (Статистические).
Нажмите ОК. Появится диалоговое окно Function Arguments (Аргументы функции).
Кликните по кнопке справа от поля Range (Диапазон) и выберите диапазон A1:C2.
Кликните в поле Criteria (Критерий) и введите «>5».
Нажмите OK.

Результат: Excel подсчитывает число ячеек, значение которых больше 5.

=COUNTIF(A1:C2;">5")=СЧЁТЕСЛИ(A1:C2;">5")

Примечание: Вместо того, чтобы использовать инструмент «Вставить функцию», просто наберите =СЧЕТЕСЛИ(A1:C2,»>5″). Когда напечатаете » =СЧЁТЕСЛИ( «, вместо ввода «A1:C2» вручную выделите мышью этот диапазон.

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Функции в excel

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

Рис. 90. Мастер функцийМатематические функции выполняют простые и сложные математические вычисления, например вычисление суммы диапазона ячеек, абсолютной величины числа, округление чисел и др.Статистические функции позволяют выполнять статистический анализ данных. Например, можно определить среднее значение и дисперсию по выборке и многое другое.Функции Ссылки и массивы позволяют осуществить поиск данных в списках или таблицах, найти ссылку на ячейку в массиве. Например, для поиска значения в строке таблицы используется функция ГПР.Функции работы с базами данных можно использовать для выполнения расчетов и для отбора записей по условию.Текстовые функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ.Логические функции предназначены для проверки одного или нескольких условий. Например, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, если оно ложно.Функции Проверка свойств и значений предназначены для определения данных, хранимых в ячейке. Эти функции проверяют значения в ячейке по условию и возвращают в зависимости от результата значения ИСТИНА или ЛОЖЬ.Для вычислений в таблице с помощью встроенных функций рекомендуется использовать мастер функций. Диалоговое окно мастера функций доступно при выборе команды Функция в меню Вставка или нажатии кнопки, на стандартной панели инструментов. В процессе диалога с мастером требуется задать аргументы выбранной функции, для этого необходимо заполнить поля в диалоговом окне соответствующими значениями или адресами ячеек таблицы.

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

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

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

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

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

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

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

1	2	3	4	5	6
01.01.2004	02.01.2004	03.01.2004	04.01.2004	05.01.2004	06.01.2004
01.янв	02.янв	03.янв	04.янв	05.янв	06.янв
Понедельник	Вторник	Среда	Четверг	Пятница	Суббота
Пн	Вт	Ср	Чт	Пт	Сб
Январь	Февраль	Март	Апрель	Май	Июнь
Янв	Фев	Мар	Апр	Май	Июн
1 кв	2 кв	3 кв	4 кв	1 кв	2 кв
1 квартал	2 квартал	3 квартал	4 квартал	1 квартал	2 квартал
1 кв 2004	2 кв 2004	3 кв 2004	4 кв 2004	1 кв 2005	2 кв 2005
1 квартал 2004	2 квартал 2004	3 квартал 2004	4 квартал 2004	1 квартал 2005	2 квартал 2005
2004 г	2005 г	2006 г	2007 г	2008 г	2009 г
2004 год	2005 год	2006 год	2007 год	2008 год	2009 год
8:00	9:00	10:00	11:00	12:00	13:00
Участок 1	Участок 2	Участок 3	Участок 4	Участок 5	Участок 6
1 стол	2 стол	3 стол	4 стол	5 стол	6 стол
1-й раунд	2-й раунд	3-й раунд	4-й раунд	5-й раунд	6-й раунд

Во всех этих случаях автозаполнение происходит рядом данных со стандартным шагом. При необходимости заполнения рядом данных с произвольным шагом необходимо в две смежные ячейки ввести два первых значения, затем выделить обе ячейки и перетащить маркер автозаполнения (рис. 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".

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

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

coolreferat.com