Калькуляция образец в excel: Калькуляция себестоимости продукции с примером расчета в Excel

Расчёт себестоимости продукции на производстве — пример в Excel

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

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

Расчёт себестоимости единицы продукции в Excel

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

СС = ΣР / О, где

• СС — себестоимость;
• ΣР — сумма всех понесённых производителем расходов;
• О — общее количество выпущенной продукции в натуральных единицах (килограммах, метрах, литрах, штуках и так далее).

Себестоимость единицы готовой продукции может рассчитываться в Excel

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

Важно: состав учитываемых в калькуляции себестоимости продукции расходов следует определять с учётом характеристик производства. Общего списка статей не существует, как и при постановке целей по СМАРТ.

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

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

Упрощённый порядок расчёта себестоимости изделия:

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

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

• В третьем столбце («Транспорт») — расходы на транспортировку исходных материалов (также в рублях или другой местной валюте).

• В четвёртом столбце («Энергия») — затраты предприятия на обеспечение производственной линии электроэнергией (также в рублях).

• В пятом столбце («Брак») — средний процент бракованной продукции и отходов за один цикл производства (в процентах или долях).

• В шестом столбце («Зарплата») — суммарную заработную плату сотрудников, занятых в производстве.

• В седьмом столбце («Количество») — количество производимой продукции каждого вида (в килограммах, литрах, штуках и так далее).

• В восьмом столбце («Сумма») необходимо просуммировать ранее внесённые данные.

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

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

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

Рассчитать себестоимость на производстве — скачать шаблон и образец Excel

Скачать шаблон для расчёта себестоимости единицы продукции на производстве в виде документа Excel можно по ссылке выше.

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

Подводим итоги

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

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

Ответ на ваш вопрос, возможно, находится здесь

Калькуляционная карта образец в excel — JSFiddle

Editor layout

Classic Columns Bottom results Right results Tabs (columns) Tabs (rows)

Console

Console in the editor (beta)

Clear console on run

General

Line numbers

Wrap lines

Indent with tabs

Code hinting (autocomplete) (beta)

Indent size:

2 spaces3 spaces4 spaces

Key map:

DefaultSublime TextEMACS

Font size:

DefaultBigBiggerJabba

Behavior

Auto-run code

Only auto-run code that validates

Auto-save code (bumps the version)

Auto-close HTML tags

Auto-close brackets

Live code validation

Highlight matching tags

Boilerplates

Show boilerplates bar less often

Постоянные затраты предприятия.

Формула. Анализ. Расчет в Excel

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

Постоянные затраты. Определение

Постоянные затраты (англ. Fixed cost, FC, TFC или total  fixed cost, аналог постоянные расходы, постоянные издержки) –  это класс издержек предприятия, которые не связаны (не зависят) с объемом производства и продаж. В каждый момент времени они постоянны, независимо от характера деятельности. Постоянные издержки в совокупности с переменными, которые являются противоположностью постоянным, составляют общие издержки предприятия.

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Формула расчета постоянных затрат/издержек

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

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

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

Постоянные издержки = Затраты на З/П + Аренда помещений + Амортизация + Налоги на имущество + Реклама;

Переменные затраты = Затраты на сырье + Материалы + Электроэнергия + Топливо + Бонусная часть З/П;

Общие затраты = Постоянные затраты + Переменные затраты.

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

Пример расчета постоянных затрат на предприятии в Excel

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Покажем наглядно различия между постоянными и переменными издержками. Для этого в Excel заполним столбцы с «объемом производства», «постоянными затратами», «переменными затратами» и «общими затратами».

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

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

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Два метода расчета затрат на предприятии

При производстве продукции все затраты могу делиться на две группы по двум методам:

• постоянные и переменные затраты;
• косвенные и прямые затраты.

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

Постоянные затраты и точка безубыточности предприятия

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

• при каком критическом объеме производства и продажах предприятие будет конкурентоспособно и рентабельно;
• какой объем продаж необходимо сделать для того, чтобы создать зону финансовой безопасности предприятия;

Маржинальная прибыль (доход) в точке безубыточности совпадает с постоянными затратами предприятия. Отечественные экономисты чаще вместо маржинальной прибыли используют термин валовый доход. Чем больше маржинальная прибыль перекрывает постоянные затраты, тем выше рентабельность предприятия. Более подробно изучить точку безубыточности вы можете в статье “Точка безубыточности. Графики и пример расчета модели в Excel. Достоинства и недостатки“.

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Постоянные затраты в балансе предприятия

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

В общем случае к постоянным затратам можно отнести строки баланса:

• Себестоимость проданных товаров – 2120;
• Коммерческие расходы – 2210;
• Управленческие (общехозяйственные) – 2220.

На рисунке ниже представлен баланс ОАО «Сургутнефтехим», как мы видим, постоянные издержки изменяются каждый год. Модель постоянных затрат – это чисто экономическая модель, и она может быть использована в краткосрочном периоде, когда выручка и объем производства изменяются линейно и закономерно.

Возьмем другой пример – ОАО «АЛРОСА» и посмотрим на динамику изменения условно-постоянных затрат. На рисунке ниже показан характер изменения затрат с 2001 по 2010 год. Можно заметить, что затраты не были постоянны на протяжении 10 лет. Самые устойчивые затраты ­на протяжении всего периода  были коммерческие расходы. Остальные расходы так или иначе изменялись.

Резюме

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

Автор: к.э.н. Жданов Иван Юрьевич

Расчет стоимости услуги в Excel

Расчет стоимости услуги в Excel позволяет легко и быстро рассчитать стоимость услуги.

Данная возможность окажет неоценимую помощь для менеджеров.

 

Принимаем заказы на автоматизацию Ваших таблиц.

Примеры автоматизации в таблицах Excel Выпадающий список

 

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

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

 

Фото в примечании

 

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

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

 

Срок оплаты в Excel

 

Автоматическое изменение цвета ячеек и строк различными цветами позволяет визуально определить сроки оплаты поставщикам.

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

 

Автоматическое формирование номера телефона

 

Автоматическое формирование номера телефона в ячейке Excel облегчает ввод номера телефона в удобном для воспрития виде.

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

 

Автоматическое создание сводной таблицы в Excel

 

Автоматическое создание сводной таблицы с графиком в Excel выполняется одним нажатием на кнопку «Рассчитать».

Данные для сводной таблицы берутся из таблицы с первичными данными.

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

 

Расчет стоимости услуги в Excel

 

Расчет стоимости услуги в Excel позволяет легко и быстро рассчитать стоимость услуги.

Данная возможность окажет неоценимую помощь для менеджеров.

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

 

Расчёт сложных процентов в Excel

Уроки MS Excel

Работая с таблицами Excel, иногда возникает необходимость в распределении информации из одного столбца по

Уроки MS Excel

Тем людям, которые регулярно работают с таблицами Excel, нужно часто выполнять одни и те

Уроки MS Excel

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

Уроки MS Excel

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

Уроки MS Excel

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

Уроки MS Excel

Excel – одна из лучших программ для аналитика данных. А почти каждому человеку на

Уроки MS Excel

Время от времени при работе с электронными таблицами появляется необходимость изменить положение нескольких рядов

Уроки MS Excel

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

Уроки MS Excel

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

Уроки MS Excel

Определение процента от числа – довольно частая задача, с которой приходится сталкиваться пользователю Ecxel,

Уроки MS Excel

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

Уроки MS Excel

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

Уроки MS Excel

Стандартное обозначение строк в Excel – цифровое. Если же речь идет о столбцах, то

Уроки MS Excel

Набор функций у программы Excel, конечно, поистине огромный. В том числе, можно в определенной

Уроки MS Excel

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

Уроки MS Excel

Важно понимать, что Excel – это не только программа для создания баз данных, но

Расчет кредита в Excel

Кто как, а я считаю кредиты злом. Особенно потребительские. Кредиты для бизнеса — другое дело, а для обычных людей мышеловка»деньги за 15 минут, нужен только паспорт» срабатывает безотказно, предлагая удовольствие здесь и сейчас, а расплату за него когда-нибудь потом. И главная проблема, по-моему, даже не в грабительских процентах или в том, что это «потом» все равно когда-нибудь наступит. Кредит убивает мотивацию к росту. Зачем напрягаться, учиться, развиваться, искать дополнительные источники дохода, если можно тупо зайти в ближайший банк и там тебе за полчаса оформят кредит на кабальных условиях, попутно грамотно разведя на страхование и прочие допы?

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

Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т. д. «Помассажировать числа» заранее, как я это называю 🙂 Microsoft Excel может сильно помочь в этом вопросе.

Вариант 1. Простой кредитный калькулятор в Excel

Для быстрой прикидки кредитный калькулятор в Excel можно сделать за пару минут с помощью всего одной функции и пары простых формул. Для расчета ежемесячной выплаты по аннуитетному кредиту (т.е. кредиту, где выплаты производятся равными суммами — таких сейчас большинство) в Excel есть специальная функция ПЛТ (PMT) из категории Финансовые (Financial). Выделяем ячейку, где хотим получить результат, жмем на кнопку fx в строке формул, находим функцию ПЛТ в списке и жмем ОК. В следующем окне нужно будет ввести аргументы для расчета:

• Ставка — процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
• Кпер — количество периодов, т. е. срок кредита в месяцах.
• Пс — начальный баланс, т.е. сумма кредита.
• Бс — конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
• Тип — способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0. 

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

Вариант 2. Добавляем детализацию

Если хочется более детализированного расчета, то можно воспользоваться еще двумя полезными финансовыми функциями Excel — ОСПЛТ (PPMT) и ПРПЛТ (IPMT). Первая из них вычисляет ту часть очередного платежа, которая приходится на выплату самого кредита (тела кредита), а вторая может посчитать ту часть, которая придется на проценты банку. Добавим к нашему предыдущему примеру небольшую шапку таблицы с подробным расчетом и номера периодов (месяцев):

Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:

Добавился только параметр Период с номером текущего месяца (выплаты) и закрепление знаком $ некоторых ссылок, т.к. впоследствии мы эту формулу будем копировать вниз. Функция ПРПЛТ (IPMT) для вычисления процентной части вводится аналогично. Осталось скопировать введенные формулы вниз до последнего периода кредита и добавить столбцы с простыми формулами для вычисления общей суммы ежемесячных выплат (она постоянна и равна вычисленной выше в ячейке C7) и, ради интереса, оставшейся сумме долга:

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

=ЕСЛИ(A17>=$C$7;»»;A17+1)

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

=ЕСЛИ(A18<>»»; текущая формула; «»)

Т.е. если номер периода не пустой, то мы вычисляем сумму выплат с помощью наших формул с ПРПЛТ и ОСПЛТ. Если же номера нет, то выводим пустую текстовую строку:

Вариант 3. Досрочное погашение с уменьшением срока или выплаты

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

В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять — не достигли мы нулевого баланса раньше срока:

А в случае уменьшения выплаты — заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:

Вариант 4. Кредитный калькулятор с нерегулярными выплатами

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

Предполагается что:

• в зеленые ячейки пользователь вводит произвольные даты платежей и их суммы
  
• отрицательные суммы — наши выплаты банку, положительные — берем дополнительный кредит к уже имеющемуся
  
• подсчитать точное количество дней между двумя датами (и процентов, которые на них приходятся) лучше с помощью функции ДОЛЯГОДА (YEARFRAC)
  

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

О Фонде Деятельность Фонда Информация для работодателей (формы отчетности, бланки) Государственные услуги Фонда Электронный листок нетрудоспособности Специальные соцвыплаты Прямые выплаты Получение ТСР Обеспечение санаторно-курортным лечением Компенсации за самостоятельно приобретенные инвалидами технические средства реабилитации Компенсации за самостоятельно приобретенные лицами, пострадавшими на производстве, технические средства реабилитации Центры реабилитации Фонда Обращение в Фонд (Электронная приемная Фонда) Скачать программы Реестр аккредитованных организаций, оказывающих услуги в области охраны труда Противодействие коррупции Закупки Вакансии Портал открытых данных Проекты Фонда Евразийский ортопедический форум 2021 Портал Фонда Шлюз для приема расчетов по форме 4 ФСС РФ в электронном виде с использованием электронно-цифровой подписи Оценить качество госуслуг

Информация для работодателей (формы отчетности, бланки) Формы документов, рекомендованные для представления в территориальный орган Фонда о выделении необходимых средств на выплату страхового обеспечения Скачать (18. 8 кб) К списку документов

Отделения Фонда в республиках Отделения Фонда в краях и областях Отделения Фонда в городах федерального значения (Москва, Санкт-Петербург, Севастополь) Отделения Фонда в автономных округах и автономной области

Как использовать функцию Excel MMULT

При работе с формулами Excel бывают ситуации, когда вам нужен массив сумм, а не одна сумма. Такие функции, как SUM, не будут работать, потому что они агрегируют результаты и возвращают одно значение. MMULT …

Ядро этой формулы — ЕЧИСЛО и ПОИСК: ЕЧИСЛО (ПОИСК (ПЕРЕДАЧА (исключить); данные)) Здесь мы транспонируем элементы в именованном диапазоне «exclude», затем передаем результат в SEARCH как «найти текст» с «data» как…

Примечание: основная идея этой формулы заимствована из примера из прекрасной книги Майка Гирвина Control + Shift + Enter. В показанном примере используется несколько формул, которые описаны ниже. На высоком уровне MMULT …

В этом примере у нас есть таблица, содержащая 6 столбцов кодов, и каждая строка кодов принадлежит группе в столбце B. Цель состоит в том, чтобы найти любой код в C5: h25 и вернуть имя группы код принадлежит….

При работе с формулами Excel бывают ситуации, когда вам нужен массив сумм, а не одна сумма. Такие функции, как SUM, не будут работать, потому что они агрегируют результаты и возвращают одно значение. MMULT …

Логические критерии, используемые в этой формуле, работают изнутри наружу: — (данные = 90) где data — именованный диапазон B4: D12. Это генерирует результат ИСТИНА / ЛОЖЬ для каждого значения в данных и двойной отрицательный результат…

Логические критерии, используемые в этой формуле, работают изнутри наружу: (данные) <70 где data - именованный диапазон C5: I14. Это генерирует результат ИСТИНА / ЛОЖЬ для каждого значения в данных и двойной отрицательный результат ...

Логические критерии, используемые в этой формуле, работают изнутри наружу: — (имена = G4) где names — именованный диапазон C4: E7. Это генерирует результат ИСТИНА / ЛОЖЬ для каждого значения в данных и двойной отрицательный результат…

Как использовать функцию Excel ISERROR

Функция СУММПРОИЗВ принимает один или несколько массивов, умножает массивы вместе и возвращает «сумму продуктов» в качестве окончательного результата. Если указан только один массив, СУММПРОИЗВ просто возвращает сумму элементов в …

#NULL! ошибка довольно редко встречается в Excel и обычно является результатом опечатки, когда вместо запятой (,) или двоеточия (:) между двумя ссылками на ячейки используется пробел.Технически пробел — это «…

«.

# ЗНАЧЕНИЕ! ошибка появляется, когда значение не соответствует ожидаемому типу. Это может произойти, когда ячейки оставлены пустыми, когда функции, ожидающей число, дается текстовое значение и когда даты оцениваются как текст в Excel …

# ИМЯ? ошибка возникает, когда Excel что-то не может распознать. Часто #NAME? возникает, когда имя функции написано с ошибкой, но есть и другие причины, как описано ниже.Исправление #NAME? ошибка обычно просто …

О разливе и # РАЗЛИВ! ошибка С появлением в Excel динамических массивов формулы, возвращающие несколько значений, «переносят» эти значения непосредственно на рабочий лист. Прямоугольник, в котором заключены значения, равен …

.

В основе этой формулы мы строим список номеров строк для данного файла. Затем мы используем функцию MAX, чтобы получить наибольший номер строки, который соответствует последней ревизии (последнему появлению) этого файла.Найти …

В этом примере цель состоит в том, чтобы подсчитать количество ячеек в диапазоне, которые не содержат ошибок. Работая изнутри, мы сначала используем функцию ISERROR для всего диапазона: ISERROR (B5: B14) // проверяем все 10 …

С появлением в Excel динамических массивов в формулах больше внимания уделяется массивам. #CALC! ошибка возникает, когда формула сталкивается с ошибкой вычисления с массивом.#CALC! error — это «новая» ошибка в …

# ЧИСЛО! ошибка возникает в формулах Excel, когда невозможно выполнить расчет. Например, если вы попытаетесь вычислить квадратный корень из отрицательного числа, вы увидите # ЧИСЛО! ошибка. В приведенных ниже примерах показаны формулы …

Работая изнутри, функция ISERRROR возвращает TRUE, если значение является распознанной ошибкой, и FALSE, если нет. Когда задан диапазон ячеек (массив ячеек), функция ISERROR вернет массив ИСТИНА / ЛОЖЬ…

О # DIV / 0! ошибка # DIV / 0! ошибка появляется, когда формула пытается разделить на ноль или значение, эквивалентное нулю. Как и другие ошибки, ошибка # DIV / 0! полезен, потому что он сообщает вам, что чего-то не хватает или …

НАЙТИ возвращает позицию (в виде числа) первого вхождения пробела в текст. Эта позиция, минус один, передается в функцию LEFT как num_chars.Затем функция ВЛЕВО извлекает символы …

101 Функции Excel, которые вы должны знать

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

Функции даты и времени

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

СЕЙЧАС и СЕГОДНЯ

Текущую дату можно получить с помощью функции СЕГОДНЯ, а текущую дату и время — с помощью функции СЕЙЧАС. Технически функция NOW возвращает текущую дату и время, но вы можете форматировать только время, как показано ниже:

 
 СЕГОДНЯ () // возвращает текущую дату
NOW () // возвращает текущее время 

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

ДЕНЬ, МЕСЯЦ, ГОД и ДАТА

Вы можете использовать функции ДЕНЬ, МЕСЯЦ и ГОД, чтобы разобрать любую дату на ее необработанные компоненты, а функцию ДАТА — чтобы снова собрать все вместе.

 
 = DAY ("14-ноя-2018") // возвращает 14
= MONTH ("14-ноя-2018") // возвращает 11
= YEAR ("14-ноя-2018") // возвращает 2018
= DATE (2018,11,14) // возвращает 14 ноября 2018 г.  

ЧАС, МИНУТА, СЕКУНДА и ВРЕМЯ

Excel предоставляет набор параллельных функций для времени.Вы можете использовать функции ЧАС, МИНУТА и СЕКУНДА для извлечения отрезков времени, а также можете собрать ВРЕМЯ из отдельных компонентов с помощью функции ВРЕМЯ.

 
 = HOUR ("10:30") // возвращает 10
= МИНУТА ("10:30") // возвращает 30
= SECOND ("10:30") // возвращает 0
= TIME (10,30,0) // возвращает 10:30 

DATEDIF и YEARFRAC

Вы можете использовать функцию РАЗНДАТ, чтобы получить время между датами в годах, месяцах или днях. РАЗНДАТ также можно настроить для получения общего времени в «нормализованных» номиналах, т.е.е. «2 года и 6 месяцев и 27 дней».

Используйте YEARFRAC, чтобы получить дробные годы:

 
 = YEARFRAC ("14-ноя-2018", "10-июн-2021") // возвращает 2,57 

EDATE и EOMONTH

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

 
 EDATE (date, 6) // на 6 месяцев вперед
EOMONTH (date, 6) // на 6 месяцев вперед (конец месяца) 

РАБДЕНЬ и ЧИСТЫЙ ДНИ

Чтобы вычислить дату n рабочих дней в будущем, вы можете использовать функцию РАБДЕНЬ. Чтобы рассчитать количество рабочих дней между двумя датами, вы можете использовать ЧИСТРАБДНИ.

 
 РАБДЕНЬ (начало, n, праздники) // дата n рабочих дней в будущем 

Видео: как рассчитать сроки выполнения с помощью WORKDAY

 
 NETWORKDAYS (начало, конец, праздники) // количество рабочих дней между датами 

Примечание: Обе функции автоматически пропускают выходные (суббота и воскресенье), а также пропускают праздничные дни, если они предусмотрены. Если вам нужна большая гибкость в отношении того, какие дни считаются выходными, см. Функции РАБДЕНЬ.INTL и ЧИСТРАБДНИ.INTL.

WEEKDAY и WEEKNUM

Чтобы определить день недели по дате, Excel предоставляет функцию ДЕНЬ НЕДЕЛИ. WEEKDAY возвращает число от 1 до 7, которое означает воскресенье, понедельник, вторник и т. Д. Используйте функцию WEEKNUM, чтобы получить номер недели в данном году.

 
 = WEEKDAY (дата) // возвращает число 1-7
= WEEKNUM (date) // возвращает номер недели в году 

Инженерное дело

ПРЕОБРАЗОВАТЕЛЬ

Большинство инженерных функций носят довольно технический характер…вы найдете много функций для комплексных чисел в этом разделе. Однако функция ПРЕОБРАЗОВАНИЕ весьма полезна для повседневного преобразования единиц измерения. Вы можете использовать CONVERT для изменения единиц измерения расстояния, веса, температуры и многого другого.

 
 = CONVERT (72, «F», «C») // возвращает 22,2 

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

ISBLANK, ISERROR, ISNUMBER и ISFORMULA

Excel предоставляет множество функций для проверки значения в ячейке, включая ISNUMBER, ISTEXT, ISLOGICAL, ISBLANK, ISERROR и ISFORMULA. Эти функции иногда называют функциями IS, и все они возвращают ИСТИНА или ЛОЖЬ в зависимости от содержимого ячейки.

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

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

Логические функции

Логические функции

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

И, ИЛИ и НЕ

Ядром логических функций Excel являются функция И, функция ИЛИ и функция НЕ. На приведенном ниже экране каждая из этих функций используется для запуска простого теста значений в столбце B:

.

 
 = И (B5> 3, B5 <9)
= ИЛИ (B5 = 3, B5 = 9)
= НЕ (B5 = 2) 

ЕСЛИ ОШИБКА и ИФНА

Функции ЕСЛИОШИБКА и IFNA могут использоваться как простой способ перехвата и обработки ошибок. На приведенном ниже экране ВПР используется для получения стоимости из пункта меню.Столбец F содержит только функцию ВПР без обработки ошибок. В столбце G показано, как использовать IFNA с функцией ВПР для отображения настраиваемого сообщения при вводе нераспознанного элемента.

 
 = VLOOKUP (E5, menu, 2,0) // без отлова ошибок
= IFNA (VLOOKUP (E5, menu, 2,0), «Not found») // поймать ошибки 

В то время как IFNA перехватывает только ошибку # Н / Д, функция ЕСЛИОШИБКА перехватывает любую ошибку формулы.

Функции IF и IFS

Функция ЕСЛИ - одна из наиболее часто используемых функций в Excel.На приведенном ниже экране IF проверяет результаты теста и присваивает «прошел» или «не прошел»:

Несколько функций IF могут быть вложены вместе для выполнения более сложных логических тестов.

Новая функция в Excel 2019 и Excel 365: функция IFS может запускать несколько логических тестов без вложенных IF.

 
 = IFS (C5 <60, «F», C5 <70, «D», C5 <80, «C», C5 <90, «B», C5> = 90, «A») 

Функции поиска и ссылки

ВПР и ГПР

Excel предлагает ряд функций для поиска и извлечения данных. Самая известная из них - ВПР:

.

Подробнее: 23 факта, которые нужно знать о ВПР.

HLOOKUP работает как VLOOKUP, но ожидает, что данные расположены горизонтально:

ИНДЕКС и МАТЧ

Для более сложных поисков INDEX и MATCH предлагают большую гибкость и мощность:

И ИНДЕКС, и функция ПОИСКПОЗ являются мощными функциями, которые встречаются во всех видах формул.

Подробнее: как использовать INDEX и MATCH

ПРОСМОТР

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

СТРОКА И КОЛОННА

Вы можете использовать функции СТРОКА и СТОЛБЕЦ, чтобы найти номера строк и столбцов на листе. Обратите внимание на возвращаемые значения ROW и COLUMN для текущей ячейки , если ссылка не указана:

Функция строки также часто встречается в сложных формулах, обрабатывающих данные с относительными номерами строк.

СТРОКИ И КОЛОННЫ

Функции ROWS и COLUMNS обеспечивают подсчет строк в ссылке. На приведенном ниже экране мы подсчитываем строки и столбцы в таблице Excel с именем «Таблица1».

Примечание ROWS возвращает количество строк данных в таблице, исключая строку заголовка.Кстати, вот 23 вещи, которые нужно знать о таблицах Excel.

ГИПЕРССЫЛКА

Вы можете использовать функцию ГИПЕРССЫЛКА, чтобы создать ссылку с формулой. Примечание. ГИПЕРССЫЛКА позволяет создавать как внешние, так и внутренние ссылки:

GETPIVOTDATA

Функция GETPIVOTDATA полезна для получения информации из существующих сводных таблиц.

ВЫБЕРИТЕ

Функция ВЫБРАТЬ удобна всякий раз, когда вам нужно сделать выбор на основе числа:

 
 = ВЫБРАТЬ (2, «красный», «синий», «зеленый») // возвращает «синий» 

Видео: как использовать функцию ВЫБРАТЬ

ТРАНСПОРТ

Функция TRANSPOSE дает вам простой способ транспонировать вертикальные данные в горизонтальные и наоборот.

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

СМЕЩЕНИЕ

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

КОСВЕННЫЙ

Функция ДВССЫЛ позволяет создавать ссылки в виде текста.Поначалу эту концепцию немного сложно понять, но она может быть полезна во многих ситуациях. Ниже мы используем INDIRECT для получения значений из ячейки A1 на 5 разных листах. Каждая ссылка динамична. Если имя листа изменится, ссылка будет обновлена.

Функция ДВССЫЛ также используется для «блокировки» ссылок, чтобы они не изменялись при добавлении или удалении строк или столбцов. Дополнительные сведения см. В связанных примерах внизу страницы функции КОСВЕННО.

Внимание: как СМЕЩЕНИЕ, так и КОСВЕННО - непостоянные функции, они могут замедлить работу больших или сложных электронных таблиц.

СТАТИСТИЧЕСКИЕ функции

COUNTA и COUNTA

Вы можете подсчитывать числа с помощью функции СЧЁТ, а непустые ячейки с помощью функции СЧЁТ. Вы можете подсчитывать пустые ячейки с помощью COUNTBLANK, но на экране ниже мы подсчитываем пустые ячейки с помощью COUNTIF, что более полезно.

 
 = COUNT (B5: F5) // подсчитать числа
= COUNTA (B5: F5) // подсчитываем числа и текст
= СЧЁТЕСЛИ (B5: F5; "") // подсчитываем пробелы 

СЧЕТЕСЛИ и СЧЕТЕСЛИ

Для условного подсчета функция СЧЁТЕСЛИ может применять один критерий. Функция СЧЁТЕСЛИМН может применять несколько критериев одновременно:

 
 = СЧЁТЕСЛИ (C5: C12, "красный") // считать красный
= СЧЁТЕСЛИ (F5: F12, "> 50") // подсчитать общее> 50
= СЧЁТЕСЛИМН (C5: C12, «красный», D5: D12, «TX») // красный и tx
= СЧЁТЕСЛИМН (C5: C12, «синий», F5: F12, «> 50») // синий> 50 

Видео: как использовать функцию СЧЁТЕСЛИ

СУММ, СУММЕСЛИ, СУММЕСЛИМН

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

 
 = СУММ (F5: F12) // все
= СУММЕСЛИ (C5: C12, "красный", F5: F12) // только красный
= СУММЕСЛИ (F5: F12; "> 50") // больше 50
= СУММЕСЛИМН (F5: F12, C5: C12, «красный», D5: D12, «tx») // красный & tx
= СУММЕСЛИМН (F5: F12, C5: C12, «синий», F5: F12, «> 50») // синий &> 50 

Видео: как использовать функцию СУММЕСЛИ

СРЕДНИЙ, СРЕДНИЙ и СРЕДНИЙ

Следуя той же схеме, вы можете вычислить среднее значение с помощью СРЕДНИХ, СРЕДНИХ и СРЕДНИХМН.

МИН, МАКС, БОЛЬШОЙ, МАЛЕНЬКИЙ

Вы можете найти наибольшее и наименьшее значения с помощью MAX и MIN, а n-е наибольшее и наименьшее значения с помощью LARGE и SMALL.На приведенном ниже экране «данные» - это именованный диапазон C5: C13, используемый во всех формулах.

 
 = MAX (данные) // наибольший
= MIN (данные) // наименьший
= НАИБОЛЬШИЙ (данные, 1) // 1-й по величине
= НАИБОЛЬШИЙ (данные; 2) // 2-й по величине
= LARGE (data; 3) // 3-й по величине
= МАЛЕНЬКИЙ (данные, 1) // 1-й наименьший
= МАЛЕНЬКИЙ (данные, 2) // 2-й наименьший
= МАЛЕНЬКИЙ (данные; 3) // 3-й наименьший 

Видео: как найти n-е наименьшее или наибольшее значение

MINIFS, MAXIFS

MINIFS и MAXIFS. Эти функции позволяют находить минимальные и максимальные значения с условиями:

 
 = MAXIFS (D5: D15, C5: C15, "женский") // самый высокий женский
= MAXIFS (D5: D15, C5: C15, "male") // самый высокий мужской
= MINIFS (D5: D15, C5: C15, "женский") // нижний женский
= MINIFS (D5: D15, C5: C15, "мужской") // нижний мужской 

Примечание. MINIFS и MAXIFS - это новые функции в Excel через Office 365 и Excel 2019.

РЕЖИМ

Функция РЕЖИМ возвращает наиболее часто встречающееся число в диапазоне:

 
 = MODE (B5: G5) // возвращает 1 

РАНГ

Чтобы ранжировать значения от наибольшего к наименьшему или от наименьшего к наибольшему, используйте функцию РАНГ:

Видео: как ранжировать значения с помощью функции РАНГ

MATH Функции

АБС

Для изменения отрицательных значений на положительные используйте функцию ABS.

 
 = ABS (-134,50) // возвращает 134,50 

RAND и RANDBETWEEN

И функция RAND, и функция RANDBETWEEN могут генерировать случайные числа на лету. RAND создает длинные десятичные числа от нуля до 1. RANDBETWEEN генерирует случайные целые числа между двумя заданными числами.

КРУГЛЫЙ, КРУГЛЫЙ, КРУГЛЫЙ, ВНИЗ, INT

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

 
 = ОКРУГЛ (11,777,1) // возвращает 11,8
= ROUNDUP (11,777) // возвращает 11,8
= ROUNDDOWN (11.777,1) // возвращает 11,7
= INT (11.777) // возвращает 11 

ОКРУГ, ПОТОЛОК, ЭТАЖ

Чтобы округлить значения до ближайшего , кратного , используйте функцию ОКРУГЛ. Функции FLOOR и CEILING также округляются до заданного числа. FLOOR приводит к округлению в меньшую сторону, а CEILING - к увеличению.

 
 = ОКРУГЛ (13,85, 0,25) // возвращает 13,75
= CEILING (13,85; 0,25) // возвращает 14
= FLOOR (13,85, 0,25) // возвращает 13,75 

MOD

Функция MOD возвращает остаток после деления. Это звучит скучно и вызывающе, но MOD появляется во всевозможных формулах, особенно в формулах, которые должны делать что-то «каждый энный раз». На экране ниже вы можете увидеть, как MOD возвращает ноль каждое третье число, когда делитель равен 3:

.

СУММПРОИЗВ

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

ИТОГО

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

АГРЕГАТ

Как и SUBTOTAL, функция AGGREGATE также может выполнять ряд агрегатных операций с набором данных и при необходимости игнорировать скрытые строки. Основные отличия заключаются в том, что AGGREGATE может выполнять больше операций (всего 19), а также может игнорировать ошибки.

На приведенном ниже экране AGGREGATE используется для выполнения операций MIN, MAX, LARGE и SMALL при игнорировании ошибок. Обычно ошибка в ячейке B9 не позволяет этим функциям возвращать результат. На этой странице представлен полный список операций, которые может выполнять AGGREGATE.

 
 = AGGREGATE (4,6, значения) // MAX игнорирует ошибки, возвращает 100
= AGGREGATE (5,6, значения) // MIN игнорировать ошибки, возвращает 75 

ТЕКСТ Функции

СЛЕВА, СПРАВА, СРЕДНЕЙ

Для извлечения символов из левого, правого или среднего текста используйте функции LEFT, RIGHT и MID:

 
 = LEFT ("ABC-1234-RED", 3) // возвращает "ABC"
= MID ("ABC-1234-RED", 5,4) // возвращает "1234"
= RIGHT ("ABC-1234-RED", 3) // возвращает "RED" 

LEN

Функция LEN возвращает длину текстовой строки.LEN встречается во многих формулах, в которых подсчитываются слова или символы.

НАЙТИ, ПОИСК

Для поиска определенного текста в ячейке используйте функцию НАЙТИ или ПОИСК. Эти функции возвращают числовую позицию совпадающего текста, но ПОИСК допускает подстановочные знаки, а НАЙТИ чувствителен к регистру. Обе функции выдают ошибку, если текст не найден, поэтому вставьте функцию ISNUMBER, чтобы вернуть TRUE или FALSE (пример здесь).

 
 = НАЙТИ («Лучше черт тебя знает», «дьявол») // возвращает 12
= ПОИСК («Это не моя красивая жена», «bea *») // возвращает 12 

ЗАМЕНА, ЗАМЕНА

Чтобы заменить текст позицией, используйте функцию ЗАМЕНИТЬ.Чтобы заменить текст соответствием, используйте функцию ЗАМЕНА. В первом примере REPLACE удаляет две звездочки (**), заменяя первые два символа пустой строкой (""). Во втором примере SUBSTITUTE удаляет все символы решетки (#), заменяя "#" на "".

 
 = REPLACE ("** Red", 1,2, "") // возвращает "Red"
= SUBSTITUTE ("## Red ##", "#", "") // возвращает "красный" 

КОД

, СИМВОЛ

Чтобы узнать числовой код символа, используйте функцию КОД.Чтобы преобразовать числовой код обратно в символ, используйте функцию СИМВОЛ. В приведенном ниже примере CODE переводит каждый символ в столбце B в соответствующий ему код. В столбце F CHAR переводит код обратно в символ.

 
 = CODE ("a") // возвращает 97
= CHAR (97) // возвращает "a" 

Видео: как использовать функции КОДА и СИМВОЛА

ОТДЕЛКА, ЧИСТАЯ

Чтобы избавиться от лишнего места в тексте, используйте функцию ОБРЕЗАТЬ. Чтобы удалить разрывы строк и другие непечатаемые символы, используйте CLEAN.

 
 = TRIM (A1) // удалить лишнее пространство
= CLEAN (A1) // удалить разрывы строк 

Видео: Как очистить текст с помощью TRIM и CLEAN

CONCAT, TEXTJOIN, CONCATENATE

Новыми в Excel через Office 365 являются CONCAT и TEXTJOIN. Функция CONCAT позволяет объединять (объединять) несколько значений, включая диапазон значений без разделителя. Функция TEXTJOIN делает то же самое, но позволяет указать разделитель, а также может игнорировать пустые значения.

 
 = TEXTJOIN (",", TRUE, B4: h5) // возвращает "красный, синий, зеленый, розовый, черный"
= CONCAT (B7: H7) // возвращает «8675309» 

Excel также предоставляет функцию СЦЕПИТЬ, но не предлагает специальных функций. Я бы не стал заморачиваться с этим, а вместо этого прямо связал бы с символом амперсанда (&) в формуле.

ТОЧНЫЙ

Функция EXACT позволяет сравнивать две текстовые строки с учетом регистра.

ВЕРХНИЙ, НИЖНИЙ, ПРАВИЛЬНЫЙ

Чтобы изменить регистр текста, используйте функции ВЕРХНИЙ, НИЖНИЙ и ПРАВИЛЬНЫЙ.

 
 = UPPER ("Sue BROWN") // возвращает "SUE BROWN"
= LOWER ("Sue BROWN") // возвращает "sue brown"
= PROPER ("Sue BROWN") // возвращает "Sue Brown" 

Видео: Как изменить регистр в формулах

ТЕКСТ

И последнее, но не менее важное - это функция ТЕКСТ.Текстовая функция позволяет применять числовое форматирование к числам (включая даты, время и т. Д.) В виде текста. Это особенно полезно, когда вам нужно вставить отформатированный номер в сообщение, например «Распродажа заканчивается [дата]».

 
 = ТЕКСТ (B5; «$ #, ## 0,00»)
= ТЕКСТ (B6; «000000»)
= "Сохранить" & ТЕКСТ (B7, "0%")
= "Распродажа заканчивается" & ТЕКСТ (B8, "ммм d") 

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

Функции динамического массива

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

Функция	Назначение
ФИЛЬТР	Отфильтровать данные и вернуть совпадающие записи
RANDARRAY	Сгенерировать массив случайных чисел
ПОСЛЕДОВАТЕЛЬНОСТЬ	Сгенерировать массив последовательных чисел
СОРТ	Сортировать диапазон по столбцу
SORTBY	Сортировать диапазон по другому диапазону или массиву
УНИКАЛЬНЫЙ	Извлечь уникальные значения из списка или диапазона
XLOOKUP	Современная замена для ВПР
XMATCH	Современная замена функции ПОИСКПОЗ

Видео: Новые функции динамических массивов в Excel (около 3 минут).

Быстрая навигация

ABS, AGGREGATE, AND, AVERAGE, AVERAGEIF, AVERAGEIFS, CEILING, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COLUMNS, CONCAT, CONCATENATE, CONVERT, COUNT, COUNTA, COUNTBLANK, COUNTBLANK, COUNT, COUNTIFS, DATE, DATE, DATE, , EOMONTH, EXACT, FIND, FLOOR, GETPIVOTDATA, HLOOKUP, HOUR, HYPERLINK, IF, IFERROR, IFNA, IFS, INDEX, INDEX, INDIRECT, INT, ISBLANK, ISERROR, ISEVEN, ISFORMULA, ISLOGICAL, ISNUMBER, LEXTARGE, ISODD , LEN, LOOKUP, LOWER, MATCH, MAX, MAXIFS, MID, MIN, MINIFS, MINUTE, MOD, MODE, MONTH, MROUND, NETWORKDAYS, NOT, NOW, OFFSET, OR, PROPER, RAND, RANDBETWEEN, RANK, REPLACE, RIGHT , ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SECOND, SMALL, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, TEXT, TEXTJOIN, TIME, TODAY, WEEKUM, WEEKUM, WEEKUM, UP , YEAR, YEARFRAC

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

Вы можете создать простую формулу для сложения, вычитания, умножения или деления значений на рабочем листе.Простые формулы всегда начинаются со знака равенства ( = ), за которым следуют константы, представляющие собой числовые значения, и операторы вычисления, такие как плюс ( + ), минус (–), звездочка ( * ) или косая черта. (/) знаки.

Возьмем для примера простую формулу.

1. На листе щелкните ячейку, в которую вы хотите ввести формулу.

2. Введите = (знак равенства), а затем константы и операторы (до 8192 символов), которые вы хотите использовать в вычислениях.

   В нашем примере введите = 1 + 1 .

   Примечания:

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

   • Следуя стандартному порядку математических операций, умножение и деление выполняется перед сложением и вычитанием.

3. Нажмите Введите (Windows) или Верните (Mac).

Возьмем еще один вариант простой формулы. Введите = 5 + 2 * 3 в другой ячейке и нажмите . Введите или . Верните . Excel умножает последние два числа и добавляет к результату первое число.

Использовать автосумму

Вы можете использовать автосумму, чтобы быстро суммировать столбец, строку или числа.Выберите ячейку рядом с числами, которые вы хотите суммировать, щелкните AutoSum на вкладке Home , нажмите Введите (Windows) или Return (Mac), и все!

Когда вы нажимаете Автосумма , Excel автоматически вводит формулу (которая использует функцию СУММ) для суммирования чисел.

Примечание: Вы также можете ввести ALT + = (Windows) или ALT + + = (Mac) в ячейку, и Excel автоматически вставит функцию СУММ.

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

Нажмите Enter, чтобы отобразить результат (95,94) в ячейке B7. Вы также можете увидеть формулу в строке формул в верхней части окна Excel.

Примечания:

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

• Создав формулу, вы можете скопировать ее в другие ячейки вместо того, чтобы вводить ее снова и снова. Например, если вы скопируете формулу из ячейки B7 в ячейку C7, формула в C7 автоматически подстраивается под новое расположение и вычисляет числа в C3: C6.

• Вы также можете использовать автосумму для нескольких ячеек одновременно.Например, вы можете выделить обе ячейки B7 и C7, нажать Автосумма и просуммировать оба столбца одновременно.

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

Примечание. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите Введите (Windows) или Верните (Mac).

Данные
2
5
Формула	Описание	Результат
= A2 + A3	Суммирует значения в ячейках A1 и A2	= A2 + A3
= A2-A3	Вычитает значение в ячейке A2 из значения в A1	= A2-A3
= A2 / A3	Делит значение в ячейке A1 на значение в A2	= A2 / A3
= A2 * A3	Умножает значение в ячейке A1 на значение в A2. A3
Формула	Описание	Результат
= 5 + 2	Добавляет 5 и 2	= 5 + 2
= 5-2	Вычитает 2 из 5	= 5-2
= 5/2	Делит 5 на 2	= 5/2
= 5 * 2	5 умножает на 2	= 5 * 2
= 5 ^ 2	Возводит 5 в 2-ю степень	= 5 ^ 2

Нужна дополнительная помощь?

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

См. Также

Обзор формул в Excel

СУММ (функция СУММ)

Калькулятор размера выборки

с Excel

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

Калькулятор размера выборки с Excel

Введите размер популяции

Общее количество лиц, для которых требуется расчет размера выборки.

Введите размер выборки

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

Скачать шаблон калькулятора размера выборки для Excel
Формула размера выборки
n: минимальный требуемый размер выборки
t: уровень достоверности на уровне значимости x%
p: оценка доли людей, попадающих в интересующую вас группу
m: погрешность

Погрешность
Погрешность или доверительный интервал обычно указывается в результатах опроса общественного мнения.Это 95-процентный доверительный интервал, т.е. каждые 20 раз, когда вы повторяете этот опрос, статистика говорит, что один раз вы получите совершенно неожиданный ответ. Например: у опроса была погрешность плюс-минус три процентных пункта (3%), и 51% процентов вашей выборки выбрали ответ, вы можете быть «уверены», что если бы вы задали вопрос всей соответствующей совокупности между 48% (51–3) и 54% (51 + 3) выбрали бы этот ответ.

Похожие сообщения

Об авторе:

Дипаншу основал ListenData с простой целью - сделать аналитику простой для понимания и отслеживания.У него более 10 лет опыта работы в области науки о данных. За время своего пребывания в должности он работал с глобальными клиентами в различных областях, таких как банковское дело, страхование, частный капитал, телекоммуникации и человеческие ресурсы.

Основные формулы Excel - Список важных формул для начинающих

Руководство по базовым формулам Excel

Освоение основных формул Excel имеет решающее значение для новичков, чтобы стать высокопрофессиональными в финансовом анализе Описание работы финансового аналитика Описание работы финансового аналитика, приведенное ниже, дает типичный пример всех навыки, образование и опыт, необходимые для работы аналитиком в банке, учреждении или корпорации.Выполняйте финансовое прогнозирование, отчетность и отслеживание операционных показателей, анализируйте финансовые данные, создавайте финансовые модели. Microsoft Excel Ресурсы ExcelИзучайте Excel онлайн с помощью 100 бесплатных руководств, ресурсов, руководств и шпаргалок по Excel! Ресурсы CFI - лучший способ изучить Excel на своих условиях. считается отраслевым стандартом программного обеспечения для анализа данных. Программа электронных таблиц Microsoft также является одним из наиболее предпочтительных программ для инвестиционных банкиров. Описание вакансии в инвестиционном банке. В этом описании вакансии в инвестиционном банке излагаются основные навыки, образование и опыт работы, необходимые для того, чтобы стать аналитиком или партнером IB и финансовыми аналитиками в области обработки данных и финансового моделирования. Финансовое моделированиеФинансовое моделирование выполняется в Excel для прогнозирования финансовых показателей компании.Обзор того, что такое финансовое моделирование, как и зачем создавать модель, и презентация. Это руководство предоставит обзор и список основных функций Excel.

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

Основные термины в Excel

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

1. Формулы

В Excel формула - это выражение, которое работает со значениями в диапазоне ячеек или ячейке. Например, = A1 + A2 + A3, которое находит сумму диапазона значений от ячейки A1 до ячейки A3.

2. Функции

Функции - это предварительно определенные формулы в Excel.Они исключают трудоемкий ручной ввод формул, давая им понятные для человека имена. Например: = СУММ (A1: A3). Функция суммирует все значения от A1 до A3.

Пять экономящих время способов вставки данных в Excel

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

1. Простая вставка: ввод формулы внутри ячейки

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

Excel довольно умен тем, что когда вы начинаете вводить имя функции, появляется всплывающая подсказка функции. Из этого списка вы выберете свои предпочтения. Однако не нажимайте клавишу Enter.Вместо этого нажмите клавишу TAB, чтобы продолжить вставку других параметров. В противном случае вы можете столкнуться с ошибкой неверного имени, часто как «#NAME?». Чтобы исправить это, просто повторно выберите ячейку и перейдите к строке формул, чтобы выполнить свою функцию.

Изображение: бесплатный ускоренный курс CFI по Excel.

2. Использование опции «Вставить функцию» на вкладке «Формулы»

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

3. Выбор формулы из одной из групп на вкладке «Формула»

Эта опция предназначена для тех, кто хочет быстро вникнуть в свои любимые функции. Чтобы найти это меню, перейдите на вкладку «Формулы» и выберите нужную группу. Щелкните, чтобы отобразить подменю со списком функций. Оттуда вы можете выбрать свои предпочтения. Однако, если вы обнаружите, что предпочитаемой группы нет на вкладке, выберите параметр «Дополнительные функции» - вероятно, она там просто скрыта.

Изображение: CFI's Excel Courses.

4. Использование опции автосуммирования

Для быстрых и повседневных задач функция автосуммы Автосумма Формула автосуммы Excel - это ярлык, который может сэкономить время при финансовом моделировании в Excel. Введите «ALT =», чтобы быстро суммировать все числа в серии данных. Это позволяет вам легко складывать серии чисел по вертикали или горизонтали без использования мыши или даже клавиш со стрелками.Итак, перейдите на вкладку Home в дальнем правом углу и нажмите кнопку AutoSum. Затем щелкните курсор, чтобы отобразить другие скрытые формулы. Этот параметр также доступен в первом варианте вкладки «Формулы» после параметра «Вставить функцию».

5. Быстрая вставка: используйте недавно использованные вкладки

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

Бесплатное руководство по формулам Excel на YouTube

Просмотрите БЕСПЛАТНОЕ видео на YouTube от CFI, чтобы быстро изучить наиболее важные формулы Excel. Посмотрев демонстрационный видеоролик, вы быстро узнаете самые важные формулы и функции.

Семь базовых формул Excel для вашего рабочего процесса

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

1. СУММ

Функция СУММ Функция СУММ Функция СУММ относится к математическим функциям и функциям тригонометрии. Функция суммирует ячейки, которые представлены как несколько аргументов. Это самая популярная и широко используемая функция в Excel. SUM помогает пользователям выполнять быстрое суммирование указанных ячеек в MS Excel. Например, нам дается стоимость 100 - это первая формула, которую необходимо знать в Excel. Обычно он объединяет значения из набора столбцов или строк из выбранного диапазона.

= СУММ ( число1 , [число2],…)

Пример:

= СУММ (B2: G2) - простой выбор, который суммирует значения строки.

= СУММ (A2: A8) - простой выбор, который суммирует значения столбца.

= СУММ (A2: A7, A9, A12: A15) - сложная коллекция, которая суммирует значения от диапазона A2 до A7, пропускает A8, добавляет A9, перескакивает с A10 и A11, а затем, наконец, добавляет от A12 к A15.

= СУММ (A2: A8) / 20 - показывает, что вы также можете превратить свою функцию в формулу.

Изображение: бесплатный ускоренный курс CFI по Excel.

2. СРЕДНЕЕ

Функция СРЕДНЕЕ Функция СРЕДНЕЕ Расчет среднего значения в Excel. Функция СРЕДНИЙ относится к Статистическим функциям. Он вернет среднее значение аргументов. Он используется для вычисления среднего арифметического заданного набора аргументов. Финансовому аналитику эта функция полезна для определения среднего числа. должен напоминать вам о простых средних данных, таких как среднее количество акционеров в данном пуле акционеров.

= СРЕДНЕЕ ( число1 , [число2],…)

Пример:

= СРЕДНЕЕ (B2: B11) - показывает простое среднее, также похоже на (СУММ (B2: B11) / 10)

3. COUNT

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

= COUNT ( значение1, [значение2],… )

Пример:

COUNT (A : A) - Подсчитывает все числовые значения в столбце A. Однако для подсчета строк необходимо настроить диапазон внутри формулы.

COUNT (A1: C1) - Теперь он может подсчитывать строки.

Изображение: CFI's Excel Courses.

4. COUNTA

Как и функция COUNT, функция COUNTACOUNTA Функция COUNTA вычисляет количество непустых ячеек в заданном наборе значений. Функцию = counta () также часто называют формулой Excel Countif Not Blank. Как финансовый аналитик, функция полезна для подсчета ячеек, которые не являются пустыми или пустыми в заданном диапазоне. считает все клетки в данной ярости.Однако он считает все ячейки независимо от типа. То есть, в отличие от COUNT, которое считает только числа, он также считает даты, время, строки, логические значения, ошибки, пустую строку или текст.

= COUNTA ( значение1, [значение2],… )

Пример:

COUNTA (C2: C13) - считает строки со 2 по 13 в столбце C независимо от типа. Однако, как и COUNT, вы не можете использовать ту же формулу для подсчета строк. Вы должны скорректировать выбор внутри скобок - например, COUNTA (C2: h3) будет считать столбцы от C до H

5.IF

Функция IF Функция IF Функция Excel IF Statement проверяет заданное условие и возвращает одно значение для ИСТИННОГО результата, а другое - для ЛОЖНОГО результата. Например, если общий объем продаж превышает 5000 долларов, тогда в качестве бонуса верните «Да», иначе верните «Нет». Мы также можем создавать вложенные операторы IF, которые часто используются, когда вы хотите отсортировать данные в соответствии с заданной логикой. Лучшая часть формулы ЕСЛИ заключается в том, что вы можете встраивать в нее формулы и работать с ними.

= IF ( logic_test, [value_if_true], [value_if_false] )

Пример:

= IF (C2 at - Проверяет, если значение C3 меньше значения в D3.Если логика верна, пусть значение ячейки будет ИСТИНА, иначе ЛОЖЬ

= ЕСЛИ (СУММ (C1: C10)> СУММ (D1: D10), СУММ (C1: C10), СУММ (D1: D10) )) - Пример сложной логики IF. Сначала он суммирует C1 до C10 и D1 до D10 , затем сравнивает сумму. Если сумма от C1 до C10 больше суммы от D1 до D10 , тогда значение ячейки становится равным сумме от C1 до C10 . В противном случае это будет СУММА от от C1 до C10 .

6. ОБРЕЗАТЬ

Функция ОБРЕЗАТЬ Функция ОБРЕЗАТЬ Функция ОБРЕЗАТЬ относится к текстовым функциям Excel. TRIM помогает удалить лишние пробелы в данных и, таким образом, очистить ячейки на листе. В финансовом анализе функция TRIM может быть полезна для удаления нерегулярных, чтобы убедиться, что ваши функции не возвращают ошибки из-за неуправляемых пробелов. Это гарантирует, что все пустые места удалены. В отличие от других функций, которые могут работать с диапазоном ячеек, TRIM работает только с одной ячейкой.Следовательно, это имеет обратную сторону - добавление дублированных данных в вашу электронную таблицу.

= ОБРЕЗАТЬ ( текст )

Пример:

ОБРЕЗАТЬ (A2) - Удаляет пустые места в значении в ячейке A2.

Изображение: бесплатный ускоренный курс CFI по Excel.

7. MAX и MIN

Функция MAXMAX Функция MAX относится к статистическим функциям Excel. MAX вернет наибольшее значение в заданном списке аргументов.Из заданного набора числовых значений он вернет наибольшее значение. В отличие от функции MAXA, функция MAX будет подсчитывать числа, но игнорировать пустые ячейки и функцию MINMIN Функция MIN относится к категории статистических функций Excel. MIN вернет минимальное значение в заданном списке аргументов. Из заданного набора числовых значений он вернет наименьшее значение. В отличие от функции MINA, функции помогают найти максимальное и минимальное количество в диапазоне значений.

= МИН ( число1 , [число2],…)

Пример:

= МИН (B2: C11) - Находит минимальное число между столбцом B от B2 и столбцом C от C2 до строка 11 в обоих столбцах B и C.

= МАКС ( число1 , [число2],…)

Пример:

= МАКС (B2: C11) - аналогично, он находит максимальное число между столбцом B из B2 и столбец C от C2 до строки 11 в столбцах B и C.

Дополнительные ресурсы

Спасибо за то, что прочитали руководство CFI по основным формулам Excel. Чтобы продолжить свое развитие в качестве финансового аналитика мирового уровня Станьте сертифицированным аналитиком финансового моделирования и оценки (FMVA) ®, эти дополнительные ресурсы CFI будут полезны:

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

  формул Excel с примерами

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

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

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

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

  В MS Excel формулы - это уравнения, которые выполняют различные вычисления на ваших листах. Хотя за прошедшие годы Microsoft представила несколько новых функций, концепция формул электронной таблицы Excel одинакова во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007 и ниже.

  • Все формулы Excel начинаются со знака равенства (=).
  • После символа равенства введите либо вычисление , либо функцию . Например, чтобы сложить значения в ячейках с B1 по B5, вы можете:
    • Введите уравнение целиком: = B1 + B2 + B3 + B4 + B5
    • Используйте функцию СУММ: = СУММ (B1: B5)
  • Нажмите клавишу Enter, чтобы заполнить формулу. Сделанный!

  Элементы формул Microsoft Excel

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

  • Константы - числа или текстовые значения, которые вы вводите непосредственно в формулу, например = 2 * 3.
  • Ссылки на ячейки - ссылка на ячейку, содержащую значение, которое вы хотите использовать в формуле Excel, например = СУММ (A1, A2, B5) .

    Чтобы ссылаться на данные в двух или более смежных ячейках, используйте ссылку на диапазон , например A1: A5. Например, чтобы суммировать значения во всех ячейках от A1 до A5 включительно, используйте следующую формулу:
    = СУММ (A1: A5) .

  • Имена - определенное имя для диапазона ячеек, константы, таблицы или функции, например = SUM (my_name) .
  • Функции - предопределенные формулы в Excel, которые выполняют вычисления с использованием значений, указанных в их аргументах.
  • Операторы - специальные символы, указывающие тип операции или вычисления, которые необходимо выполнить.

  Операторы в формулах листа Excel

  Чтобы сообщить Microsoft Excel, какой тип операции вы хотите выполнить в формуле, вы используете специальные символы, которые технически называются операторами .В Excel существует 4 типа операторов:

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

  Эти операторы используются для выполнения основных математических операций, таких как сложение, вычитание, умножение и деление. 3
  (увеличивает число в A2 до степени 3)

  Например, если у вас есть цена товара в ячейке A2 и НДС в ячейке B2, вы можете рассчитать сумму НДС, используя следующую процентную формулу: = A2 * B2
  

  Операторы сравнения в формулах Excel

  В формулах Microsoft Excel сравнения или логические операторы , используются для сравнения двух значений.Результатом сравнения всегда является логическое значение ИСТИНА или ЛОЖЬ. В Excel доступны следующие логические операторы:

  Оператор сравнения	Значение	Пример формулы
  =	равно	= A2 = B2
  <>	Не равно	= A2 <> B2
  >	Больше	= A2> B2
  <	Менее	= A2
  > =	Больше или равно	= A2> = B2
  <=	Меньше или равно	= A2 <= B2

  Например, формула = A1 = B1 возвращает ИСТИНА, если ячейки A1 и B1 содержат одно и то же значение (число, текст или дату), в противном случае - ЛОЖЬ.

  Дополнительные сведения и примеры использования операторов сравнения в формулах MS Excel см. В следующем руководстве: Логические операторы Excel - равно, не равно, больше, меньше.

  Оператор конкатенации текста

  Оператором конкатенации текста в Excel является символ амперсанда ( и ). Вы можете использовать его для объединения двух или более текстовых строк в одну строку.

  Например, если у вас есть коды стран в столбце A и номера телефонов в столбце B, вы можете использовать следующую формулу, чтобы получить номера телефонов в сочетании с кодами стран:

  = A1 & "" & B1

  В приведенной выше формуле мы объединяем пробел между ними, чтобы числа лучше читались:
  

  Тот же результат может быть достигнут с помощью функции СЦЕПИТЬ, а в следующем руководстве объясняются все детали: Как объединить текстовые строки, ячейки и столбцы в Excel.

  Справочные операторы в формулах и функциях Excel

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

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

  Например, диапазон A1: A00 включает 100 ячеек от A1 до A100. Чтобы найти среднее значение этих 100 ячеек, используйте следующую формулу: = СРЕДНЕЕ (A1: A00)

  Вы также можете ссылаться на весь столбец (A: A) или на всю строку (1: 1).Например, следующая формула находит сумму всех чисел в столбце A: = СУММ (A: A) . Узнайте больше о ссылках на весь столбец и целую строку.

  Запятая (, ) - используется для разделения аргументов в формулах электронных таблиц Excel. Например, формула = ЕСЛИ (A1> 0, «хорошо», «плохо») читается следующим образом: если A1 больше нуля, вернуть «хорошо», в противном случае - «плохо».

  Примечание. Запятая - это разделитель списка по умолчанию в Северной Америке и некоторых других странах.В европейских странах запятая зарезервирована как десятичный символ , а разделитель списка обычно устанавливается на точку с запятой (;). В этом случае вам необходимо разделить аргументы функции точкой с запятой, например = ЕСЛИ (A1> 0; «хорошо»; «плохо») .

  Итак, если вы пытаетесь создать формулу на своем листе, но Excel не принимает ее и выдает ошибку «неверная формула», перейдите в свои региональные настройки ( Панель управления> Регион и язык > Дополнительно Settings) и проверьте, какой символ установлен как List Separator там.Это тот символ, который вам нужно использовать для разделения аргументов в формулах Excel.

  Пробел - это оператор пересечения, который позволяет получить ячейки, общие для двух указанных вами ссылок. Например, если у вас есть список элементов в столбце A и некоторые связанные данные в других столбцах, вы можете получить значение на пересечении
  данного столбца и строки, используя следующую формулу: = B3: D3 C2: C4
  

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

  Типы формул Excel

  Формулы, которые вы создаете в таблицах Excel, могут быть простыми или сложными:

  • Простые формулы Excel выполняют всего одну математическую операцию, например = 10 * 5 или = СУММ (A1: A10)
  • Сложные (расширенные) формулы Excel включают несколько вычислений, например = 10 * 5 + 20 или = СУММ (A1: A10) / 2

  Далее в этом руководстве вы найдете подробные инструкции по созданию обоих типов формул электронной таблицы Excel.

  Как создавать формулы в Excel

  Как уже упоминалось, любая формула Excel начинается со знака равенства (=). Итак, какую бы формулу вы ни собирались написать, начните с ввода = либо в ячейке назначения, либо в строке формул Excel. А теперь давайте подробнее рассмотрим, как вы можете составлять разные формулы в Excel.

  Как создавать простые формулы в Excel

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

  Как создавать расширенные формулы в Excel

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

  Создание сложных формул с константами и математическими операторами

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

  .
  • Математические операции в скобках
  • Степень (экспоненциальные вычисления)
  • Умножение и деление, в зависимости от того, что идет первым в формуле
  • Сложение и вычитание, в зависимости от того, что в формуле наступит раньше

  Например, вы можете использовать следующие формулы для расчета суммы и комиссии:
  

  А теперь давайте разберем эти формулы, чтобы увидеть, как Microsoft Excel их вычисляет:

  Итоговая формула: = $ B2 * $ D2 + $ B2 * $ D2 * $ C2

  • 1-е умножение: $ B2 * $ D2 (цена * кол-во.= сумма)
  • 2 ^nd и 3 ^rd умножения: $ B2 * $ D2 * $ C2 (цена * кол-во *% НДС = сумма НДС)
  • Дополнение: сумма + сумма НДС = итого

  Формула комиссии: = ($ B2 * $ D2 + $ B2 * $ D2 * $ C2) * 10%

  Чтобы рассчитать комиссию 10%, вам нужно умножить сумму на 10%, чтобы вы заключили предыдущий расчет в скобки и получили желаемый результат.

  Конечно, ничто не мешает вам умножить сумму, уже рассчитанную в столбце E, на 10%, в этом случае формула сведется к простому вычислению = E2 * 10% .Однако на больших листах имеет смысл писать независимо вычисляемые формулы, чтобы удаление столбца с одной формулой не нарушило работу других.

  Формулы Excel с вложенными функциями

  В формулах Microsoft Excel вложенность одной функции в другую означает использование одной функции в качестве аргумента другой функции. В современных версиях Excel 2016, 2013, 2010 и 2010 можно использовать до 64 вложенных функций. В более старых версиях Excel 2003 и ниже разрешены только до 7 уровней функций.

  Вот очень простой пример вложенной формулы Excel, которая включает функцию СУММ для нахождения итога и функцию ОКРУГЛ для округления этого числа до ближайшего целого (0 знаков после запятой):

  = ОКРУГЛ (СУММ (B2: B6), 0)
  

  Из всех функций Excel функция IF является вложенной чаще, чем все остальные. Как вы, вероятно, знаете, функция ЕСЛИ используется для оценки указанного условия и возврата одного значения, когда условие выполняется, и другого значения, когда условие не выполняется.Однако, часто цитируя, вам приходится иметь дело с ситуациями, когда существует более двух возможных исходов. И в этом случае вы можете написать несколько функций ЕСЛИ и вложить их друг в друга:
  

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

  Формулы массива в Excel

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

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

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

  Эта задача требует сравнения двух диапазонов, и это можно сделать, используя следующую формулу массива:

  = СУММ ((B2: B10> = A2: A10) * (B2: B10> 0))
  

  Примечание. Чтобы правильно ввести формулу массива Excel, вы должны нажать Ctrl + Shift + Enter вместо обычного ввода Enter.

  Чтобы узнать больше о формулах массива Excel, см. Следующие руководства:

  Пользовательские функции Excel

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

  Такие пользовательские функции называются Пользовательскими функциями (UDF), и они особенно полезны для сложных математических или инженерных расчетов. Как и макросы, пользовательские функции написаны на языке VBA (Visual Basic для приложений). Например, вы можете просмотреть и загрузить пользовательские функции, созданные нашей командой, для подсчета и суммирования ячеек по цвету.

  Абсолютные, относительные и смешанные ссылки на ячейки в формулах Excel

  В Excel существует 3 типа ссылок на ячейки: абсолютные ($ A $ 1), относительные (A1) и смешанные ($ A1 или A $ 1).Все три приведенные выше ссылки относятся к одной и той же ячейке, а знак доллара ($) используется только для одной цели - он сообщает Microsoft Excel, следует ли изменять или не изменять ссылки на ячейки, когда формула перемещается или копируется в другие ячейки.

  Абсолютная ссылка на ячейку ($ A $ 1) - знак $ перед координатами строки и столбца делает ссылку статической и позволяет копировать формулу без изменения ссылок .

  Относительная ссылка на ячейку (A1) - ссылка на ячейку без изменения знака $ в зависимости от относительного положения строк и столбцов в электронной таблице.

  Ссылка на смешанную ячейку - может быть 2-х типов:

  • Абсолютный столбец и относительная строка ($ A1) - знак $ перед буквой столбца блокирует ссылку на указанный столбец, поэтому столбец никогда не изменяется. Относительная ссылка на строку без знака доллара изменяется в зависимости от строки, в которую копируется формула.
  • Относительный столбец и абсолютная строка (A $ 1) - ссылка на строку, заблокированная с помощью $, не изменяется, а ссылка на столбец изменяется.

  На следующем изображении показано, как на практике работают разные типы ссылок.
  

  Дополнительные сведения о ссылках на ячейки Excel и другие примеры формул см. В разделе Зачем использовать $ в формулах Excel - абсолютные и относительные ссылки на ячейки.

  Советы и быстрые клавиши для работы с формулами Excel

  Формулы

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

  • Для переключения между абсолютными, относительными и смешанными ссылками в формуле используйте клавишу F4, как показано в разделе «Переключение между типами ссылок в Excel».
  • Чтобы просмотреть все формулы на листе, нажмите кнопку Показать формулы на вкладке Формулы > Аудит формул или нажмите сочетание клавиш Ctrl + ~.
  • Чтобы отредактировать формулу , нажмите F2, или дважды щелкните ячейку, или щелкните строку формул.
  • Чтобы отладить формулы в Excel, выберите часть формулы и нажмите F9. Это позволит вам увидеть фактические значения за ссылками на ячейки.
  • Кому скопируйте формулу в все ячейки в столбце , введите формулу в первую ячейку, выберите эту ячейку и наведите курсор на маленький квадрат в правом нижнем углу, пока он не изменится на черный крест (который называется
    дескриптором заполнения). Дважды щелкните этот крестик, и формула будет скопирована через весь столбец.
  • Чтобы преобразовать формулы в значения , выберите все ячейки с формулами, которые вы хотите преобразовать, нажмите Ctrl + C, чтобы скопировать эти формулы, затем нажмите Shift + F10, затем нажмите V, а затем нажмите Enter. Shift + F10 + V - это ярлык для специальной вставки в Excel - значения только . Если вы не уверены, что запомните этот ярлык, просто нажмите обычный ярлык для вставки Ctrl + V, щелкните маленькую стрелку справа от кнопки Вставить , чтобы открыть раскрывающийся список, и выберите Вставить значения .Дополнительные сведения см. В разделе «Как заменить формулы на их значения в Excel».

  Формулы Microsoft Excel с примерами

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

  Процентная формула Excel

  Формулы сумм в Excel

  Формула подсчета в Excel

  Формула среднего в Excel

  Формулы даты в Excel

  • Как преобразовать текст на дату в Excel - несколько формул для преобразования текста на дату.
  • Как преобразовать дату Excel в текст - формулу для покрытия даты в текстовой строке в указанном формате.
  • Примеры формул Excel DATE - как получить серийный номер, представляющий дату, сложить и вычесть даты в Excel, вернуть дату на основе значений в других ячейках, преобразовать текстовую строку в дату, а также несколько расширенных примеров формул Excel DATE.
  • Формулы для вычисления дней в Excel (функции WEEKDAY, DAY) - формулы даты для возврата дня недели от даты, получения количества дней в году, определения количества дней между двумя датами и т. Д.
  • Как рассчитать месяцы в Excel (функции МЕСЯЦ и МЕСЯЦ) - примеры формулы даты Excel для извлечения месяца из даты, получения первого и последнего дня месяца, преобразования названия месяца в число и т. Д.
  • Расчет номера недели в Excel (функция WEEKNUM) - как использовать формулы даты в Excel для получения номера недели от даты, преобразования номера недели в дату, получения номера недели в месяце, суммирования значений по номеру недели и т. Д.
  • Как складывать и вычитать даты в Excel - формулы даты для сложения и вычитания дней, недель, месяцев и лет.
  • Как рассчитать разницу между двумя датами (функция РАЗНДАТ) - формула даты Excel для вычисления разницы между двумя датами в днях, неделях, месяцах или годах.
  • Расчет рабочих дней в Excel (РАБДЕНЬ и ЧИСТРАБДНИ) - использование формулы даты в Excel для расчета рабочих дней с настраиваемыми параметрами выходных и праздничных дней.
  • Преобразование даты в год и вычисление возраста по дате рождения - формулы возраста Excel и несколько других формул даты для извлечения года из даты, преобразования даты в месяц и год, определения високосных лет.

  Формулы времени в Excel

  Формулы Excel для ВПР

  Формулы условного форматирования Excel

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