Популярные формулы в excel: 15 формул в Excel, которые помогут в работе
Содержание
Наиболее часто используемые формулы в Excel: процент и маржа
Примеры, описанные в этом уроке, доступны в файле Excel: Часто Используемые Формулы.xlsx, только их самостоятельное написание гарантирует, что вы запомните этот урок.
Самые популярные формулы в Excel: расчет процентов и маржи
Зачастую знание самого Excelя оказывается недостаточным и необходимо знание основных математических формул.
Много раз я уже убеждался, что даже человек с базовым экономическим образованием сталкивался с проблемой расчета цены без НДС имея такие данные: цена с НДС составляет 100$ и ставка НДС — 23%, и с удивлением утверждавшего, что он не в состоянии это сделать.
Ниже я представляю решение проблем, с которыми чаще всего сталкивается обычный сотрудник или кандидат на должность, где требуется знание Excel.
Формулы расчета процентов в Excel
Процентное увеличение вычисляем по следующей формуле:
Увеличение процента = новая стоимость / старая стоимость — 1
Эта формула использовалась в следующем примере для расчета процентного увеличения, которое составило 25%.
Ту же формулу также можно написать в следующей форме:
Прибавление процента = (новая стоимость – старая стоимость) / старая стоимость
Иногда бывает, что процентное изменение за год рассчитывается «с другой стороны», в приведенном ниже примере мы можем сказать, что продажи в 2013 году были на 20% меньше, чем в 2014 году. Получатели такой информации недолго думая запоминают, что разница составляет 20%, тогда как на самом деле, как мы рассчитали в примере 1 (этот пример и предыдущий имеют одни и те же данные), она составляет 25%.
Это является преднамеренным введением в заблуждение получателя информации, и я не рекомендую использовать такой подход, разве что кто-то занимается политикой и должен придерживаться принятых в этой сфере стандартов.
Чтобы найти процентное отношение, например, какого-то товара в общем объеме продаж, мы делим объем продажи этого товара на общий объем продаж.
Процентное отношение = продажи товара A / общий объем продаж
Для увеличения заданного значения на процент, например, для увеличения цены на 23% налога на добавленную стоимость, служит следующая формула:
Стоимость с НДС = стоимость без учета НДС * (1 + процент изменения)
Аналогично выглядит формула уменьшения значения на процент, с той лишь разницей, что вместо знака плюс используется минус:
Новое значение = Старое значение * (1 — процент изменения)
Иногда процент, на который нам нужно что-то уменьшить, дается со знаком минус (уменьшить на -20%), что теоретически является ошибкой (два минуса должны давать плюс), но, к сожалению, такой вариант популярен и должен пониматься как уменьшение на 20%.
В таком случае используем ту же формулу, что и при увеличении на процент (минус уже в %).
Новое значение = Старое значение * (1 + проценты изменения)
И конечно же, уменьшить значение на процент — это одно, а «вывести» процент который был добавлен — совсем другое.
В следующем примере мы бы хотели бы найти цену без НДС от цены с НДС, НДС составляет 23 %.
Используем следующую формулу. Для того чтобы добавить определенный процент к значению, мы умножали значение на (1 + %), чтобы «вывести» процент — разделим значение на (1 + %) (деление является противоположным умножению действием).
Стоимость без НДС = Стоимость с НДС / (1 + % НДС)
Как вы можете видеть, в приведенном примере нам удалось получить первоначальное значение цены без НДС, показанного на двух примерах выше.
Многие люди задают вопрос, почему обратным действием для увеличения значения на какой-то процент не является уменьшение на тот же процент.
Давайте рассмотрим следующий пример, в котором цена была изменена дважды.
Начальная цена составляла 100$, она была увеличена на 10%, а после этого изменения она составляла 110$ (10% от 100 это 10, 10 + 100 = 110).
После первого изменения цена была снижена на 10% и в итоге составила 99$ (10% от 110 это 11). 110 -11 это 99).
Как видно, цена не вернулась к своему первоначальному значению. Чем больше было бы процентное изменение, тем больше была ба разница между начальной и окончательной ценой.
В приведенном ниже примере стоимость какой-то инвестиций увеличивается на 10% каждый год, мы хотели бы рассчитать, за сколько лет эта величина удвоится.
В первой из зеленых ячеек вводим формулу увеличения на процент и перетаскиваем ее в следующие ячейки. Удвоения этой суммы мы не должны ждать аж десять лет. Величина будет в два раза больше первоначальной суммы в седьмом году, а в восьмом значительно превысит свою двукратность. Происходит так, потому что процент во втором и каждом последующем инвестиционном году рассчитывается не из первоначальной суммы, а из уже увеличенной суммы.
означает степень)
В следующем примере сложных процентов некто задается вопросом, если бы он положил на вклад 1 000 $ под фиксированную процентную ставку в размере 5 % годовых и с ежегодной капитализацией процентов, были бы его пра-пра-праправнуки через 200 лет миллионерами.
После применения вышеприведенной формулы к таким данным, на вышеупомянутый вопрос мы получим утвердительный ответ. Через 200 лет на счету будет сумма более 17 миллионов $.
Если мы решим рассчитать ту же задачу с использованием 200 формул для увеличения на процент, отметим, что прирост суммы депозита на заключительном этапе несравненно больше, чем в начальные периоды. За последние несколько лет величина депозита увеличивалась почти на 1 млн. $ ежегодно.
Такой рост величины депозита в последующие периоды типичен для экспоненциальных функций, то есть тех, в которых переменная, в данном случае количество лет, находится в степени.
Должны ли мы теперь поспешить в банки с целью открытия таких депозитных счетов? Если бы мы хотели рассмотреть эти расчеты не как пример, а серьезно, следовало бы нам также учесть инфляцию, риск банкротства банка, риск девальвации валюты депозита или даже смену системы, национализацию банковских депозитов, дефолт государства или войну.
С учетом этих факторов, более разумным способом инвестирования 1 000 $, по-видимому, является использование их для повышения своей профессиональной квалификации, например, обучение Excel и VBA :-).
Читайте также: Прибавить и отнять процент в Excel от числа с примерами.
Формулы расчета маржи в Excel
Вычисление маржи для многих людей является большой проблемой, потому что они думают о добавлении маржи как о добавлении процента к заданной величине.
Фактически, маржа — это не процент от «накладных расходов», а процент, которым является прибыль в конечной цене продукта или услуги.
При данной стоимости и проценте маржи, цена рассчитывается по следующей формуле:
Цена = Стоимость / (1- процент маржи)
Маржа должна быть менее 100%, потому что невозможно продать что-то, зарабатывая при этом 100% и более, при этом каждая хозяйственная деятельность связана с некоторыми издержками.
В то же время, маржа может быть отрицательной, тогда компания продает свои товары или услуги ниже себестоимости и теряет на каждой операции.
Вопреки тому как может казаться, это не редкое явление, в некоторых отраслях, например, при продаже принтеров нормальным явлениям является их продажа ниже себестоимости. Производители покрывают эти потери продавая услуги и картриджи (чернила) с высокой маржей. Такая стратегия используется для упрощения привлечения клиентов.
Чтобы рассчитать маржу, зная стоимость и цену, используем следующую формулу:
Процент маржи = (цена – стоимость) / цена
Вышеуказанная формула также может быть записана как: процент маржи = 1 — стоимость / цена.
Однако, если искомым элементом является стоимость, мы используем следующую формулу, такую же, которую мы используем для уменьшения суммы на процент.
Другими словами, мы понижаем цену на размер маржи и получаем стоимость.
Стоимость = цена * (1 — маржа)
Средневзвешенная маржа от продаж
Для вычисления средней маржи мы не можем использовать обычное среднее значение, мы должны вычислить его с использованием средневзвешенного значения, где весом являются объёмы продаж.
Мы можем сделать это тремя способами:
- Используя первый способ, мы перемножаем каждую процентную маржу на соответствующий ей объем продаж, суммируем результаты и эту сумму делим на сумму всего объёма продаж. Эта формула даёт нам полный контроль над методом расчёта и позволяет понять, на чём основывается средневзвешенное значение.
- При использовании второго способа, нам служит функция СУММПРОИЗВ, которая суммирует произведения маржи и соответствующий ей объем продаж. Результат, который возвращает функция мы должны ещё разделить на общий объём продаж. Эта функция подробно описана в статье «Основные функции».
- Третий способ является для многих самым простым, но требуется создание дополнительного столбца с маржей от суммы каждой продажи. Значения в столбце K получаем путём умножения каждой процентной маржи на соответствующий ей объем продаж. Чтобы рассчитать средневзвешенную маржу, просто разделите сумму маржи на сумму всех продаж.
Читайте также: Как посчитать маржу и наценку в Excel.
Я предлагаю вам удалить содержимое всех зелёных полей в файле упражнений к этому уроку и самостоятельно ввести формулы без подглядывания в вышеприведённое описание. Если вам удастся ввести их все правильно, значит вы освоили этот материал, если нет — всегда можно начать снова и снова, и так пока не получите результат.
Топ 20 самых важных формул MS Excel
Microsoft Excel — один из самых популярных инструментов анализа данных в мире. Все больше фирмы используют это программное обеспечение и много людей ежедневно пользуются Excel. Однако, по моему опыту, только немногие из них в полной мере используют возможности, которые может предложить эта программа. Я представляю Вам мой список из топ 20 самых полезных формул Excel, которые каждый должен использовать для повышения эффективности. Функции Excel также очень рекомендуются для вычислений, так как они могут значительно уменьшить количество ошибок. И последнее, но не менее важное: изучение этих функций — прекрасная возможность улучшить Ваши знания Excel и стать уверенным пользователем MS Excel.
Table of Contents
1. Sum
«Sum» — это, наверное, самая простая, но и самая важная функция Exceл. Вы можете использовать эта формула для вычисления суммы диапазона ячеек.
= SUM(A1:A10) or = SUM(A1,A10)
В этом примере первая формула суммирует ячейки от A1 до A10. Вторая суммирует только ячейки A1 и A10. Вы можете попробовать с ним и попытаться использовать разные диапазоны, чтобы привыкнуть к этой функций. Обратите внимание, что вы также можете использовать числа внутри этой функции вместо ссылок.
2. Average
Еще одна очень важная функция. Как следует из названия, эта формула оценивает среднее число из диапазона ячеек. Предположим, что мы имеем диапазон чисел в столбце A.
= AVERAGE(A1:A10)
Это вернет среднее значение ячеек от A1 до A10.
3. If
Другая очень необходимая формула. Она относительно простая в использовании, но и очень мощная. Вы можете использовать эту функцию, чтобы проверить, является ли данный оператор истинным или ложным, и чтобы вернуть указанное число.
= IF(A2>100,"yes","no")
Здесь формула проверит если продажи в ячейке «A2» более 100 €. Если это заявление верно, формула возвращает «да». Если продажи ниже 100 евро, формула возвращает «нет».
4. Sumif
Sumif — еще одна очень важная формула Excel. Формула будет суммировать числа, если выполняется определенное условие.
= SUMIF(A1:A20,A2,B1:B20)
Давайте рассмотрим приведенный выше пример. У нас есть торговые агенты, которые регистрируют определенное количество продаж. Мы хотим узнать общее количество продаж для каждого агента. Функция = SUMIF (A1: A20, A2, B1: B20) суммирует значения ячеек B1: B20, которые соответствуют тексту в ячейке A2 (в случае «Ивайло»). Парень зарегистрировал общий объем продаж 325 €. Мы можем использовать формулу для расчета оборота других агентов.
5. Countif
Countif — очень полезная функция, которая работает как sumif. Он суммируется при определенном условии. Разница между sum и count состоит в том, что функция count суммирует количество ячеек, где определенное условие истинно.
Напротив, суммирующая функция суммирует значения внутри ячеек.
= COUNTIF(A1:A10,">100")
Формула в приведенном выше примере проверяет, сколько продаж были выше чем 100 евро и возвращает количество продаж. Вы также можете включить более одного критерия, используя countif для этой цели.
PS: Если вы не уверены в синтаксисе функции, вы всегда можете использовать функцию «insert function» в Excel (просто нажмите знак функции в левой части функциональной панели). В следующем примере мы продемонстрируем, как это работает.
6. Counta
Эта формула подсчитывает количество ячеек определенного диапазона, которые не являются пустыми. Это очень полезно, когда у вас есть тысячи строк, и вы хотите знать, где все это кончается.
= COUNTA(A:A)
Это даст вам количество строк, которые содержат значение, текст или любой другой символ.
7. Vlookup
Вероятно, самая мощная функция Excel. Очень полезно, когда вам приходится иметь дело с более чем одной таблицей, разными листами или даже другого файла.
Vlookup позволяет вам извлекать данные из одной таблицы в другую.
Продолжим этот пример, добавив еще один столбец «А» с номерами клиентов.
Обратите внимание, что есть информация о продажах и номерах клиентов, но имена клиентов отсутствуют. Однако у нас есть еще один лист Excel (Sheet2) с именами клиентов и номерами клиентов.
Вместо того, чтобы тратить свое время, чтобы посмотреть с Sheet1 на Sheet2 и искать совпадение вручную для каждой отдельной строки, мы можем использовать следующую формулу:
= VLOOKUP(A2,Sheet2!$A$1:$B$100,2,0)
Эта формула ищет значение в ячейке A2 (112 в нашем случае) в листе2, столбцы A & B (знаки «$» означают, что мы всегда хотим иметь постоянный диапазон поиска). Затем функция возвращает значение из второго столбца. Последняя часть формулы, мы пишем 0. Таким образом, мы делаем Excel не искать совпадение в порядке чисел.
Теперь попробуйте создать собственную формулу vlookup, используя опцию insert function.
В нашем случае это выглядит так:
8. Left, Right, Mid
Left, right и mid функции — очень важные функции, которые позволяют извлекать определенное количество символов из строки
= Left(A1,1) or = MID(A1,4,6) or = RIGHT(A1,5)
В нашем примере с формулой left мы берем первый символ с левой стороны. С формулой right мы можем сделать то же самое, но с символами справа налево. Имейте в виду, что пустое пространство также является символом.
9. Trim
Еще одна исключительная функция MS Excel. Функция trim удаляет пустые пространства после слов, когда их больше одного. Это может случиться довольно часто в Excel. Возможно, что информация извлекается из разных баз данных и заполняется ненужными пустым пространством между словами. Это может быть огромной проблемой, потому что ваши формули Excel могут не работать. Чтобы избавиться от раздражающих пустых пространств, мы используем формулу trim.
= TRIM(A1)
В этом примере формула удалит лишние пробелы, если она найдет несколько.
10. Concatenate
Это удобная функция Excel, которая помогает, когда мы хотим объединить ячейки.
Здесь мы хотим объединить ячейки A1, A2 и A3. Это можно сделать с помощью следующей функции:
= CONCATENATE(A1,A2,A3)
Таким образом, у нас не будет пробелов между словами. Чтобы добавить пробелы между «I» и «love», нам нужно добавить пробел. Это можно решить, добавив кавычки внутри функции:
= CONCATENATE(A1," ",A2," ",A3)
PS: Вы также можете объединить ячейки, просто добавив:
= A1&" "&A2&" "&A3
Попробуйте сами!
11. Len
Еще одна прекрасная формула, которая может применяться во многих ситуациях. Она широко используется для построения более сложных формул.
= LEN(A1)
Эта функция будет подсчитывать количество символов в ячейке.
12. Max
Функция max возвращает наибольшее значение из диапазона.
= MAX(A2:A8)
Эта формула будет выглядеть в ячейках A2: A8, и тогда она извлечет самое высокое значение из этого диапазона.
13. Min
Функция min функционирует аналогично функции max, однако она дает противоположный результат. Он извлекает самое низкое значение из диапазона данных.
= MIN(A2:A8)
Формула из приведенного выше примера вернет наименьшее число из выбранных ячеек в столбце «A».
14. Days
Это функция, когда вам нужно вычислить количество дней между двумя днями в электронной таблице.
= DAYS(B1,A1)
Формула вернет количество прошедших дней.
15. Networkdays
Иногда вы хотите работать только с рабочими днями. Эта функция работает очень похоже на приведенный выше пример. Разница в том, что он возвращает рабочие дни, прошедшие, за исключением выходных.
16. SQRT
Это довольно просто и довольно полезно одновременно. Как следует из названия, формула дает квадратный корень из числа
= SQRT(1444)
Это вернет квадратный корень из 1444 (38). Вы также можете использовать ссылку на ячейку здесь, как и во многих других формулах Excel.
17. Now
Иногда вам нужно знать текущую дату, когда вы открываете электронную таблицу Excel.
= NOW()
Это вернет текущую дату. Не забудьте установить формат на сегодняшний день. Вы можете попробовать с ним столько, сколько хотите. Вы можете, например, добавлять или вычитать дни. = Now () -14 вернет дату до двух недель.
18. Round
Функция Round очень полезна, когда вам приходится работать с округленными номерами. Excel может отображать числа для определенного символа, но сохраняет исходный номер и использует его для вычислений. Однако в некоторых случаях это может быть проблемой, и нам нужно работать только с округленными номерами. В таких случаях функция ROUND обращается к нашей помощи.
= ROUND(B1,2)
В этом примере число округляется до второго десятичного знака.
19.Roundup
Это вариация формулы round, которая может очень помочь в некоторых случаях. Онa округляет значение до ближайшего целого числа. Это очень полезно в сочетании с другими формулами Excel.
Давайте посмотрим на этот пример:
= ROUNDUP(MONTH(B3)/3,0)
Здесь мы хотим найти в каком квартале текущего года находится текущую дату. Секрет этой формулы — простая математика. Функция принимает номер месяца, например, январь равен 1, февраль равен 2 и т. Д., А затем делит его на 3, округляя до ближайшего большего числа.
20. Rounddown
Rounddown работает аналогично Roundup, однако она округляет число до ближайшего целого числа
= ROUNDDOWN(20.65,0)
Это вернет 20.
Excel имеет много полезных функций. В этой статье я попытался представить наиболее важные формулы в MS Excel наилучшим образом. Я считаю, что эти функции являются очень хорошей базой для тех из вас, кто хочет развивать свои умения Excel. Посредством освоения этих формул вы можете легко справляться с повседневными ситуациями, которые могут возникать при работе с Excel, и это действительно хорошая возможность для дальнейшего развития ваших навыков.
© 2017 Атанас Йонков
Прочитайте больше: 18 готовые макросы в VBA Excel
Literature:
1.
ExcelChamps.com: The Top 15 Excel Functions you NEED to know — Excel by Joe.
3. BG Excel.info: Top 11 most popular Excel functions.
15 функций анализа данных Excel, которые вам нужно знать
– Excel with Business
Если вы когда-либо использовали Excel, то, вероятно, вам приходилось мучиться, выбирая неправильную формулу для анализа набора данных. Возможно, вы работали над ним часами, в конце концов сдавшись, потому что вывод данных был неправильным или функция была слишком сложной, и казалось более простым подсчитывать данные самостоятельно вручную. Если это похоже на вас, то этот анализ данных в Excel Top 15 для вас.
В Excel есть сотни функций, и может быть сложно сопоставить правильную формулу с правильным анализом данных. Самые полезные функции не обязательно должны быть сложными. Пятнадцать простых функций улучшат вашу способность анализировать данные, заставив задуматься, как вы раньше жили без них.
Независимо от того, балуетесь ли вы Excel или интенсивно используете его в своей работе, в этом списке найдется функция для каждого.
1. СЦЕПИТЬ
=СЦЕПИТЬ — одна из самых простых в освоении, но наиболее эффективных формул при проведении анализа данных. Объединяйте текст, числа, даты и многое другое из нескольких ячеек в одну. Это отличная функция для создания конечных точек API, артикулов продуктов и запросов Java.
Формула:
= СЦЕПИТЬ (ВЫБЕРИТЕ ЯЧЕЙКИ, КОТОРЫЕ ХОТИТЕ ОБЪЕДИНИТЬ)
В этом примере:
=СЦЕПИТЬ(A2,B2)
2. ДЛСТР
=ДЛСТР быстро предоставляет количество символов в данной ячейке. Как и в приведенном выше примере, вы можете определить два разных типа товарных складских единиц (SKU), используя формулу =LEN, чтобы узнать, сколько символов содержит ячейка. LEN особенно полезен при попытке определить различия между различными уникальными идентификаторами (UID), которые часто бывают длинными и расположены не в правильном порядке.
Формула:
=ДЛСТР(ВЫБРАТЬ ЯЧЕЙКУ)
В этом примере:
=ДЛСТР(A2)
3.
СЧЁТЧ
=СЧЁТЧАС определяет, пуста ячейка или нет. В жизни аналитика данных вы будете ежедневно сталкиваться с неполными наборами данных. COUNTA позволит вам оценить любые пробелы в наборе данных без необходимости реорганизации данных.
Формула:
=СЧЁТЕС(ВЫБРАТЬ ЯЧЕЙКУ)
В примере:
=СЧЕТЧ(A10)
4. ДНИ/ЧИСТРАБДНИ
=ДНИ — это именно то, что подразумевается. Эта функция определяет количество календарных дней между двумя датами. Это полезный инструмент для оценки жизненного цикла продуктов, контрактов и дохода от рейтинга пробега в зависимости от продолжительности обслуживания — анализ данных необходим.
=ЧИСТРАБДНИ немного надежнее и удобнее. Эта формула определяет количество «рабочих дней» между двумя датами, а также возможность учета праздников. Даже трудоголикам время от времени нужен перерыв! Использование этих двух формул для сравнения временных рамок особенно полезно для управления проектами.
Формулы:
= дни (Select Cell, Select Cell)
или
= NetworkDays (Select Cell, Select Cell, [numberOfHolidays])
Примечание: [NumberOfholidays] является необязательным
.
пример:
=ДНИ(C8,B8)
ИЛИ
=ЧИСТРАБДНИ(B7,C7,3)
5. СУММЕСЛИМН
=СУММЕСЛИМН – одна из формул, которую необходимо знать аналитику данных. Используется стандартный метод =СУММ, но что, если вам нужно суммировать значения на основе нескольких критериев? СУММЕСЛИ — это. В приведенном ниже примере функция СУММЕСЛИМН используется для определения вклада каждого продукта в общий доход.
Формула:
=СУММЕСЛИ(ДИАПАЗОН,КРИТЕРИИ,[сумма_диапазон])
примечание: [сумма_диапазон] не является обязательным
$2:$A$28,$F2)
6. СРЗНАЧСЛИМН
Подобно СУММЕСЛИМН, СРЗНАЧСЛИМН позволяет получить среднее значение на основе одного или нескольких критериев.
Формула:
=СРЗНАЧЕСЛИ(ВЫБЕРИТЕ ЯЧЕЙКУ, КРИТЕРИИ,[СРЕДНИЙ_ДИАПАЗОН])
примечание: [средний_диапазон] не является обязательным
В примере:
=СРЗНАЧЕСЛИ($C:$C,$A:$A,$F2)
7.
ВПР
ВПР — одна из самых полезных и узнаваемых функций анализа данных. Как пользователь Excel, вам, вероятно, в какой-то момент понадобится «объединить» данные. Например, отдел дебиторской задолженности может знать, сколько стоит каждый продукт, но отдел доставки может предоставить только отгруженные единицы. Это идеальный вариант использования ВПР.
На изображении ниже мы используем справочные данные (A2) в сочетании с таблицей цен, чтобы Excel искал критерии соответствия в первом столбце и возвращал соседнее значение.
Формула:
= ВПР(ПРОСМОТР_ЗНАЧ, ТАБЛИЦА_МАССИВ, COL_INDEX_NUM, [ДИАПАЗОН_ПРОСМОТР])
В примере:
=ВПР($05 90$1,08)80$5, НАЙТИ/ПОИСК
=ПОИСК/=ПОИСК — это мощные функции для выделения определенного текста в наборе данных. Оба перечислены здесь, потому что =НАЙТИ вернет совпадение с учетом регистра, т. е. если вы используете НАЙТИ для запроса «Большой», вы вернете только результаты «Большой=истина».
Но =ПОИСК для «Большой» будет соответствовать Большому или большому, делая запрос немного шире. Это особенно полезно для поиска аномалий или уникальных идентификаторов.
Формула:
=ПОИСК(ТЕКСТ,WITHIN_TEXT,[START_NUMBER]) ИЛИ =ПОИСК(ТЕКСТ,WITHIN_TEXT,[START_NUMBER])
примечание: [start_number] не является обязательным и используется для обозначения начальной ячейки в тексте для поиска
В примере:
=(НАЙТИ(«Большой», A2,1)»»)
9. ЕСЛИОШИБКА
=ЕСЛИОШИБКА — это то, чем должен воспользоваться любой аналитик, который активно представляет данные . Используя предыдущий пример, поиск определенного текста/значений в наборе данных не даст совпадения. Это вызывает ошибку #ЗНАЧ, и, хотя это безвредно, это отвлекает и мозолит глаза.
Используйте =IFERROR, чтобы заменить ошибки #VALUE любым текстом/значением. В приведенном выше примере ячейка пуста, поэтому потребители данных могут легко выбрать, какие строки вернули совпадающее значение.
Формула:
=ЕСЛИОШИБКА(НАЙТИ»ЗНАЧЕНИЕ»,ВЫБЕРИТЕ ЯЧЕЙКУ,ЗНАЧ_ЕСЛИ_ОШИБКА)
В примере:
=ЕСЛИОШИБКА(НАЙТИ»БОЛЬШОЕ»,A6,1),»»)
10. СЧЁТЕСЛИМН
9
9 =СЧЁТЕСЛИМН — это самый простой способ подсчитать количество экземпляров набора данных, удовлетворяющих набору критериев. В приведенном выше примере название продукта используется для определения того, какой продукт был самым продаваемым. СЧЁТЕСЛИМН мощна из-за безграничных критериев, которые вы можете ввести.
Формула:
=СЧЁТЕСЛИМН(ДИАПАЗОН,КРИТЕРИИ)
В примере:
=СЧЁТЕСЛИМН($A:$A,$F9)
простые методы извлечения статических данных из ячеек. =LEFT вернет «x» символов от начала ячейки, а =right вернет «x» символов от конца ячейки. В приведенном ниже примере =LEFT используется для извлечения кода города потребителя из его номера телефона, а =RIGHT используется для извлечения последних четырех цифр.
Формула:
= слева (выберите ячейку, номер)
или
= правый (выберите ячейку, номер)
В этом примере:
= слева (A6, 3)
и
= ПРАВО(A6,4)
12.
РАНГ
=РАНГ — это древняя функция Excel, но это не умаляет ее эффективности для анализа данных. =RANK позволяет быстро определить ранжирование значений в наборе данных в порядке возрастания или убывания. В примере RANK используется для определения того, какие клиенты заказывают больше всего товаров.
Формула:
=RANK(SELECT CELL,RANGE_TO_RANK_AGAINST,[ORDER])
примечание: [порядок] не является обязательным
$7,0)
примечание: 0 возвращает наибольшее значение с рангом #1
13. MINIFS
=MINIFS очень похожа на функцию min, за исключением того, что она позволяет вам взять минимальное значение из набора значений и сопоставить их. также по критериям. В примере =MINIFS используется для поиска самой низкой цены, по которой продается каждый продукт.
Формула:
=МИНИФС(ДИАПАЗОН1,КРИТЕРИИ1,ДИАПАЗОН2)
В этом примере:
=МИНИФС($B$B,$A:$A,$E5) =
IFS 19.
MAXIFS, как и его аналог minifs, позволяет вам сопоставлять критерии, но на этот раз он ищет максимальное число.
Формула:
=МАКСЕСЛИ(ДИАПАЗОН1,КРИТЕРИИ1,ДИАПАЗОН2)
В этом примере:
=МАКСЕСЛИМН($B$B,$A:$A,$E5)
15. СУММПРОИЗВ9
— отличная функция для расчета средней доходности, ценовых пунктов и маржи. СУММПРОИЗВ умножает один диапазон значений на соответствующие ему аналоги строки. Это золото анализа данных. В приведенном ниже примере мы рассчитываем среднюю цену продажи всех наших продуктов, используя сумму продукта, умноженную на цену на количество, а затем делим на общий проданный объем.
Формула:
=СУММПРОИЗВ(ДИАПАЗОН1, ДИАПАЗОН2)/ВЫБЕРИТЕ ЯЧЕЙКУ
В этом примере:
=СУММПРОИЗВ(B2:B9,C2:C9)/C10
Мы надеемся, что вы нашли это полезным. Если вас интересует анализ данных в Excel, взгляните на курс Excel, который помог сотням тысяч людей освоить Excel.
20+ самых популярных формул Excel для начинающих, которые вы должны знать
Возможно, вы знакомы с Microsoft Excel.
Это приложение, созданное корпорацией Microsoft, является приложением с наибольшим количеством пользователей в мире в более чем 140 странах. Это приложение имеет множество формул Excel, которые могут точно представлять числа и данные.
Основной функцией Microsoft Excel является расчет, поэтому его используют многие пользователи, как студенты, так и офисные работники. Несмотря на другие программы для работы с электронными таблицами (конкуренты), Excel сохранил свое господство и даже стал отраслевым стандартом.
Судя по всему, Excel слишком хорош, чтобы быть правдой. Все, что вам нужно сделать, это ввести формулу Excel, и почти все, что вам нужно сделать вручную, можно сделать автоматически. Используйте лучшее программное обеспечение для бухгалтерского учета, чтобы правильно прогнозировать движение денежных средств и более оптимально анализировать финансовые отчеты.
Если вам нужно объединить два листа с одинаковыми данными, Excel может это сделать. Вам нужна помощь с математикой? Эксель может это сделать.
Используете ли вы данные из нескольких ячеек? Эксель тоже может это сделать.
В этой статье вы узнаете о функциях Excel в различных профессиональных областях. Тогда вы поймете, какие формулы вам нужно знать. Позже вы сможете применить автоматические расчеты, которые будут использоваться в соответствии с вашими потребностями.
Содержание
- Использование Excel в профессиональных областях
- Полные формулы Excel с их функциями
- Заключение
Использование Excel в профессиональных областях
Многие вакансии требуют от кандидатов владения Excel. Неудивительно, что Excel нужен для самых разных сфер деятельности, требующих сбора данных.
С этой целью мы представим следующие профессии, которые могут извлечь выгоду из использования Excel, среди прочего:
Бухгалтерия
Бухгалтеры широко используют Excel.
Область бухгалтерского учета выигрывает от использования Microsoft Excel несколькими способами, включая упрощение расчета корпоративных прибылей и убытков, определение больших прибылей за период, расчет заработной платы сотрудников и многое другое.
Бухгалтер может облегчить свою работу, используя бухгалтерскую систему как рабочий инструмент. Система может обеспечить удобство в управлении финансовым оборотом компании и оптимальном и точном его расчете.
Читайте также: Заработная плата: понимание, льготы, формат, пример
Математические расчеты
Следующим преимуществом использования Microsoft Excel являются математические расчеты. Используйте вычисления, чтобы найти данные из чисел, вычитаний, умножений и делений, а также различные другие варианты, такие как вычисление наклона.
Обработка данных
В управлении данными есть полезные формулы Excel, включая управление статистическими базами данных, расчет данных, поиск медианных значений, средних значений и поиск максимальных и минимальных значений наборов данных.
Создание графики
Excel позволяет создавать графику. Например, график данных о развитии населения за один год, график посещений студенческой библиотеки за один год, график выпуска студентов за шесть лет, график роста количества студентов за три года и другие.
Табличные операции
Последнее приложение для управления столом. Всего 1 084 576 строк и 16 384 столбца в Microsoft Excel упростят ввод данных, для которых требуется большое количество столбцов и строк.
Источник: versionmuseum.com
Полные формулы Excel с их функциями
В дополнение к функциям и использованию Microsoft Excel, упомянутых ранее, для работы с этой программой Excel требуются формулы Excel. Каждая формула имеет другую функцию, которую вы можете использовать для определенных нужд.
Вот формулы Excel, которые вам нужно знать, среди прочего:
| Формулы | Функциональное описание |
| СУММА | Подведение итогов |
| СРЕДНЯЯ | Поиск средних значений |
| И | Поиск значения путем сравнения и |
| НЕ | Поиск значения с исключениями |
| ИЛИ | Нахождение значения путем сравнения или |
| ОДИНОЧНЫЙ, ЕСЛИ | Поиск значений, если условия ВЕРНЫЕ/НЕПРАВИЛЬНЫЕ |
| МУЛЬТИ ЕСЛИ | Поиск значений, если условия ПРАВИЛЬНЫ/НЕПРАВИЛЬНЫ со многими сравнениями |
| ОБЛАСТИ | Просмотр количества областей (диапазона или ячеек) |
| ВЫБЕРИТЕ | Просмотр результатов выбора на основе номеров индексов |
| ГПР | Поиск данных из таблицы, организованной в горизонтальном формате |
| ВПР | Поиск данных из таблицы, расположенной в вертикальном формате |
| СПИЧКА | Отображает положение определенного адреса ячейки |
| СЧЁТЕСЛИ | Подсчет количества ячеек в диапазоне с определенными критериями |
| КОНТА | Подсчет количества заполненных ячеек |
| ПОТОЛОК | Округлить число в большую сторону |
| ЭТАЖ | Округляет числа в меньшую сторону |
| ДЕНЬ | В поисках ценности дня |
| МЕСЯЦ | В поисках ценности Луны |
| ГОД | Поиск значения года |
| ДАТА | Получить значение даты |
| НИЖНИЙ | Изменить буквы текста на строчные |
| ВЕРХНИЙ | Изменить буквы текста на верхний регистр |
| ПРАВИЛЬНЫЙ | Изменить начальный символ текста на верхний регистр |
23 формулы Excel
Для получения дополнительной информации о том, как использовать формулы функций Excel, упомянутые выше, вот подробное описание, а также примеры:
СУММА
Эта формула суммирования Excel имеет основную функцию суммирования чисел в определенных ячейках.
Тем не менее, он также часто используется для быстрого выполнения работы или задачи.
Чтобы использовать СУММ, сначала создайте таблицу суммирования и введите приведенную ниже формулу суммирования. Например, =СУММ(G8:H8) или как показано на рисунке ниже:
Затем, если была введена формула СУММ, нажмите клавишу ввода, чтобы указать сумму. Как показано на рисунке ниже:
СРЕДНЯЯ
Основная функция формулы СРЗНАЧ или формулы среднего значения в Excel — найти среднее значение переменной. Хитрость заключается в том, чтобы создать таблицу оценок, а затем ввести формулу СРЕДНИЙ, чтобы определить средний балл учащегося. Например, =СРЗНАЧ(D2:F2) , как на следующем рисунке:
Если вы вводите формулу СРЕДНЕГО, нажмите Enter, чтобы увидеть результат, показанный на изображении ниже.
И
Функция формулы И генерирует значение ИСТИНА, если все ранее проверенные аргументы верны, или может возвращать значение ЛОЖЬ, если все аргументы или ответы неверны.
Чтобы определить ИСТИНА или ЛОЖЬ, необходимо создать таблицу и ввести ФОРМУЛУ И. Например, =И(G7>I7).
Этот метод обычно используется, чтобы помочь заполнить анкеты или ответить на столбцы вопросов, чтобы ускорить процесс установления ценности.
НЕ
Формула НЕ выполняет функцию, противоположную формуле И, поскольку она дает ИСТИНА, если проверяемое условие равно ЛОЖЬ, и ЛОЖЬ, если проверяемое условие равно ИСТИНА.
Первый шаг — создать таблицу и ввести ФОРМУЛУ НЕ для того, чтобы узнать результаты. Например, как показано на диаграмме ниже, =НЕ(G7>J7).
ИЛИ
ИЛИ следующая формула Excel выдаст ИСТИННЫЕ данные, если какой-либо заданный аргумент верен. Если все представленные аргументы неверны, ответ ЛОЖЬ.
Средний результат теста учащегося является примером того, как OR может использовать его в Excel. Если он меньше 70, он должен повторить курс; если он больше 70, студент заканчивает обучение.
ОДИНАРНАЯ, ЕСЛИ
Функция ЕСЛИ должна возвращать значение, которое при проверке равно ИСТИНА, и другое значение, видимое как ЛОЖЬ.
Не сильно отличается от техники использования формул в ИЛИ. Это просто кажется более простым.
Например, если средний балл учащегося ниже 75, этот учащийся не заканчивает учебу, и наоборот. Как создать формулу ЕСЛИ: =ЕСЛИ(J8<75;"НЕ ВЫПУСКАЕТ";"ПРОШЕЛ") .
Для ясности взгляните на следующее изображение:
После ввода формулы нажмите ввод, и результат будет соответствовать тому, что вы заказывали.
МУЛЬТИ ЕСЛИ
Эта формула является еще одним уроком из ОДИНАРНОГО ЕСЛИ, но если ЕДИНСТВЕННОЕ ЕСЛИ использует только одно условие с двумя вариантами, то в МНОЖЕСТВЕННОМ ЕСЛИ используются два условия и три варианта.
Двойная/множественная ФОРМУЛА — это тип формулы логического ЕСЛИ, состоящей из 2 или более ЕСЛИ при написании формулы. Если для одного IF требуется только 1 IF, например, =IF(B2=>=70;»Выпускной»;»Неудачный») , то Двойной ЕСЛИ требуется более 1 ЕСЛИ, например =ЕСЛИ(B2>=70;»Хорошо»;ЕСЛИ(B2>=50;»Достаточно»;»Меньше»)) .
Чтобы полная формула Excel обеспечивала синтаксис для двойного / множественного ЕСЛИ, среди прочего:
=ЕСЛИ(Логическая_Проверка; Значение_Если_Истина; ЕСЛИ(Логическая_Проверка; Значение_Если_Истина; Значение_Если_Ложь))
ОБЛАСТИ
Вы можете использовать удобство использования ОБЛАСТИ, если хотите рассчитать количество областей, которые вы хотите выбрать.
Формула ОБЛАСТИ может использовать формулу =ОБЛАСТИ(G6:K8) .
В результате получается одна область. Результаты основаны на примере на изображении выбора только одного диапазона ячеек.
ВЫБЕРИТЕ
Основная функция формулы ВЫБОР заключается в отображении результатов выбора на основе номеров индексов или последовательностей в ссылке (ЗНАЧЕНИЕ), которые содержат имена текста, числа, формулы или диапазона. На изображении ниже показан пример написания формулы ВЫБОР.
Тем временем результаты формулы ВЫБОР показаны на изображении ниже.
ПРОСМОТР
Функция ГПР отображает данные из таблиц, расположенных горизонтально. Однако расположение таблиц или данных в первой строке должно основываться на последовательностях от маленьких до больших или повышать их.
Например, цифры 1,2,3,4… Или буква A-Z. Сортировка по возрастанию меню, если вы ранее вводили случайно. Случаи, подобные перечисленным ниже, являются примерами.
=HLOOKUP(Ячейки, которые содержат типы пакетов; Диапазон стоимости данных таблицы ячеек; Расположение столбца данных, который вы хотите получить; 0) .
ВПР
Основная функция формулы ВПР почти идентична формуле ГПР; Разница в том, что формула ВПР отображает данные из таблиц, расположенных в прямом или вертикальном формате. Требование состоит в том, чтобы первая строка таблицы данных была подготовлена в порядке от меньшего к большему или выше.
Например: 1,2,3,4… Или буква A-Z. Например: 1,2,3,4… Или буква A-Z. Отсортируйте в меню по возрастанию, если вы ранее вводили в случайном порядке.
СПИЧКА
Функция ПОИСКПОЗ — это один из компонентов формулы Excel, которую можно использовать для выполнения поиска или поиска справочных данных.
Функция ПОИСКПОЗ работает, находя относительное положение значения в диапазоне или массиве и генерируя число, которое является индексом относительного положения ячейки, содержащей искомое значение.
Использование функции ПОИСКПОЗ Excel должно соответствовать правилам синтаксиса, а именно ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_совпадения])
Чтобы лучше понять, посмотрите на изображение ниже:
Источник: je-excel
СЧЁТЕСЛИ
СЧЁТЕСЛИ — полезная формула для подсчёта количества ячеек с одинаковыми критериями. В результате вам больше не придется мучиться с сортировкой данных.
Предположим, вы проводите опрос. Вы хотите узнать, сколько государственных служащих среди 20 опрошенных. В итоге можно использовать следующие формулы:
Источник: битлабс
Для начала все, что вам нужно сделать, это ввести диапазон ячеек, которые вы хотите идентифицировать, в формулу СЧЁТЕСЛИ (B2:B21)
Затем введите критерий расчета ячейки, а именно «PNS» (должен быть в кавычках).
Эта формула дает результат 7. Таким образом, из 20 респондентов семеро были государственными служащими.
КОНТА
COUNTA имеет функцию подсчета количества ячеек, содержащих числа, и количества ячеек, содержащих что-либо. В результате можно определить количество непустых ячеек.
Предположим, что ячейки с A1 по D1 содержат слова, а ячейки с G1 по I1 — числа. Поэтому использование формулы =СЧЁТЧАСТЬ(A1:I1)
Источник: битлабс
В результате получается 7, потому что пустыми ячейками являются только E1 и F1.
ПОТОЛОК
Формула CEILING может округлить число в большую сторону до нескольких значений определенного числа. Формула: ПОТОЛОК (количество КЛЕТОК; кратность).
Мисальня, ПОТОЛОК(G9;100).
Тогда число на Cells G9 будет округлено до кратного 100.
ЭТАЖ
Если ранее формула использовалась для округления до ближайшего целого числа и выше, формула FLOOR используется для округления до ближайшего целого числа. Формула почти идентична формуле ПОТОЛКА, но с добавлением ПОЛА.
Формула FLOOR: FLOOR (Число ячеек; Несколько).
ДЕНЬ
Формула ДЕНЬ используется для нахождения дня данных типа даты (в числах 1-31). Рассмотрим функцию ДЕНЬ (столбец B). Данные даты в столбце A будут извлечены и преобразованы в числа от 1 до 31 в соответствии с объяснением на изображении:
- .
После ввода формулы =ДЕНЬ(ЧИСЛО) нажмите Enter и посмотрите результаты ниже:
МЕСЯЦ
Почти аналогично формуле ДЕНЬ, использование формулы МЕСЯЦ для поиска месяцев (в числах 1-12) из данных типа даты.
Например, использование функции МЕСЯЦ (столбец K) данных даты столбца I после его извлечения дает числа 1-12, как показано на рисунке ниже:
ГОД
Между тем, есть формула ГОД. Способ использования этой формулы аналогичен тому, как использовались предыдущие две формулы. Использование формулы YEAR занимает годы (в диапазоне 1900-9999) от данных типа даты.
ДАТА
Формула ДАТА имеет функцию, которая позволяет получить типы данных даты путем ввода лет, месяцев и дней. Функция ДАТА противоположна функциям ДЕНЬ, МЕСЯЦ и ГОД, которые описывают данные типа месяца и года по соответствующим датам.
Данные о годе, месяце и дне в виде чисел в сочетании с функцией DATE создают данные с типами даты, как показано на рисунке ниже. Формула даты написания изображения, а именно:
НИЖНЯЯ
Нижняя формула преобразует все прописные буквы в тексте в строчные.
Чтобы использовать формулу, введите команду LOWER, и тексты преобразуют желаемое написание в нижний регистр.
Например, при реализации формулы LOWER с формулой =LOWER (не забудьте использовать программное обеспечение HashMicro) результатом будет «не забудьте использовать программное обеспечение hashmicro».
ВЕРХНИЙ
В дополнение к нижним формулам Microsoft Excel включает верхние формулы. Использование ПРОПИСНОЙ заключается в преобразовании всего текста, содержащего строчные буквы, в прописные, что является противоположностью функции НИЖНЯЯ.
Например, если вы наберете ПРОПИСНЫЕ (hashmicro), ваш текст будет полностью заглавными буквами.
ПРАВИЛЬНЫЙ
Иногда вы забываете писать предложения строчными буквами, а не заглавными. Таким образом, с помощью формулы PROPER вы можете извлечь выгоду из первого символа в каждом слове, сохранив при этом остальные.
Формула СООТВЕТСТВУЮЩАЯ (текст).
Заключение
Хотя полное понимание Excel требует времени, вы будете знакомы со всеми правильными формулами и приложениями.
В компании использование Excel является подавляющим, особенно при представлении данных, статистики и финансов компании, таких как заработная плата. Цифровой мир обеспечивает корпоративную трансформацию для упрощения предоставления информации и расчета заработной платы с помощью автоматизированных систем. Вы можете использовать бухгалтерское программное обеспечение HashMicro в качестве инструмента для оптимального и точного расчета финансового оборота.
Кроме того, у HashMicro есть система, которая помогает со всеми вашими задачами управления персоналом и сотрудниками. С программным обеспечением HRM от HashMicro можно рассчитывать заработную плату, управлять списками отпусков и посещаемости, процессами возмещения расходов, расписаниями и другими операционными действиями за считанные секунды.
