Как записать в экселе формулу: как написать формулу, как посчитать сумму, сложение с условием, счет строк и пр.

Вставка формулы в Excel — пошаговая инструкция и несколько способов

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

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

Окно вставки функции

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

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

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

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

4. После вставки функции в ячейку она отобразится в стандартном виде и все еще будет доступна для редактирования.

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Используем вкладку с формулами

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

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

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

Ручная вставка формулы в Excel

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

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

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

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

Вставка математических формул

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

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

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

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

Формула суммы в Excel — statanaliz.info

Любое обучение всегда идет от простого к сложному. Когда-то и я не знал, как в Эксель посчитать сумму столбца. Примерно с этого начинали и другие пользователи. Однако все можно изучить, если приложить усилия и выделить время.

В этой статье рассмотрим самые азы, а именно, как в Экселе сплюсовать ячейки , и как автоматически посчитать сумму столбца. 

«+» – плюс

«+» – оператор суммирования. Чтобы сплюсовать числа в двух или более ячейках, нужно вначале выделить ячейку для записи суммы, нажать «=», левой кнопкой мыши указать первую ячейку, нажать «+», указать вторую ячейку и т. д., после выделения последней ячейки нажимать Enter. Данная функция является наследием микрокалькуляторов, когда нужно было набирать и плюсовать каждое значение в отдельности.

На мониторе суммирование плюсиком выглядит примерно так:

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

Функция СУММ (SUM) в Excel

СУММ – самая простая и часто используемая функция. Синтаксис очень простой.

СУММ(число1;число2;…)

число1;число2;… – это ссылки на ячейки или на целый диапазон.

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

Так как функция суммирования используется очень часто, то есть несколько способов ее вызвать. Стандартный вариант – через Мастер функций. Для вызова Мастера функций нужно стать в ячейку (то есть сделать ее активной), где будет прописана формула, затем нажать кнопку fx (слева от строки формул) и выбрать нужную функцию. На рисунке видно, где что нажимать:

Кому такой способ не нравится, в панели инструментов на закладках Главная и Формулы предусмотрена специальная кнопочка — Автосумма.

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

Однако истинные мастера Excel выбирают горячие клавиши. Автосумму можно вызвать комбинацией клавиш Alt + = (Альт и равно). Ниже показано, что происходит на экране при использовании автосуммы: активировали ячейку B21, нажали Alt + = (или кнопку на панели).

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

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

Поделиться в социальных сетях:

Надежный подход к написанию сложных формул Excel

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

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

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

Формулы Excel

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

Щелкните, чтобы увеличить изображение

Прежде чем вы сдадитесь, позвольте мне рассказать вам об одном приеме, который я впервые увидел у Билла Джелена (он же Мистер Эксель), который значительно упрощает написание формул — даже таких сложных формул, как эта.

Если вы поставите передо мной это отвратительное чудовище без контекста и попросите меня шаг за шагом объяснить, что означает каждая часть этой формулы, я, вероятно, не смогу этого сделать. Не потому, что я скопировал его из Интернета. Я могу честно сказать, что я не получил никакой помощи с ним вообще. И я специально выбрал что-то эзотерическое, чтобы у меня не было соблазна поискать его и проверить себя, чтобы увидеть, сработает ли этот подход для действительно продвинутой формулы. Так оно и было. Как колдовство.

Итак, хватит болтать. Позвольте мне показать вам это в действии. Но сначала давайте рассмотрим некоторую терминологию. И не волнуйтесь; мы начнем с гораздо более простой задачи, чем это вуду данных.

Определения

Формула : Формулы предоставляют какие-то инструкции для Excel, чтобы что-то вычислить. Они всегда начинаются со знака равенства. Оно может быть простым, как =2+2, или невероятно сложным, как показано выше.

Функция : Проще говоря, функции — это формулы, предварительно упакованные в Excel. Я имею в виду, что вы можете создавать свои собственные функции, но большую часть времени вы будете использовать одну из 80 баджиллионов, изначально предлагаемых в Excel (если, конечно, вам не нужна разница в процентах, которую Excel не предлагает… пойди разберись) . С более сложными формулами, которые вы будете писать, вы будете использовать несколько функций в одной формуле.

Аргумент : Каждая функция начинается с имени функции, за которым следует набор круглых скобок, например, =СУММ(A3:A67) или =СЦЕПИТЬ(B2,C2,D2). Отдельные элементы внутри этих круглых скобок, разделенные запятыми, являются аргументами.

Квадратные скобки : Иногда во всплывающей подсказке или на сайте Microsoft можно увидеть аргументы, заключенные в квадратные скобки. Скобки указывают, что аргумент является необязательным.

Boolean : Возвращает значение TRUE или FALSE.

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

Загрузить

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

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

Наконец, я просто вычел 1 в конце, и все заработало, как и планировалось. В конце концов, это важно, поэтому я пошел с этим.

Задача 1. Извлечение домена из URL-адресов

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

Например, предположим, что www.linkingsite.com связан с www.mysite.com/landing-page-02 и www.mysite.com/landing-page-45. Две целевые страницы будут отображаться под доменом www.linkingsite.com в моей сводной таблице, как я ее организовал.

Щелкните, чтобы увеличить изображение

Итак, как я их извлек? Легкий. Но сначала немного предыстории.

Важная информация, которую необходимо понять

Когда вам нужно извлечь строку текста из более длинной строки в Excel, вы должны использовать три основные функции: ВЛЕВО, ВПРАВО и СРЕДНЯЯ. Вы можете повозиться с ЗАМЕНОЙ, которая очень похожа на функцию «Найти и заменить» в Excel или Word, но мы не будем вдаваться в подробности.

Таким образом, каждая из этих функций запрашивает у вас:

1. Из какой ячейки вы хотите извлечь текст
2. Сколько символов вы хотите извлечь

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

Это все хорошо, но с нашим столбцом URL-адресов количество символов, которое нам нужно, чтобы извлечь изменения из URL-адреса в URL-адрес, поэтому мы не можем использовать статическое число. Вот тут и приходит на помощь функция ПОИСК.

Вкратце: Если вы много баловались формулами, вы можете спросить, почему я не использую функцию НАЙТИ. Что ж, функции ПОИСК и НАЙТИ очень похожи, только функция НАЙТИ более ограничивающая, поскольку она чувствительна к регистру и не поддерживает подстановочные знаки. Хромой. Таким образом, только раз, когда я использую FIND, когда я хочу указать регистр, который в последний раз, когда я делал это, был последним … Арендатор здесь … Да, никогда.

Итак, что мы собираемся сделать, так это использовать функцию ПОИСК, чтобы указать количество необходимых нам символов, потому что она возвращает позицию того, что вы просите Excel найти. Если он не находит то, что вы искали, он возвращает неприятную ошибку #N/A, которую мы фактически ИСПОЛЬЗУЕМ в следующем примере. Однако для нашей задачи нам нужна позиция первой косой черты после домена.

Бууут, есть проблема.

Видите эти две косые черты перед доменом (http: // )? Да, они собираются сбить нас с толку. Но не беспокойтесь. Функция SEARCH имеет необязательный аргумент для начального числа (start_num), который мы будем использовать в наших интересах.

The Playas

Итак, для нашей формулы мы собираемся объединить две функции: ВЛЕВО и ПОИСК. Вот синтаксис для каждого:

 LEFT(text, [num_chars]) 

 SEARCH(find_text,within_text,[start_num]) 

Итак, давайте попробуем…

Стратегия

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

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

find_text: «/»
Это говорит о том, что мы ищем косую черту.

внутри_текста: B3
Это первая ячейка, из которой мы собираемся извлечь домен. Когда мы закончим, мы наведем указатель мыши на правый нижний угол ячейки, чтобы перетащить формулу вниз по столбцу.

start_num: 9
Нам нужно просто выбрать номер, который стоит после последнего/до домена. Мы могли бы использовать здесь 8, но если какой-либо из URL-адресов является безопасным, вам понадобится еще один символ. Убедитесь сами.

https://* <– * находится в позиции 9 ^th , где мы хотим начать поиск /, следующего за доменом.

Итоговая формула выглядит так:

Щелкните для увеличения.

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

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

text: B3

[num_chars]: C3

Таким образом, окончательная формула выглядит так:

Нажмите, чтобы увеличить изображение.

Теперь все, что нам нужно сделать, это объединить их так, чтобы вместо ссылки на ячейку C3 мы внедрили функцию ПОИСК в окончательную формулу. Для этого просто нажмите клавишу Esc, чтобы выйти из ячейки, в которой вы находитесь, затем скопируйте функцию ПОИСК в буфер обмена (но не знак =).

Затем снова нажмите клавишу Esc, чтобы выйти из этой ячейки, и вернитесь к ячейке, содержащей вашу окончательную формулу, и замените ссылку C3 на формулу, которую вы только что скопировали из C3. Теперь ваша формула должна выглядеть так:

Нажмите, чтобы увеличить изображение.

Задача 2: Сравнить два набора данных

В моем аудите Google Doc одна из задач, которую я включил, — это проверка на наличие потерянных страниц на сайте (на вкладке «Ссылки»). Один из способов проверить их — сравнить карту сайта вашего сайта со сканированием сайта, которое можно выполнить с помощью такого инструмента, как Screaming Frog. (Раскрытие информации: я не связан с Screaming Frog каким-либо образом.)

Если вы обнаружите, что в карте сайта есть URL-адреса, которые не были найдены при сканировании Screaming Frog (или любом хороший шанс, что это осиротевшие или заблокированные страницы. В любом случае, они требуют расследования и, вероятно, не должны быть в вашей карте сайта.

Playas

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

ЕСЛИ: ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

ISNA: ISNA(значение)

MATCHok: MATCHok: , lookup_array, [match_type])

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

Поскольку эта формула требует еще нескольких шагов, я разобью их на шаги.

Шаг 1: Посмотрите, находится ли URL-адрес в C2 где-нибудь в столбце B.

Функция ПОИСКПОЗ — это еще одна функция поиска, предлагаемая Excel, и она особенно универсальна. Но он возвращает номер строки того, что вы ищете. Если он не может его найти, он возвращает ошибку #N/A (которую мы будем использовать).

Нажмите, чтобы увеличить изображение.

Шаг 2: ISNA просто возвращает логическое значение, которое позволяет узнать, является ли значение в ячейке, на которую вы ссылаетесь, ошибкой #Н/Д.

Шаг 3: Напишите оператор IF, который присваивает значение «Сиротский», если URL-адрес не находится в столбце Screaming Frog (другими словами, возвращает ошибку #N/A), и «Найдено», если нашлось.

Шаг 4: Замените каждую ссылку на вспомогательную ячейку формулой внутри этой ячейки (все, кроме =). Если вы распределите свои вспомогательные ячейки слева направо по электронной таблице, как я, начните с крайнего левого и продвигайтесь вправо.

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

Ресурсы

Полное руководство по Excel для маркетологов — это руководство Distilled по Microsoft Excel для SEO-специалистов. Это отличная отправная точка, если вы хотите начать свое путешествие, сосредоточившись на функциях, которые маркетологи используют чаще всего.

Если вы действительно хотите стать хардкорщиком, я настоятельно рекомендую канал Билла Джелена на YouTube. Его плейлисты особенно полезны, если вы хотите сконцентрироваться на определенной слабой области. Еще один подкаст, который мне очень нравится, — это канал Майка Гирвина ExcelIsFun на YouTube.

Я также регулярно публикую видеоуроки по Excel в своем блоге.

Заключительные мысли

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

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

Мнения, высказанные в этой статье, принадлежат приглашенному автору и не обязательно принадлежат Search Engine Land. Штатные авторы перечислены здесь.

Формулы — основы Microsoft Excel

Формулы в Excel — это в основном математические выражения, в которых в качестве аргументов используются ссылки на ячейки (например, «A5», D17). Например, формула, которая суммирует содержимое ячеек E5 и E6, может быть записана следующим образом:

= E5+E6

(Примечание: всем формулам в Excel должен предшествовать знак «=». ) Если значения, содержащиеся в E5 и E6, равны 6 и 11 соответственно, формула выдаст 17 в качестве отображаемого значения. Если вы измените E5 на 7, результат автоматически изменится на 18.

Пример

Предположим, вы оформляли заказ на канцелярские товары и хотели отслеживать, сколько вы тратите. Вы можете составить электронную таблицу, подобную приведенной ниже, со списком предметов, которые нужно купить, их ценами за единицу, количеством каждого заказанного предмета и общей суммой, потраченной на каждый. Было бы разумно ввести то, что вы знаете заранее (например, цену отдельных товаров и количество заказанного товара), но вы можете позволить Excel рассчитать итоговые суммы за вас. Для первого предмета, указанного ниже (карандаши), это можно сделать, умножив значение общей цены (ячейка D2), значение цены за единицу (содержащееся в ячейке C2) на количество заказанных предметов (содержащееся в ячейке D2). ). Эта формула будет записана как «=B2*C2».

После нажатия «Ввод» в ячейке отобразится рассчитанное значение, а в строке формул по-прежнему будет отображаться формула. (Примечание. Всегда нажимайте «Ввод», когда закончите вводить формулу вручную. Если вы щелкнете по ячейке, ячейка, на которую вы нажали, будет добавлена ​​к вашей формуле.) введите математическую формулу, если используются допустимые операторы. Обычно используемые операторы включают «+» (сложение), «-» (вычитание), «*» (умножение) и «/» (деление). (У Microsoft есть полный список допустимых операторов для использования в формулах Excel на веб-сайте Office). Вот несколько примеров формул, использующих общие операторы:

Формула Описание

= C2-B2 Содержание B2 из содержимого C2

= C2/B2 делят содержимое C2 на содержимое B2

= (B2+C2+D2)/3 добавляет содержимое B2. , C2 и D2 и делит результат на 3

Excel также имеет встроенные функции, которые могут выполнять множество полезных вычислений. Доступ к ним проще всего получить, нажав кнопку «Вставить функцию», которая представлена ​​символом « fx» рядом со строкой формул. Например, вместо того, чтобы вводить формулу, показанную выше, того же результата можно было бы добиться, используя встроенную функцию «ПРОДУКТ», щелкнув ячейку D2 и нажав кнопку «Вставить формулу». Это даст диалоговое окно, подобное показанному ниже.

После выбора «ПРОИЗВЕД» и нажатия кнопки «ОК» появится другое диалоговое окно, в котором можно выбрать ячейки для умножения. Вы можете сделать это для отдельных ячеек, выбрав ячейки отдельно в полях «Число1» и «Число2», показанных ниже, или выбрав массив ячеек, щелкнув и перетащив ячейки диапазона, которые вы хотите использовать в самой электронной таблице. . (Примечание: если вы пытаетесь ввести формулу в ячейку с помощью кнопки «Вставить формулу» и рядом есть ячейки с числами, Excel часто выбирает эти ячейки автоматически, поэтому убедитесь, что в диалоговом окне выбраны правильные ячейки. )

После того, как вы нажмете «ОК», ваша заполненная формула будет введена в ячейку.

Копирование и вставка формул

Часто вам потребуется Excel для выполнения ряда подобных вычислений, где меняются только ячейки, используемые в качестве аргументов. Например, в приведенном выше примере вы, вероятно, хотели бы, чтобы Excel рассчитал общую цену для каждого элемента в заказе. Вы можете повторно ввести ту же формулу, которая использовалась для получения общей стоимости карандашей в каждой ячейке в этой строке, просто изменив указанные ячейки (например, «=ПРОДУКТ(B3:C3)», «=ПРОДУКТ(B4:C4)», д.), но в Excel для этого есть более простой метод. Если у вас есть несколько ячеек в одной строке или столбце, которые должны выполнять одни и те же вычисления, вы можете просто скопировать значение в ячейку, в которую вы ввели формулу, а затем вставить его в последующие ячейки. Затем Excel автоматически отрегулирует, какие ячейки включены в формулу, в зависимости от того, в какую ячейку была вставлена ​​формула.