Как делать формулы в эксель таблицах

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

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

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.

Важно: В этой статье мы обсудим XLOOKUP и ВПР, которые похожи. Попробуйте использовать новую функцию ПРОСМОТРX, улучшенную версию функции ВПР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что делает ее проще и удобнее в использовании, чем предшественницу.

Создание формулы, ссылающейся на значения в других ячейках

1. Выделите ячейку.
2. Введите знак равенства » ocpAlert»>Примечание: Формулы в Excel начинаются со знака равенства.

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

Введите оператор. Например, для вычитания введите знак «минус».

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

Нажмите клавишу ВВОД. В ячейке с формулой отобразится результат вычисления.

Просмотр формулы

При вводе в ячейку формула также отображается в строке формул.

• Чтобы увидеть формулу в строке формул, выберите ячейку.

Ввод формулы, содержащей встроенную функцию

1. Выделите пустую ячейку.
2. Введите знак равенства «=», а затем — функцию. Например, чтобы получить общий объем продаж, нужно ввести «=СУММ».
3. Введите открывающую круглую скобку «(«.
4. Выделите диапазон ячеек, а затем введите закрывающую круглую скобку «)».
5. Нажмите клавишу ВВОД, чтобы получить результат.

Скачивание книги «Учебник по формулам»

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

Подробные сведения о формулах

Чтобы узнать больше об определенных элементах формулы, просмотрите соответствующие разделы ниже.

Части формулы Excel

Формула также может содержать один или несколько таких элементов, как функции, ссылки, операторы и константы.

Части формулы

1. Функции. Функция ПИ() возвращает значение числа пи: 3,142.

2. Ссылки. A2 возвращает значение ячейки A2.

3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.

4. Операторы. Оператор ^ (крышка) применяется для возведения числа в степень, а * (звездочка) — для умножения.

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

Константа представляет собой готовое (не вычисляемое) значение, которое всегда остается неизменным. Например, дата 09.10.2008, число 210 и текст «Прибыль за квартал» являются константами. выражение или его значение константами не являются. Если формула в ячейке содержит константы, а не ссылки на другие ячейки (например, имеет вид =30+70+110), значение в такой ячейке изменяется только после редактирования формулы. Обычно лучше помещать такие константы в отдельные ячейки, где их можно будет легко изменить при необходимости, а в формулах использовать ссылки на эти ячейки.

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

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

• Стиль ссылок A1 По умолчанию Excel использует стиль ссылок A1, в котором столбцы обозначаются буквами (от A до XFD, не более 16 384 столбцов), а строки — номерами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.
  

  Ячейка или диапазон	Использование
  Ячейка на пересечении столбца A и строки 10	A10
  Диапазон ячеек: столбец А, строки 10-20.	A10:A20
  Диапазон ячеек: строка 15, столбцы B-E	B15:E15
  Все ячейки в строке 5	5:5
  Все ячейки в строках с 5 по 10	5:10
  Все ячейки в столбце H	H:H
  Все ячейки в столбцах с H по J	H:J
  Диапазон ячеек: столбцы А-E, строки 10-20	A10:E20

• Создание ссылки на ячейку или диапазон ячеек с другого листа в той же книге В приведенном ниже примере функция СРЗНАЧ вычисляет среднее значение в диапазоне B1:B10 на листе «Маркетинг» в той же книге. 1. Ссылка на лист «Маркетинг». 2. Ссылка на диапазон ячеек от B1 до B10 3. Восклицательный знак (!) отделяет ссылку на лист от ссылки на диапазон ячеек.

Примечание: Если указанный лист содержит пробелы или числа, необходимо добавить апострофы (‘) до и после имени листа, например =’123′! A1 или =’Январь доход’! A1.

1. Относительные ссылки . Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании или заполнении формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании или заполнении относительной ссылки из ячейки B2 в ячейку B3 она автоматически изменяется с =A1 на =A2. Скопированная формула с относительной ссылкой
2. Абсолютные ссылки . Абсолютная ссылка на ячейку в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам и столбцам абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, а для использования абсолютных ссылок надо активировать соответствующий параметр. Например, при копировании или заполнении абсолютной ссылки из ячейки B2 в ячейку B3 она остается прежней в обеих ячейках: =$A$1. Скопированная формула с абсолютной ссылкой
3. Смешанные ссылки . Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец имеет вид $A1, $B1 и т. д. Абсолютная ссылка на строку имеет вид A$1, B$1 и т. д. Если положение ячейки с формулой изменяется, относительная ссылка меняется, а абсолютная — нет. При копировании или заполнении формулы по строкам и столбцам относительная ссылка автоматически изменяется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в ячейку B3 она автоматически изменяется с =A$1 на =B$1. Скопированная формула со смешанной ссылкой

• При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.
• Трехмерные ссылки нельзя использовать в формулах массива.
• Трехмерные ссылки нельзя использовать вместе с оператор пересечения (один пробел), а также в формулах с неявное пересечение.

Что происходит при перемещении, копировании, вставке или удалении листов . Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.

• Вставка или копирование . Если вставить листы между листами 2 и 6, Microsoft Excel прибавит к сумме содержимое ячеек с A2 по A5 на новых листах.
• Удаление . Если удалить листы между листами 2 и 6, Microsoft Excel не будет использовать их значения в вычислениях.
• Перемещение . Если листы, находящиеся между листом 2 и листом 6, переместить таким образом, чтобы они оказались перед листом 2 или после листа 6, Microsoft Excel вычтет из суммы содержимое ячеек с перемещенных листов.
• Перемещение конечного листа . Если переместить лист 2 или 6 в другое место книги, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.
• Удаление конечного листа . Если удалить лист 2 или 6, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Работа с формулами в Excel

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

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

Поиск перечня доступных функций в Excel

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

1. Откройте вкладку «Формулы» и нажмите на кнопку «Вставить функцию» либо разверните список с понравившейся вам категорией функций.
2. Вместо этого всегда можно кликнуть по значку с изображением «Fx» для открытия окна «Вставка функции».
3. В этом окне переключите категорию на «Полный алфавитный перечень», чтобы в списке ниже отобразились все доступные формулы в Excel, расположенные в алфавитном порядке.
4. Выделите любую строку левой кнопкой мыши и прочитайте краткое описание снизу. В скобках показан синтаксис функции, который необходимо соблюдать во время ее написания, чтобы все аргументы и значения совпадали, а вычисления происходило корректно. Нажмите «Справка по этой функции», если хотите открыть страницу о ней в официальной документации Microsoft.
5. В браузере вы увидите большое количество информации по выбранной формуле как в текстовом, так и в формате видео, что позволит самостоятельно разобраться с принципом ее работы.

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

Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей

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

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

Использование математических операций в Excel

Если необходимо выполнить математические действия с ячейками или конкретными числами, в Excel тоже создается формула, поскольку все записи, начинающиеся с «=» в ячейке, считаются функциями. Все знаки для математических операций являются стандартными, то есть «*»– умножить, «/» – разделить и так далее. Следует отметить, что для возведения в степень используется знак «^». Вкратце рассмотрим объявление подобных функций.

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

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

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

Растягивание функций и обозначение константы

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

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

В итоге вы должны увидеть, что функция растянулась на выбранный диапазон, а значения в ней подставлены автоматически. Так, изначальная функция имела вид =B2*C2, но после растягивания вниз последующие значения подставились автоматически (от B3*C3 до B13*C13, что видно на следующем изображении). Точно так же растягивание работает с СУММ и другими простыми формулами, где используется несколько аргументов.

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

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

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

В закрепление темы рассмотрим три константы, которые можно обозначить при записи функции:

• $В$2 – при растяжении либо копировании остаются постоянными столбец и строка.
• B$2 – неизменна строка.
• $B2 – константа касается только столбца.

Построение графиков функций

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

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

Работа в Excel с формулами и таблицами

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

Маркетинг

Читайте также:

Из чего состоят формулы «Эксель»

• знак «=», с которого начинается любое выражение;
• оператора, определяющего действие (сложение, умножение, возведение в степень и прочие);
• название функции, которая задает тип вычисления;
• ячейки, для которых нужно выполнить действие, или их диапазон.

Все действия в таблицах производятся в выбранных пользователем ячейках. Каждая из них имеет уникальный идентификатор — обозначения строки и столбца, на пересечении которых она располагается. Столбцы описываются буквами латинского алфавита, а строки — числами. Примеры идентификаторов ячеек — А2, B6, C12 и прочие.

Использование операторов

В формулах «Эксель» операторы нужны, чтобы программа «понимала», какие именно вычисления необходимо произвести. С их помощью складывают, умножают, сравнивают и объединяют значения из ячеек. Выделяют 9 типов действий:

• Арифметические. Это сложение (+), вычитание (-), умножение (*), деление (/), процент (%) и возведение в степень (^). Например, если нужно вычесть значение ячейки B4 из показателя ячейки A2, то формула будет выглядеть так: =A2-B4.

Бизнес не может обойтись без выполнения расчетов и анализа данных — для этого и необходим «Эксель». А для того, чтобы собрать всю информацию по маркетинговым кампаниям, подключите Сквозную аналитику Calltouch. Сервис отследит количество сделок, лидов и прибыли по каждой кампании и рассчитает ROI для всех каналов продвижения. Все это вы увидите в наглядном отчете и узнаете, какие площадки приносят доход, а какие — только расходуют бюджет.

Сквозная аналитика
Оценивайте эффективность всех рекламных кампаний в одном окне от клика до ROI
Вкладывайте в ту рекламу, которая приводит клиентов

• Сравнение. Сюда входят равенство (=), больше и меньше (>, <), больше или равно (>=), меньше или равно (<=), не равно (<>). Когда значения верны, в ячейке появляется слово «ИСТИНА», в противном случае — «ЛОЖЬ».
• Объединение текста. Чтобы соединить символы из нескольких ячеек, используют амперсанд (&). Для добавления пробела или какого-либо символа используют кавычки-лапки (“). Например: B3&” “&E2&” “&G8.
• Изменение естественного порядка действий в операциях. В Excel соблюдается стандартный порядок выполнения действий в числовых выражениях — сначала программа умножает, затем — складывает значения. Если сложение требуется выполнить в первую очередь, данные заключают в скобки — например: A2*(D4+E3).
• Добавление ссылок на ячейки. Чтобы одна ячейка отображала значение из другой, устанавливают ссылку с помощью знака «равно» и идентификатора ячейки — например: =A5.
• Добавление простых ссылок. Когда нужно выбрать диапазон, указывают первую и последнюю ячейки, а между ними ставят двоеточие (:). Если же требуется выбрать определенные ячейки, их разделяют точкой с запятой (;). Например: =СУММ(A1:A11) или =СУММ(A3;A8;A9).
• Добавление ссылок на другой лист. Чтобы добавить ссылку на другой лист, нужно написать его название, добавить восклицательный знак (!) и указать идентификаторы ячеек. Например: =Лист3!B6;B8.
• Добавление относительных ссылок. Если ячейку с формулой скопировать, она автоматически подстроится под столбец или строку в соответствии с заданным расположением. Например, если в A10 написать формулу =СУММ(A1:A9) и скопировать ее в B10, то формула примет вид: =СУММ(B1:B9).
• Добавление абсолютных ссылок. Иногда автоматический перенос формул требует корректировки. Зайдите в ячейку, выберите значение и нажмите на клавиатуре F4. Данные ячейки останутся неизменными. Формулу можно скопировать в другие ячейки.

Если в формулах в таблице Excel есть ошибки, программа идентифицирует их как «ЛОЖЬ» или просто не произведет вычисление.

Операции с формулами

Все операции с формулами Excel можно разделить на 6 типов:

• Копирование/вставка. Формулы дублируют с помощью выбора команд «Копировать» и «Вставить» правой кнопкой мыши или комбинаций клавиш Ctrl+C и Ctrl+V. При копировании ячейка, данные из которой вы переносите в другую, обрамляется подвижным пунктиром.
• Отмена операций. На панели быстрого доступа вверху располагается кнопка отмены действий в виде стрелки, повернутой влево. Также отменить операцию можно при помощи сочетания клавиш Ctrl+Z.
• Повторение действий. Если операцию отменили ошибочно, можно вернуться к предыдущему шагу, нажав Ctrl+Y. Также можно использовать кнопку на панели быстрого доступа — она выглядит как стрелка, повернутая вправо.
• Перетаскивание. Чтобы переместить ячейки из одного места в другое, нужно мышкой выделить диапазон и переместить курсор к одной из границ выделения. Появится четырехсторонняя стрелка, на которую необходимо нажать левой кнопкой мыши и, не отпуская, перетащить группу ячеек в другое место.
• Копирование путем перетаскивания. Оно производится так же, как и в предыдущем пункте, но после выделения диапазона нужно зажать на клавиатуре Ctrl.
• Автозаполнение формулами. Чтобы работать с этим инструментом, следует навести курсор на правый нижний край ячейки. Появится жирный черный крестик. Если на него нажать, можно продублировать формулу из одной ячейки на весь выделенный диапазон.

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

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

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

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

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

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

Анализируй и сегментируй звонки легко и быстро
Текстовая расшифровка звонков и автотегирование

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

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

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

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

Главное в статье

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

Как работать с формулами в Excel

Формулы в таблицах Excel делятся на простые, сложные и комбинированные.

Анастасия Хамидулина
Автор статьи
13 апреля 2022 в 17:48

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

Кому важно знать Excel и где выучить основы

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

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

Аналитик данных: новая работа через 5 месяцев
Получится, даже если у вас нет опыта в IT

На онлайн-курсе Skypro «Аналитик данных» научитесь владеть базовыми формулами Excel, работать с нестандартными данными, статистикой. Кроме Excel вы изучите Metabase, SQL, Power BI, язык программирования Python. Программа подойдет даже тем, у кого совсем нет опыта в анализе и кто не любит математику. Вас ждут живые вебинары, мастер-классы, домашки с разбором, помощь наставников.

Урок из курса «Аналитик данных» в Skypro

Из чего состоит формула в Excel

Как работать в гугл-таблицах

= с него начинают любую формулу;

( ) заключают формулу и ее части;

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

: ставят, чтобы обозначить диапазон ячеек, а не выбирать всё подряд вручную.

В таблицах Excel работают с простыми математическими действиями:

сложением +

вычитанием —

умножением *

делением /

возведением в степень ^

Еще в таблицах Excel используют символы сравнения:

равенство =

меньше

больше >

меньше либо равно

больше либо равно >=

не равно <>

Основные виды

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

Многие помнят их с уроков информатики в школе. Но даже если вы совсем не знакомы с таблицами Excel, на курсе Skypro «Аналитик данных» обучение начинают с основ. Не нужны глубокие знания математики и навыки работы с программами для анализа данных.

Простые

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

✅ СУММ. Складывает несколько чисел. Сумму можно посчитать для нескольких ячеек или целого диапазона в таблице.

=СУММ(А1;В1) — для соседних ячеек;

=СУММ(А1;С1;H1) — для определенных ячеек;

=СУММ(А1:Е1) — для диапазона.

Сумма всех чисел в ячейках от А1 до Е1

✅ ПРОИЗВЕД. Умножает числа в соседних, выбранных вручную ячейках или диапазоне таблицы.

Произведение всех чисел в ячейках от А1 до Е1

✅ ОКРУГЛ. Округляет дробное число до целого в большую или меньшую сторону. Укажите ячейку таблицы с нужным числом, в качестве второго значения — 0.

=ОКРУГЛВВЕРХ(А1;0) — к большему целому числу;

=ОКРУГЛВНИЗ(А1;0) — к меньшему.

Округление в меньшую сторону

✅ ВПР. Находит данные в таблице или определенном диапазоне.

=ВПР(С1;А1:В6;2)

• С1 — ячейка, в которую выписывают известные данные. В примере это код цвета.
• А1 по В6 — диапазон ячеек в таблице. Ищем название цвета по коду.
• 2 — порядковый номер столбца таблицы для поиска. В нём указаны названия цвета.

Формула вычислила, какой цвет соответствует коду

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

=СЦЕПИТЬ(А1;В1;С1) — текст без пробелов;

=СЦЕПИТЬ(А1;» «;В1;» «С1) — с пробелами.

Формула объединила три слова в одно предложение

✅ КОРЕНЬ. Вычисляет квадратный корень числа в ячейке таблицы.

=КОРЕНЬ(А1)

Квадратный корень числа в ячейке А1

✅ ПРОПИСН. Преобразует текст в верхний регистр, то есть делает буквы заглавными.

=ПРОПИСН(А1:С1)

Формула преобразовала строчные буквы в прописные

✅ СТРОЧН. Переводит текст в нижний регистр, то есть делает из больших букв маленькие.

=СТРОЧН(А2)

✅ СЧЕТ. Считает количество ячеек таблицы с числами.

=СЧЕТ(А1:В5)

Формула вычислила, что в диапазоне А1:В5 четыре ячейки с числами

✅ СЖПРОБЕЛЫ. Убирает лишние пробелы. Например, когда переносите текст из другого документа и сомневаетесь, правильно ли там стоят пробелы.

=СЖПРОБЕЛЫ(А1)

Формула удалила двойные и тройные пробелы

Сложные

✅ ПСТР. Выделяет определенное количество знаков в тексте, например одно слово.

=ПСТР(А1;9;5)

1. Введите =ПСТР.
2. Кликните на ячейку таблицы, где нужно выделить знаки.
3. Укажите номер начального знака: например, с какого символа начинается слово. Пробелы тоже считайте.
4. Поставьте количество знаков, которые нужно выделить из текста. Например, если слово состоит из пяти букв, впишите цифру 5.

В ячейке А1 формула выделила 5 символов, начиная с 9-го

✅ ЕСЛИ. Анализирует данные по условию. Например, когда в таблице нужно сравнить одно с другим.

=ЕСЛИ(A1>25;"больше 25";"меньше или равно 25")

В формуле указали:

• А1 — ячейку с данными;
• >25 — логическое выражение;
• больше 25, меньше или равно 25 — истинное и ложное значения.

Первый результат возвращается, если сравнение истинно. Второй — если ложно.

Число в А1 больше 25. Поэтому формула показывает первый результат — больше 25.

✅ СУММЕСЛИ. Складывает числа, которые соответствуют критерию. Обычно критерий — числовой промежуток или предел.

=СУММЕСЛИ(В2:В5;">10")

В формуле указали:

• В2:В5 — диапазон ячеек таблицы;
• >10 — критерий, то есть числа меньше 10 не будут суммироваться.

Число 8 меньше указанного в условии, то есть 10. Поэтому оно не вошло в сумму.

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

=СУММЕСЛИМН(D2:D6;C2:C6;"сувениры";B2:B6;"ООО ХY")

• D2:D6 — диапазон, из которого суммируем числа;
• C2:C6 — диапазон ячеек таблицы для категории;
• сувениры — условие, то есть числа другой категории учитываться не будут;
• B2:B6 — диапазон ячеек таблицы для компании;
• ООО XY — условие, то есть числа другой компании учитываться не будут.

Под условия подошли только ячейки D3 и D6: их сумму и вывела формула

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

В таблицах Excel можно комбинировать несколько функций: сложение, умножение, сравнение и другие. Например, вам нужно найти сумму двух чисел. Если значение больше 65, сумму нужно умножить на 1,5. Если меньше — на 2.

=ЕСЛИ(СУММ(A1;B1)<65;СУММ(A1;B1)*1,5;(СУММ(A1;B1)*2))

То есть если сумма двух чисел в А1 и В1 окажется меньше 65, программа посчитает первое условие — СУММ(А1;В1)*1,5. Больше 65 — Excel задействует второе условие — СУММ(А1;В1)*2.

Сумма в А1 и В1 больше 65, поэтому формула посчитала по второму условию: умножила на 2

Встроенные

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

1. Поместите курсор в нужную ячейку таблицы.
2. Откройте диалоговое окно мастера: нажмите клавиши Shift + F3. Откроется список функций.
3. Выберите нужную формулу. Нажмите на нее, затем на «ОК». Откроется окно «Аргументы функций».
4. Внесите нужные данные. Например, числа, которые нужно сложить.

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

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

Как скопировать

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

1. Впишите функцию в ячейку таблицы и кликните на нее.
2. Наведите курсор на правый нижний угол — курсор примет форму креста.
3. Нажмите левую кнопку мыши, удерживайте ее и тяните до нужной ячейки таблицы.
4. Отпустите кнопку. Появится итог.

Посчитали сумму ячеек в трех строках

Как обозначить постоянную ячейку

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

1. Нажмите на ячейку таблицы с формулой.
2. Поместите курсор в нужную ячейку таблицы и нажмите F4.
3. В формуле фрагмент с описанием ячейки таблицы приобретет вид $A$1. Если вы протянете формулу, то ссылка на ячейку $A$1 останется на месте.

Как поставить «плюс», «равно» без формулы

Когда нужна не формула, а данные, например +10 °С:

1. Кликните правой кнопкой по ячейке таблицы.
2. Выберите «Формат ячеек».
3. Отметьте «Текстовый», нажмите «ОК».
4. Поставьте = или +, затем нужное число.
5. Нажмите Enter.

2 ноября 18:00 МСК
Как без опыта и навыков гарантировано перейти на удаленную работу в 2023 году

Как сделать фильтр по столбцам в Google Sheets

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

Представим, что перед нами задача — собрать данные о клиентах: возраст до тридцати лет, из Москвы и Казани. Для этого нужно будет поработать с фильтром.

Таблица Excel. Работа с фильтром

Перейдите на любую ячейку таблицы, кликните «Создать фильтр».
Щелкните по выпадающему списку столбца «Город» и отметьте галочками только Москву и Казань. Нажмите «ОК».

Дальше точно так же кликните по выпадающему списку столбца «Возраст» и выберите опцию «Фильтровать по условию». Выберите в списке «Меньше или равно», а затем выставьте значение «30». Нажмите «ОК».
Если нужно вернуть исходный вид данных, можно сбросить настройки таблицы. Для этого перейдите по значку фильтра.
В онлайн-университете Skypro вы можете за пару месяцев пройти курс по Excel и получить востребованный навык для аналитики. Программа курса состоит из важнейших блоков: первичная обработка информации, работа со сложными данными и прогнозирование. Еще вы создадите проект по анализу бизнес-метрик.

Как создать сводную таблицу в Google Sheets

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

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

Сводная таблица в Google Sheets

Выделяем ячейку А1. Так программа поймет, c какими значениями нужно работать.

В верхнем меню «Вставка» выбираем «Создать сводную таблицу».

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

Сводная таблица в Google Sheets

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

Главное о формулах в Excel

• Формула состоит из математических знаков. Чтобы ее вписать, используют символы = ( ) ; : .
• С помощью простых формул числа складывают, умножают, округляют, извлекают из них квадратный корень. Чтобы отредактировать текст, используют формулы поиска, изменения регистра, удаления лишних пробелов.
• Сложные и комбинированные формулы помогают делать объемные вычисления, когда нужно соблюдать несколько условий.
• Значительно упростит жизнь аналитика возможность создавать сводную таблицу для расчетов и фильтровать данные по столбцам