Формулы в экселе все: Функции Excel (по алфавиту) — Служба поддержки Майкрософт

Содержание

Основы использования формул в Excel | MS Office

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

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

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

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

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

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

От формулы, содержащей только числа, написанные вручную, мало пользы. Гораздо более полезно применять в формулах Excel ссылки на ячейки. В формулу можно подставить адрес любой ячейки, кроме ячейки самой формулы (что привело бы к ошибке). Адреса ячеек лучше не писать вручную, а просто щёлкать мышкой на тех ячейках листа, адреса которых требуется подставить в формулу Excel.

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

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

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

В Excel не предусмотрено встроенной функции для поиска формул. Когда формула введена в ячейку, узнать, является ячейка постоянным значением или значением, полученным из формулы, можно, только щелкнув ячейку и взглянув на строку формул или же нажав Ctrl+~ (тильда; чтобы отменить режим показа формул, нажмите Ctrl+~ еще раз). Предлагаемый трюк позволит вам выделить ячейки с формулами при помощи трех строчек кода VBA и условного форматирования. [1]

Закройте окно Visual Basic, нажав Alt+Q или пройдя по меню File –> Close and Return to Microsoft Excel. Вы создали функцию IsFormula. Если вы создали функцию в модуле, относящемся к этой рабочей книге (как на рис. 1), а не в модуле Личной книги макросов – Personal.xlsb, то функция будет доступна только в этой конкретной книге Excel. Чтобы ваш труд не пропал даром, сохраните Excel-файл командой Сохранить как, и выбрав тип файла Книга Excel с поддержкой макросов (*.xlsm). Ваша пользовательская функция, как и любая встроенная функция, доступна в окне Вставка функции в категории Определенные пользователем (рис. 2).

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

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

Выделите диапазон ячеек, заполненный данными, плюс сделайте запас на случай, если данные будут добавляться. Не выделяйте весь лист, так как это может увеличить размер файла (иногда катастрофически). В нашем примере (рис. 4) я выделил область А1:К28. Причем ячейка А1 должна быть активной.

Пройдите по меню Главная –> Условное форматирование –> Создать правило (я работаю в Excel2013). В открывшемся окне Создание правила форматирования выберите опцию Использовать формулу для определения форматируемых ячеек. И в поле Форматировать значения, для которых следующая формула является истинной введите =IsFormula(A1). Щелкните кнопку Формат рядом с полем Образец и выберите желтую заливку для идентификации ячеек с формулами. Щелкните ОК (рис. 5).

Получился ножиданный эффект, который можно наблюдать в прикрепленном Excel-файле. Я считал, что функция =СЛЧИС() пересчитывается только при изменении хотя бы одного значения на листе. Так вот, на листе формально не происходит изменения значений. Но, видимо, функция IsFormula, участвующая в условном форматировании, как-то влияет на этот процесс, так что экран оживает, и значения постоянно изменяются. 🙂

Иногда при вводе формул в окне условного форматирования Excel пытается добавить кавычки вокруг формул после того, как вы щелкаете на кнопке ОК. Это означает, что Excel распознал то, что вы ввели, как текст, а не как формулу. Если это произошло, вернитесь в окно Условное форматирование, удалите кавычки и щелкните ОК.

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

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

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

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

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

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

Excel также позволяет переключаться между режимами ввода при редактировании формулы. Пока в Режим редактирования , если вы поместите курсор перед или после оператора (например: + , - , и ) и нажмете F2 , вы переключитесь на Режим входа. Это позволяет использовать клавиши со стрелками для вставки новой ссылки на ячейку в этом месте.

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

Чтобы использовать F4 , щелкните формулу так, чтобы курсор касался ссылки на ячейку (например, A1 или C7 ). Когда вы нажмете F4 , вы увидите несколько знаков доллара вокруг имени ячейки. Эти знаки доллара показывают, когда эта часть ссылки на ячейку блокируется. Продолжая нажимать F4 будет переключаться между четырьмя настройками:

Оператор : позволяет нам определять диапазоны в формулах. Диапазоны определяются начальной и конечной ячейками, объединенными : . Например, диапазон A1:B2 включает ячейки A1 , A2 , B1 и B2 . Диапазоны, определенные с помощью оператора : , имеют несколько правил:

Для некоторых формул, таких как операторы IF() , требуется несколько отдельных фрагментов информации. Оператор , позволяет нам сообщить Excel, когда заканчивается один раздел и начинается следующий. Части формулы часто называют «аргументами», «параметрами» или «терминами». Эти термины взаимозаменяемы.

Помимо указания новой формулы, мы можем использовать знак равенства для проверки на равенство. Например, = 2 = 2 — это формула для проверки того, соответствует ли значение 2 равно значению 2 . Поскольку эти два значения одинаковы, результатом ячейки будет TRUE .

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

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

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

Эта формула является вспомогательной функцией. Он существует для того, чтобы при суммировании многих ячеек нам не приходилось перечислять их по отдельности строкой из + с. Подобно MIN() , MAX() и COUNT() вы можете определить диапазон для суммирования различными способами. Любой текст, включенный в диапазон, будет проигнорирован.

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

Первый термин должен быть своего рода логическим тестом, который дает в результате ИСТИНА или ЛОЖЬ . Очень простой пример: 1 = 1 , но в зависимости от ваших потребностей они могут быть намного сложнее. Часто вы будете использовать формулу IF , чтобы проверить, соответствует ли другая ячейка определенному значению. В этом случае ваш первый термин будет выглядеть примерно так: A1 = "некоторая конкретная строка" .

Второй и третий термины — это то, что вы хотите, чтобы ваша ячейка отображала. Какой из них появится, зависит от результата логического теста в вашем первом семестре. Если ваш логический тест дает TRUE , тогда Excel отобразит все, что находится во втором термине. Если ваш логический тест оценивается как ЛОЖЬ , тогда Excel отобразит все, что находится в вашем третьем термине. Это могут быть простые значения, такие как "это было правдой" , "это было неверно" , или 385 или формулы сами по себе.

Допустим, вы хотите проверить, соответствует ли ячейка A1 строке яблоко . Если это так, вы хотите отобразить ячейку B1 , который содержит значение фруктов . Если оно ложно, вы хотите отобразить ячейку B2 , которая содержит значение , а не фрукт . Формула IF() , которая вам понадобится, приведена ниже:

, …) 3 Часто нам нужно подсчитать диапазон с определенным условием. Например, «сосчитайте числа в столбце A , где столбец B равен TRUE ». На самом деле в Excel есть две формулы, которые могут это сделать: СЧЕТЕСЛИ() и СЧЁТЕСЛИМН() . Разница между ними в том, что COUNTIFS() (с дополнительным S ) работает с одним или несколькими условиями. Вместо того, чтобы запоминать два набора синтаксиса, по умолчанию используйте COUNTIFS() . Элементы в формуле СЧЁТЕСЛИМН() должны быть парами. Первый элемент — это диапазон, который вы хотите подсчитать, второй — критерии, по которым элементы следует включать и исключать. Если вы хотите подсчитать столбец A, где значение равно ИСТИНА тогда формула будет выглядеть так: =СЧЁТЕСЛИМН(A:A, ИСТИНА) Чтобы добавить дополнительные критерии, скажем, где столбец B является числом 6 , добавьте еще один член: 2
COUNTIFS(A:A, TRUE, B:B, 6)
Это вернет количество строк, где столбец A равен TRUE и , где столбец B равен 6 . Если одна или другая из этих проверок ложны, эта строка не будет включена в подсчет. Убедитесь, что все диапазоны, которые вы используете, имеют одинаковый размер. Если это не так, то формула не сможет сравнивать каждый диапазон, и формула вернет #ЗНАЧ! ошибка .
Суммировать диапазон, если выполняется ряд условий: СУММЕСЛИМН()
=СУММЕСЛИМН(суммарный диапазон, диапазон критериев1, критерий1, …)
2 90SUMIFS() Аналогично
() похож на формулу SUM() плюс условия. В отличие от СЧЁТЕСЛИМН() вам также нужно указать Excel, какой диапазон вы хотите суммировать. Диапазон для суммирования — это первый член формулы. После определения диапазона для суммирования вы можете добавить столько пар диапазонов и условий, сколько вам нужно. Суммарный столбец A на основе условий в столбцах B и C будет выглядеть следующим образом: выполняется ряд условий: СРЗНАЧЕСЛИМН()
=СРЗНАЧСЛИМН(средний диапазон, критерий диапазон1, критерий1, …)
Конструкция и использование СРЗНАЧСЛИМН() точно такие же, как 9094 СУММЕСЛИМН() . Ему также нужен диапазон для усреднения в качестве первого члена, а затем критерии и диапазоны критериев. Формула для усреднения строки 1 на основе значений в строке 2 и 3 может выглядеть примерно так:
=СУММЕСЛИМН(1:1, 2:2, ИСТИНА, 3:3, 6) значение в определенном месте: ИНДЕКС()
=ИНДЕКС(массив, номер строки, [номер столбца])
Простейшая форма ИНДЕКС() принимает диапазон и число в качестве входных данных . В фоновом режиме формула начинается в верхнем левом углу вашего диапазона. Затем он подсчитывает количество ячеек, которые вы сообщаете ему, используя второй аргумент, и возвращает значение в этой ячейке.
=ИНДЕКС(A:A, 5) → любое значение в A5
Вы также можете добавить второй член для подсчета справа.
=ИНДЕКС(A:D, 1, 3) → любое значение в C1
Поиск в диапазоне: ПОИСКПОЗ()
02 массив , [тип соответствия])
ПОИСКПОЗ() — функция поиска. Первый аргумент — это элемент, который вы хотите найти, а второй — диапазон, в котором вы хотите искать). Важно знать о MATCH() заключается в том, что его основная цель — найти числа, а не текст. Вот почему существует третий термин, тип совпадения , который в большинстве случаев нам придется вводить.
Если мы хотим найти число, которое больше или равно нашему значению поиска, мы вводим -1 в качестве третьего термина. Если нам нужно значение, которое меньше или равно нашему поисковому запросу, мы вводим 1 или оставляем поле пустым (это значение по умолчанию). Если мы хотим найти точное совпадение, третий член должен быть равен 0 .
Как вы понимаете, с числами это работает гораздо лучше, чем с текстом. Если вы используете MATCH() с текстом, убедитесь, что ваш третий термин всегда равен 0 .
ПОИСКПОЗ() возвращает индекс первой ячейки, в которой найдено значение, соответствующее критериям поиска. Например, если строка некоторая строка находится в ячейке A4 , то формула вернет 4 .
=ПОИСКПОЗ("некоторая строка", A:A, 0) → 4
Соответствующие ячейки: vlookup () , Hlookup ()
Если вы когда -нибудь слышали об их навыках излишки. ВПР() . Учитывая, насколько важным для большинства людей является инструмент VLOOKUP() (и его двоюродный брат, HLOOKUP() ), вы, вероятно, удивлены, что его нет в этом списке.
Его здесь нет, потому что он ужасен.
Они неэффективны, негибки и, самое главное, склонны бесшумно ломаться. То ли кто-то съеживается при упоминании VOOLKUP() — лакмусовая бумажка того, насколько хорошо они знают Excel. Серьезные пользователи Excel знают, что есть гораздо лучший способ сопоставления ячеек с помощью INDEX() и MATCH() .