Excel: 10 формул для работы в офисе

Функции программы

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

Синтаксис

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

К примеру, вам надо сложить значения в ячейках В1, В2, В3, В4. СУММ – функция добавляющая значения. При этом формат записи имеет следующий вид.

Сначала ставится знак равно (=). После него идет функция СУММ, а за ней диапазон ячеек (В1:В4).

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

Основные функции

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

Кратко рассмотрим их названия и особенности:

1. СУММ. С помощью опции можно вычислить сумму двух и больше чисел. К примеру, если записать в качестве адреса (А1:А6), программа просуммирует все цифры в секциях, начиная с А1 по А6. Если указать опцию в формате (А1; А6), расчет будет выполнен только применительно к двум указанным секциям.
2. СЧЕТ. Задача формулы в том, чтобы рассчитать число ячеек с числовыми обозначениями в одном ряду. К примеру, для получения информации о числе ячеек с цифрами между В1 и В20, пропишите такую формулу Excel — = СЧЕТ (В1:В20).
3. СЧЕТ3. В отличие от прошлой опции, здесь учитываются все секции с внесенными данными (не только с цифрами). Плюс в том, что СЧЕТ3 можно использовать для разных типов информации, в том числе указанной в буквенном отображении.
4. ДЛСТР. Задача опции состоит в расчете числа знаков в секции. Но учтите, что система считает все действия, в том числе сделанные пробелы.
5. СЖПРОБЕЛЫ. Цель опции в удалении лишних пробелов. Это полезно, когда информация переносится с других источников, где уже имеется много ненужных пробелов.
6. ВПР. Используется, если нужно найти элементы в таблице или диапазоне по строкам.
7. ЕСЛИ. Опция применяется, если расчет осуществляется с условием «ЕСЛИ» и большим объемом данных с различными сценариями. Применение функции позволяет сравнить значения. Если результат правдивый, программа выполняет какое-то еще действие.
8. МАКС и МИН — определяют наибольший и наименьший параметр из перечня.

В Эксель применяются и другие функции, но они менее востребованы.

Правила использования

Для лучшего понимания рассмотрим, как правильно добавлять функцию в Excel. Используем параметр СРЗНАЧ.

Алгоритм действий такой:

1. Жмите на ячейку, где необходимо установить формулу — В11.
2. Пропишите знак равно =, а после укажите название нужной опции СРЗНАЧ.
3. Укажите диапазон секций в круглых скобках (В3:В10).
4. Кликните на Ввод.

После указания этих параметров программа суммирует данные в ячейках с В3 по В10, а после этого вычисляет их среднее значение.

Применение Автосуммы

Для удобства почти любую опцию можно вставить с помощью Автосуммы. Сделайте следующее:

• Выберите и жмите на секцию, в которую необходимо вбить формулу (С 11).
• В группе Редактирования в разделе Главная отыщите и нажмите на стрелку возле надписи Автосумма. 
• Выберите нужную опцию в появившемся меню, например, Сумма.
• Программа автоматически выбирает диапазон ячеек для суммирования, но эти данные можно задать вручную путем внесения правок в формулу.

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

Комбинированные формулы

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

Рассмотрим ситуацию, когда необходимо просуммировать три числа и умножить их на коэффициент 1,5 или 1,6 в зависимости от того, какое получилось число (больше или меньше 100).

В таком случае запись имеет следующий вид: =ЕСЛИ(СУММ(А2:С2)<100;СУММ(А2:С2)*1,5;СУММ(А2:С2)*1,6).

В приведенной выше формуле используется две опции — ЕСЛИ и СУММА. В первом случае учитывается три результата — условие, правильно или неправильно.

Здесь действуют такие условия:

1. Эксель суммирует числа в ячейках с А2 по С2.
2. Если полученное число меньше 100, тогда параметр умножается на 1,5.
3. Если итоговая цифра превышает 100, в таком случае результат умножается на 1,6.

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

Ошибки в формулах Excel

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

• ### – ширины столбца недостаточно для отображения результата;
• #ЗНАЧ! – использован недопустимый аргумент;
• #ДЕЛ/0 – попытка разделить на ноль;

• #ИМЯ? – программе не удалось распознать имя, которое было применено в выражении;
• #Н/Д – значение в процессе расчета было недоступно;
• #ССЫЛКА! – неверно указана ссылка на ячейку;
• #ЧИСЛО! – неверные числовые значения.

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

ABS

Чтобы определить модуль числа, используйте функцию ABS.

СЛЧИС и СЛУЧМЕЖДУ

И функция СЛЧИС (RAND), и функция СЛУЧМЕЖДУ (RANDBEWTEEN) могут генерировать случайные числа на лету. СЛЧИС (RAND) создает длинные десятичные числа от нуля до 1. СЛУЧМЕЖДУ (RANDBETWEEN) генерирует случайные целые числа между двумя заданными числами.

ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ и ЦЕЛОЕ

Чтобы округлить значения в большую или меньшую сторону, используйте функцию ОКРУГЛ (ROUND). Для принудительного округления до заданного количества цифр в большую сторону используйте ОКРУГЛВВЕРХ (ROUNDUP). Для принудительного округления в меньшую сторону используйте ОКРУГЛВНИЗ (ROUNDDOWN). Чтобы полностью удалить десятичную часть числа, используйте функцию ЦЕЛОЕ (INT).

ОКРУГЛТ, ОКРВВЕРХ и ОКРВНИЗ

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

ОСТАТ

Функция ОСТАТ (MOD) возвращает остаток после деления. Это звучит скучно, но ОСТАТ (MOD) появляется во всех видах формул, особенно в формулах, которые должны делать что-то «каждый раз». В приведенном ниже примере вы увидите, как ОСТАТ (MOD) возвращает ноль каждое третье число, когда делитель равен 3:

СУММПРОИЗВ

Функция СУММПРОИЗВ (SUMPRODUCT) — это мощный и универсальный инструмент для работы со всеми видами данных. Вы можете использовать СУММПРОИЗВ (SUMPRODUCT) для простого подсчета и суммирования на основе критериев, и вы можете использовать его изящными способами, которые просто не работают с СЧЁТЕСЛИМН (COUNTIFS) и СУММЕСЛИМН (SUMIFS). В приведенном ниже примере мы используем СУММПРОИЗВ (SUMPRODUCT) для подсчета и суммирования заказов в марте.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) является «агрегатной функцией», которая может выполнять ряд операций с набором данных. В общем, ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) может выполнять 11 операций, включая СУММ (SUM), СРЗНАЧ (AVERAGE), СЧЁТ (COUNT), МАКС (MAX), МИН (MIN) и т.д.

Ключевой особенностью ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) является то, что он будет игнорировать строки, которые были «отфильтрованы» из таблицы Excel и строки, которые были скрыты вручную. В приведенном ниже примере ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) используется для подсчета и суммирования только 7 видимых строк в таблице:

АГРЕГАТ

Как и ПРОМЕЖУТОЧНЫе.ИТОГИ (SUBTOTAL), функция АГРЕГАТ (AGGREGATE) может также выполнять ряд агрегатных операций над набором данных и, при необходимости, игнорировать скрытые строки. Ключевые различия заключаются в том, что АГРЕГАТ (AGGREGATE) может выполнять больше операций (всего 19), а также может игнорировать ошибки.

В приведенном ниже примере АГРЕГАТ (AGGREGATE) используется для выполнения операций МИН (MIN), МАКС (MAX), НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL), игнорируя ошибки. Обычно ошибка в ячейке B9 не позволяет этим функциям возвращать результат.

Как рассчитать средневзвешенную цену в Excel?

Чтобы определить средневзвешенную цену, необходимо использовать функции СУММПРОИЗВ

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

=СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).

9

Эта формула работает в два этапа. Функция СУММПРОИЗВ

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

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

Виды формул

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

Простые

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

СУММ

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

=СУММ(значение1;значение2)

=СУММ(начало_диапазона:конец_диапазона)

Перемножает все числа в выделенном диапазоне.

= ПРОИЗВЕД(начало_диапазона:конец_диапазона)

Помогает произвести округление дробного числа в большую (ОКРУГЛВВЕРХ) или меньшую сторону (ОКРУГЛВНИЗ).

ВПР

Это – поиск необходимых данных в таблице или диапазоне по строкам. Рассмотрим функцию на примере поиска сотрудника из списка по коду.

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

Таблица – диапазон, в котором будет осуществляться поиск.

Номер столбца – порядковый номер столбца, где будет осуществляться поиск.

Альтернативные функции – ИНДЕКС/ПОИСКПОЗ.

СЦЕПИТЬ/СЦЕП

Объединение содержимого нескольких ячеек.

=СЦЕПИТЬ(значение1;значение2) – цельный текст

=СЦЕПИТЬ(значение1;» «;значение2) – между словами пробел или знак препинания

Вычисление квадратного корня любого числа.

=КОРЕНЬ(ссылка_на_ячейку)

=КОРЕНЬ(число)

Альтернатива Caps Lock для преобразования текста.

=ПРОПИСН(ссылка_на_ячейку_с_текстом)

=ПРОПИСН(«текст»)

Преобразует текст в нижний регистр.

=СТРОЧН(ссылка_на_ячейку_с_текстом)

= СТРОЧН(«текст»)

СЖПРОБЕЛЫ

Убирает лишние пробелы. Это будет полезно, когда данные переносятся в таблицу из другого источника.

=СЖПРОБЕЛЫ(адрес_ячейки)

Сложные

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

ПСТР

Позволяет «достать» требуемое количество знаков из текста. Обычно используется при редактировании тайтлов в семантике.

=ПСТР(ссылка_на_ячейку_с_текстом;начальная_числовая_позиция;число_знаков_которое_вытащить)

ЕСЛИ

Анализирует выбранную ячейку и проверяет, отвечает ли значение заданным параметрам. Возможны два результата: если отвечает – истина, не отвечает – ложь.

=ЕСЛИ(какие_данные_проверяются;если_значение_отвечает_заданному_условию;если_значение_не_отвечает_заданному_условию)

СУММЕСЛИ

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

=СУММЕСЛИ(C2:C5;B2:B5;«90»)

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

СУММЕСЛИМН

Суммирование чисел на основе нескольких заданных условий.

=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…)

Программа посчитала общую сумму зарплат женщин-кассиров.

По такому же принципу работают функции СЧЁТЕСЛИ, СРЗНАЧЕСЛИ и т.п.

Комбинированные

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

Есть задача: найти сумму трех чисел и умножить ее на коэффициент 1,3, если она меньше 80, и на коэффициент 1,6 – если больше 80.

Решение следующее: =ЕСЛИ(СУММ(А2:С2)

Комбинированные формулы могут включать 10 и более функций.

Встроенные

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

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

Примеры основных формул Excel

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

1. Укажите знак «=». 
2. Введите название действия, например СУММ (сложение), ПРОИЗВЕД (умножение), КОРЕНЬ (квадратный корень числа). Если начать ввод, программа подскажет корректное название. 
3. В скобках укажите ячейки, для которых нужно выполнить действие (через точку с запятой), или их диапазон (через двоеточие). Например, в результате вычисления =СУММ(А2;С2;F2) отобразится сумма чисел трех указанных ячеек, а формула =СУММ(А2:F2) задействует весь промежуток от А2 до F2 включительно.
4. Нажмите на Enter.

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

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

Технология речевой аналитики Calltouch Predict

• Автотегирование звонков
• Текстовая расшифровка записей разговоров

Узнать подробнее

Выделим 12 наиболее популярных формул «Эксель»:

• СУММ. Самая простая функция, с помощью которой складывают значения внутри ячеек.
• СУММЕСЛИ. Тоже позволяет суммировать значения, но при соблюдении определенных условий. Например, когда нужно посчитать продажи от конкретного филиала или от определенной цены товара. В формуле сначала указывают диапазон, затем условие. Например, мы можем сложить стоимость всех товаров в столбце С, цена которых больше 100 рублей. Вид формулы: =СУММЕСЛИ(С1:С8;“>100”).
• СТЕПЕНЬ. Функцию используют, когда нужно возвести в степень какое-нибудь число. Сначала пишут идентификатор ячейки, затем степень. Например: =СТЕПЕНЬ(В4).
• СЛУЧМЕЖДУ. Формулы Excel позволяют находить случайное число из выбранного диапазона по принципу рандомайзера. Сначала указывают нижнюю границу, затем верхнюю. Например: =СЛУЧМЕЖДУ(А1;С12).
• ВПР. Функция поиска, необходимая для работы с таблицами большого объема. Например, нужно найти номер телефона сотрудника по его фамилии. Для этого указывают искомое значение, потом выбранный диапазон, затем номер столбца. Интервальный просмотр нужен, чтобы найти приблизительное значение. 

• СРЗНАЧ. Высчитывает среднее арифметическое значение. Складывает все числа и делит полученную сумму на количество слагаемых. Полезно, когда нужно посчитать среднюю выручку со всех филиалов.

• МАКС. С помощью этой функции определяют наибольшее значение среди отдельных ячеек или в рамках выбранного диапазона.
• КОРРЕЛ. Оценивает связь между несколькими значениями. Чем больше отличий, тем меньше корреляция. Она может быть от -1 до +1. Такую функцию используют, например, для сравнения курсов валют.
• ДНИ. Простая и полезная функция, которая высчитывает количество дней между датами. В первом значении указывают конечную дату и только потом — начальную.
• ЕСЛИ. Функцию удобно использовать, когда необходимо узнать, выполняется условие или нет. Например, если работник выполнил план, ему назначают премию. Сначала указывают логическое выражение, потом значение, которое нужно показать при выполнении условий. Третье значение можно не указывать.
• СЦЕПИТЬ. Помогает объединить несколько текстовых ячеек в одну. Чтобы текст не получился слитным, между значениями добавляют пробел в кавычках: ” “.
• ЛЕВСИМВ. Функция поможет обрезать часть текста до определенного размера. Полезно при составлении метатегов для сайтов. Сначала указывают текст, затем количество знаков.

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

Вложенные условия с математическими выражениями.

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

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

Если предположить, что количество хранится в B8, то формула будет выглядеть следующим образом:

=B8*IF(B8>=101; 12; IF(B8>=50; 14; IF(B8>=20; 16; IF( B8>=11; 18; IF(B8>=1; 22; «»)))))

И вот результат:

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

Например, вместо «жесткого» ввода цен в саму формулу можно обратиться к ячейкам, где они указаны (ячейки B2 — B6). Это позволит вам редактировать исходные данные без необходимости обновлять саму формулу:

=B8*IF(B8>=101; B6; IF(B8>=50; B5; IF(B8>=20; B4; IF( B8>=11; B3; IF(B8>=1; B2; «»)))))

Информационные функции

ЕПУСТО, ЕОШИБКА, ЕЧИСЛО, ЕФОРМУЛА, ЕТЕКСТ, ЕЧЁТН, ЕНЕЧЁТ, ЕЛОГИЧ

В Excel предусмотрено множество функций для проверки значения в ячейке, включая ЕЧИСЛО (ISNUMBER), ЕТЕКСТ (ISTEXT), ЕЛОГИЧ( ISLOGICAL), ЕПУСТО (ISBLANK), ЕОШИБКА (ISERROR) и ЕФОРМУЛА (ISFORMULA). Эти функции иногда называют функциями «Е», и все они возвращают ИСТИНА или ЛОЖЬ в зависимости от содержимого ячейки.

В Excel также есть функции ЕНЕЧЁТ (ISODD) и ЕЧЁТН (ISEVEN), которые проверят число на четность или нечетность.

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

Создание вложенной функции “ЕСЛИ”.

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

• • • Явное значение в виде конкретного текста или числового значения;
    • Адрес ячейки с нужным значением;
    • Математическая или другая формула;
    • Функция.

Обратите внимание на четвертый вариант. Можно указать любую функцию, в том числе и саму функцию «ЕСЛИ»

К примеру, проверим ячейку А2. Если в ней дата, то пусть Excel пусть выведет 25% для второго полугодия и 50% для первого полугодия, иначе покажет сообщение об ошибке.

Используем формулу:

=ЕСЛИ(ЯЧЕЙКА(“формат”;A2)=”D1″;ЕСЛИ(МЕСЯЦ(A2)>6;25%;50%);”Введена не дата!”).

Для проверки формата используется функция Ячейка. Она выводит все необходимые сведения. В данном случае проверяется формат, который должен быть в виде даты с номером месяца. Об этом говорит сравнение результата работы функции «ЯЧЕЙКА» со значением «D1».

Более того, при применении функции «Если» в блоке «ЛОЖЬ» можно проверить по очереди несколько условий. Как только одно из условий сработает, выведется результат варианта «ИСТИНА» для него и работа закончится. Дальше условия проверяться уже не будут. Если не выполнится ни одно из указанных условий, выведется результат, указанный в самом конце. В результате в Excel функция ЕСЛИ позволяет использовать несколько условий в заданном порядке. 

Рассмотрим такой вариант. Пусть стоимость отправки зависит от города. Если это Алматы, то она равна 2500, если Актау, то 5000, если это Усть-Каменогорск, то 4000. По остальным городам стоимость будет 1500.

Если название города указывается в ячейке В2, то получаем такую формулу.

=ЕСЛИ(B2=”Алматы”;2500;ЕСЛИ(B2=”Актау”;5000;ЕСЛИ(B2=”Усть-Каменогорск”;4000;1500))).

Обратите снимание, что города Туркестан в списке нет, поэтому выведено значение 1500, а вот Актау присутствует, и выведено значение именно для Актау, то есть 5000.

Так образуется вложенный вариант для функции «Если». Каждое новое добавление происходит в области значения при ошибке предыдущей в списке функции.

Операторы сравнения чисел и строк

Операторы, сравнивающие числа и строки, представлены операторами, состоящими из одного или двух математических знаков равенства и неравенства:

• > — более чем;
• >= — больше или равно;
• = — равно;
• — не равны.

Синтаксис:

1	Результат = Выражение1 Оператор Выражение2

• Результат — любая числовая переменная;
• Выражение — это выражение, которое возвращает число или строку;
• Оператор — любой оператор, который сравнивает числа и строки.

Если переменная Result объявлена как Boolean (или Variant), она будет возвращать False и True. Числовые переменные других типов возвращают 0 (False) и -1 (True).

Операторы сравнения чисел и строк работают с двумя числами или двумя строками. При сравнении числа со строкой или строки с числом Excel VBA выдает ошибку несоответствия типов:

1 2 3 4 5 6 7 8 9 10

Sub Primer1() On Error GoTo Instr Dim myRes As Boolean ‘Сравнение строки с числом myRes = «пять» > 3 Instr: If Err.Description «» Then MsgBox «Произошла ошибка: » & Err.Description End If End Sub

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

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

1 2 3

myRes = «семь» > «восемь» ‘myRes = True myRes = «семь» > «восемь» ‘myRes = False myRes = Len(«семь») > Len(«восемь») ‘myRes = False

Создание простых формул в Microsoft Excel

​ время, когда необходимо​ ячейка B2. Её​Сумма значений в ячейках​, а затем нажмите​ использование стиля ссылок​ 6, Microsoft Excel​ относительный столбец. Абсолютная​ строки 10-20​ 09.10.2008, число 210​ ее адрес в​ данных.​По такому же принципу​ всего это сделать​ вокруг ячейки образовался​ ячеек. Без формул​ нажимаем правую кнопку​ и нажимаем «Вставить»​

Создаем первую простую формулу в Excel

​ использовать одну и​ границы будут выделены​ A1 и A2​ клавишу​ R1C1, установите или​ прибавит к сумме​

1. ​ ссылка столбцов приобретает​A10:E20​ и текст «Прибыль​ выделенной.​Копирование диапазонов ячеек​
2. ​ можно заполнить, например,​ с помощью клавиши​ «мелькающий» прямоугольник).​ электронные таблицы не​ мыши – «Вставить»​ или комбинацию CTRL+V.​
3. ​ ту же формулу​ синим цветом.​=A2+A3​ВВОД​ снимите флажок​ содержимое ячеек с​ вид $A1, $B1​
4. ​Создание ссылки на ячейку​ за квартал» являются​Введите оператор. Например, для​ с помощью курсора​ даты. Если промежутки​
5. ​ F4.​Ввели знак *, значение​ нужны в принципе.​ — «Строку» (CTRL+SHIFT+»=»)(строка​Таким же способом можно​ множество раз. Изучите​Нажмите​
6. ​=A2-A3​​(Windows) или​​Стиль ссылок R1C1​ A2 по A5​ и т.д. Абсолютная​

​ или диапазон ячеек​ константами. Выражение или​ вычитания введите знак​ мышки.​ между ними одинаковые​Создадим строку «Итого». Найдем​ 0,5 с клавиатуры​Конструкция формулы включает в​ всегда вставляется сверху).​ перемещать несколько ячеек​ уроки раздела Относительные​

Основное преимущество формул со ссылками

​Enter​Разность значений в ячейках​Return​в разделе​ на новых листах.​ ссылка строки приобретает​ с другого листа​ его значение константами​ «минус».​Смена и выравнивание​ – день, месяц,​ общую стоимость всех​ и нажали ВВОД.​

​ себя: константы, операторы,​​ сразу. На этот​ и абсолютные ссылки,​на клавиатуре. Формула​ A1 и A2​(Mac).​Работа с формулами​Удаление​ вид A$1, B$1​

Создаем формулу в Excel, выбирая ячейку мышкой

​ в той же​ не являются. Если​Выберите следующую ячейку или​ шрифтов в ячейках.​ год. Введем в​ товаров. Выделяем числовые​Если в одной формуле​ ссылки, функции, имена​Чтобы программа воспринимала вводимую​ же лист, на​ чтобы получить дополнительную​ будет создана и​=A2-A3​Когда вы нажимаете кнопку​категории​   .  Если удалить листы​

​в диалоговом окне​ и 6, Microsoft​ содержащей формулу, относительная​ функция СРЗНАЧ вычисляет​ не ссылки на​Нажмите клавишу ВВОД. В​ форматирование таблиц.​ «ноя.15». Выделим первые​ ячейку. Это диапазон​ их в следующей​ другие формулы. На​ знак «=». Например,​

​Чтобы переместить несколько ячеек,​

office-guru.ru>

Шаг 2: выбор функции

Итак, независимо от того, какой из описанных выше способов был выбран, перед нами появится окно Мастера функций (Вставка функции). Оно состоит из следующих элементов:

1. В самом верху расположено поле для поиска конкретной функции. Все что мы делаем – это набираем название (например, “сумм”) и жмем “Найти”. Результаты отобразятся в поле под надписью “Выберите функцию”.
2. Параметр “Категория”. Щелкаем по текущему значению и в раскрывшемся списке выбираем категорию, к которой относится наша функция (допустим, “Математические”).Всего предлагается 15 вариантов:
3. финансовые;
4. дата и время;
5. математические;
6. статистические;
7. ссылки и массивы;
8. работа с базой данных;
9. текстовые;
10. логические;
11. проверка свойств и значений;
12. инженерные;
13. аналитические;
14. совместимость;
15. интернет.
16. Также у нас есть возможность отобразить 10 недавно использовавшихся функций или представить все доступные операторы в алфавитном порядке без разбивки на категории.Примечание:“Совместимость” – это категория, в которую включены функции из более ранних версий программы (причем у них уже есть современные аналоги). Сделано это для того, чтобы сохранялась совместимость и работоспособность документов, которые были созданы в устаревших версиях Excel.

Функция ЕСЛИ в Excel

Функция имеет следующий синтаксис.

ЕСЛИ (bool_test, значение_if_true, )

логический_тест — проверяемое условие. Например, A2

value_if_true — это значение или формула, возвращаемая, когда происходит событие, указанное в первом параметре.

value_if_false — альтернативное значение или формула, которая возвращается, если условие не выполняется. Это поле не является обязательным. В этом случае при возникновении альтернативного события функция вернет FALSE.

Очень простой пример. Необходимо проверить, превышают ли продажи отдельных товаров 30 шт. Если они пройдут, формула должна вернуть «ОК», в противном случае — «Удалить». Расчет с результатом показан ниже.

Продажи первого продукта составляют 75, что означает, что выполнено условие, что оно больше 30. Следовательно, функция возвращает то, что указано в следующем поле — «Ок». Продажи второго товара меньше 30, поэтому условие (> 30) не выполняется, и возвращается альтернативное значение, указанное в третьем поле. Это суть функции SE. Сводя расчет, получаем результат по каждому товару.

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

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

В прогнозе запасов больше нет отрицательных значений, что в целом очень хорошо.

Шаг 3: заполнение аргументов функции

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

Рассмотрим на примере “СРЗНАЧ” (для вычисления среднего арифметического значения), работающего с числовыми данными.

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

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

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

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

Примечания:

• В нижней части окна представлено описание функции, а также комментарии/рекомендации касательно того, как именно следует заполнить тот или иной аргумент.
• Иногда количество аргументов может увеличиться. Например, как это произошло в нашем случае с функцией “СРЗНАЧ”. По умолчанию предусмотрено всего два аргумента, но если мы перейдем к заполнению второго, добавится третий и т.д.
• Принцип заполнения текстовых данных в других функциях, где это предполагается, аналогичен рассмотренному выше – либо мы указываем конкретные значения, либо ссылки на ячейки или диапазоны ячеек.

Простейший пример применения.

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

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

В этом нам поможет функция Excel ЕСЛИ. Добавим в таблицу данных столбец “Страна”. Регион “Запад” – это местные продажи («Местные»), а остальные регионы – это продажи за рубеж («Экспорт»).

СРЕДНИЕ

Функция =СРЗНАЧЕСЛИМН позволяет найти среднее значение для определенного набора данных на основе одного или нескольких критериев. При использовании этой функции следует помнить, что каждый критерий и средний диапазон могут быть разными. Однако в функции =СРЗНАЧЕСЛИ и диапазон критериев, и диапазон суммы должны иметь один и тот же диапазон размеров. Заметили разницу в единственном и множественном числе между этими функциями? Ну, тут нужно быть осторожным.

В этом примере я хотел найти средний балл в зависимости от пола учащихся. Для этого я использовал формулу =СРЗНАЧЕСЛИМН(C2:C10, B2:B10, «M») и в результате получил 56,8. Здесь C2:C10 — это диапазон, в котором формула будет искать среднее значение, B2:B10 — это диапазон критериев, а «M» — это критерии.

Формула: =СРЗНАЧЕСЛИ(средний_диапазон, критерий_диапазон1, критерий1,…)