Функции Excel 2016: 10 самых принципиальных формул
Полное количество функций для работы с электрическими таблицами величавое огромное количество. Но посреди их есть более полезные для ежедневного использования. Мы составили 10 самых принципиальных формул Excel 2016 на любой денек.
Объединение текстовых значений
Для объединения ячеек с текстовым значением можно употреблять различные формулы, но они имеют свои аспекты. К примеру, команда =СЦЕПИТЬ(D4;E4) удачно соединит воединыжды две ячейки, равно как и наиболее обычная функция =D4&E4, но никакого разделителя меж словами добавлено не будет – они отобразятся слитно.
Избежать данного недостатка можно добавляя пробелы, или в конце текста каждой ячейки, что навряд ли можно именовать хорошим решением, или конкретно в самой формуле, куда в хоть какое пространство можно вставить набор знаков в кавычках, в том числе и пробел. В нашем случае формула =СЦЕПИТЬ(D4;E4) получит вид =СЦЕПИТЬ(D4;» «;E4). Вообщем, если вы объединяете огромное количество текстовых ячеек, то аналогичным образом пробел вручную придется прописывать опосля адреса каждой ячейки.
Раскрывающиеся списки Excel
Раскрывающиеся списки Excel
Добавление раскрывающегося перечня в вашу Excel-таблицу может существенно повысить удобство работы, а означает и эффективность.
Подробнее
Иной типовой формулой для склеивания ячеек с текстом является команда ОБЪЕДИНИТЬ. По собственному синтаксису она по дефлоту содержит два доп параметра – поначалу идет определенный знак разделения, потом команда ИСТИНА либо ЛОЖЬ (в первом случае пустые ячейки из обозначенного интервала будут игнорироваться, во 2-м – нет), и позже уже перечень либо интервал ячеек. Меж ячейками также можно употреблять и обыденные текстовые значения в кавычках. К примеру, формула =ОБЪЕДИНИТЬ(» «;ИСТИНА;D4:F4) склеит три ячейки, пропустив пустые, если таковые имеется, и добавит меж словами по пробелу.
Применение: Данная функция нередко употребляется для склеивания ФИО, когда отдельные составные части находятся в различных колонках и есть общая сводная колонка с полным именованием человека.
Выполнение условия ИЛИ
Обычный оператор ИЛИ описывает выполнение данного в скобках условия и на выходе возвращает одно из значений ИСТИНА либо ЛОЖЬ. В предстоящем данная формула может употребляться в качестве составного элемента наиболее сложных критерий, когда зависимо от того, что выдаст значение ИЛИ будет производиться то либо другое действие.
При всем этом сравниваться могут как численные характеристики, применяя знаки >, <, =, так и поиск определенного значения для ячейки, которое быть может текстовым. А именно, для поиска слова «Сдал» в определенных ячейках будет употребляться формула =ИЛИ(D4= «Сдал»; E4= «Сдал»; F4= «Сдал»)
Применение: Одним из вариантов внедрения данной функции быть может учет удачливости прохождения тесты из 3-х попыток, где довольно одной удачной сдачи для предстоящего обучения/роли.
Поиск и внедрение значения
По горизонтали
Используя функцию ГПР мы можем задать поиск по определенной строке таблицы, а на выходе получить значение из иной ячейки такого же столбца (на одну либо несколько строк ниже), что соответствует данному условию. При этом поиск задается или на четкое значение (употребляется оператор ЛОЖЬ), или на ориентировочное (с оператором ИСТИНА), что дозволяет употреблять интервалы. Синтаксис =ГПР(искомое_значение; таблица; номер_строки; интервальный_просмотр)
Применение: Для расчета приза определенному сотруднику можно задать интервалы, начиная от которых действует тот либо другой процент от прибыли. Скажем, формула =ГПР(E5;$D$1:$G$2;2;ИСТИНА) будет находить в первой строке таблицы из интервала D1:G2 значение, примерно похожее со значением из ячейки E5, а результатом формулы станет вывод ячейки со 2-ой строчки соответственного столбца.
По вертикали
Аналогичным образом действует функция ВПР – лишь логика деяния незначительно иная. Поиск будет вестись не по горизонтали, а по вертикали, другими словами по ячейкам 1-го столбца, а итог браться из обозначенной ячейки отысканной строчки.
Другими словами для формулы =ВПР(E4;$I$3:$J$6;2;ИСТИНА) будет сравниваться значение ячейки E4 с ячейками столбца I из таблицы интервала I3:J6, а значение будет выдаваться из примыкающей ячейки столбца J.
Выполнение условия ЕСЛИ
При использовании данной функции задается конкретное условие, а следом два результата – один для случаев, если условия выполнено, а другое – напротив. Скажем для сопоставления валютных средств из 2-ух колонок может употребляться таковая формула =ЕСЛИ(C2>B2; «Превышение бюджета»; «В границах бюджета»).
Не считая того, в качестве условия может употребляться иная функция, к примеру, условие ИЛИ и даже очередное условие ЕСЛИ. При всем этом у воженных функций ЕСЛИ быть может от 3 до 64 вероятных результатов). Как пример, =ЕСЛИ(D4=1; «ДА»;ЕСЛИ(D4=2; «Нет»; «Может быть»)).
В качестве результата может также выводиться значение обозначенной ячейки, как текстовое, так цифирное. В таком случае в предстоящем довольно будет поменять значение одной ячейки, без необходимости править формулу во всех местах использования.
Формула ранжирования
Для значения чисел можно употреблять формулу РАНГ, которая выдаст величину всякого числа относительно остальных в данном перечне. При всем этом ранжирование быть может как от наименьшего значения в сторону роста, так и назад.
Для данной функции употребляется три параметра – конкретно число, массив либо ссылка на перечень чисел и порядок. При всем этом если порядок не указан либо стоит значение 0, то ранг определяется в порядке убывание. Хоть какое другое значение для порядка будет отсортировывать значения по возрастанию.
Применение: Для таблицы с доходами по месяцам можно добавить столбец с ранжированием, а в предстоящем по этому столбцу создать сортировку.
Максимум из избранных значений
Обычная, но весьма нужная формула МАКС выдает наибольшее значение из перечня значений. Сам перечень может состоять как из ячеек и/либо их спектра, так и вручную введенных чисел. Всего наибольшее значение можно находить посреди перечня из 255 чисел.
Применение: Ворачиваясь например с ранжированием, заместо ранга можно выводить значение наилучшего показателя за избранный период.
Минимум из избранных значений
Аналогичным образом действует формула поиска малых значений. Схожий синтаксис, оборотный итог на выходе.
Среднее из избранных значений
Для получения среднего арифметического из избранного перечня значений также есть своя формула. Но написание ее в российском языке не настолько разумеется. Звучит она как СРЗНАЧ, опосля что в скобках указываются или определенные значения, или ссылки на ячейки.
Сумма избранных значений
В итоге, самая ходовая функция, которую понимает любой, когда-либо использовавший электрические таблицы Excel. Сложение делается по формуле СУММ, а в скобках задается интервал либо интервалы ячеек, значения которых требуется суммировать.
Куда наиболее увлекательным вариантом является суммирование ячеек, отвечающих определенным аспектам. Для этого употребляется оператор СУММЕСЛИ с аргументами спектр, условие, спектр суммирования.
Применение: К примеру, есть перечень школьников, согласившихся поехать на экскурсионную поездку. У всякого есть статус – оплатил он мероприятие либо нет. Таковым образом, зависимо от содержимого столбца «Оплатил» значение из столбца «Стоимость» будет считаться либо нет. =СУММЕСЛИ(E5:E9; «Да»; F5:F9)
Примечание: Подробную информацию о использовании каждой функции Excel можно отыскать на официальном веб-сайте Microsoft Office.
Excel: 10 формул для работы в кабинете
Офисной программкой Excel пользуются все — и школьники, и суровые банкиры. Мы поведаем про 10 самых нужных функций Excel и объясним, как они работают.
1 СУММ: Сложение
Сумма — самая пользующаяся популярностью функция Excel, которой пользуются полностью все – и школьники, и суровые бухгалтеры, и аналитики. Как надо из наименования, функция складывает значения в обозначенном спектре. Все просто – поставьте в ячейку символ равно, выберете функцию СУММ, выделите область, в какой находятся числа для сложения, и нажмите «ОК». В ячейке покажется сумма избранных значений.
Функция может сложить как два, так и миллион чисел — реальный супер-калькулятор.
2 ЕСЛИ: Условие
Если вы желаете задать условие, используйте формулу ЕСЛИ. Эта нужная операция может стать основным ассистентом при работе с таблицами в Excel. ЕСЛИ играет роль логической функции, которая производит вычисления по данным вами аспектам.
Аргументами функции являются:
- Лог_выражение — тут нужно задать параметр, по которому будут отбираться значения.
- Значение_если_истина — то, что будет на дисплее, если логическое условие правильно.
- Значение_если_ложь — имя значения, которое не соответствует аспекту, обозначенному в логическом выражении.
Вот маленькой пример: нужно рассортировать данные таблицы с продуктом. Зададим логическое выражение: «если стоимость больше 50». Если выражение правильно, будем считать это значение как «Много», если нет — как «Не много». Нажмем «ОК».
Сейчас напротив каждой ячейки со стоимостью будет указана ее черта. Это весьма обычной пример, но освоив ЕСЛИ вы можете делать и сложные логические операции в Excel . Не считая того, функция осознает вложенные условия, а означает, с ее помощью можно решать вправду трудные задачки.
3 МАКС: Максимум
Как отыскать максимум в Excel? Используйте функцию МАКС. Это одна из самых фаворитных операций в офисной программке. Для использования данной функции изберите ячейку, в какой желаете узреть итог, поставьте в нее равно и выберете МАКС. Выделите ячейки, посреди которых нужно отыскать наибольшее значение: помните, спектр должен содержать не больше, чем 255 чисел.
По тому же принципу работает функция для поиска минимума – МИН.
4 СРЗНАЧ: Среднее значение
Данная формула служит для поиска среднего значения посреди огромного количества ячеек таблицы. Именуется она достаточно разумеется – СРЗНАЧ. Аргументом данной функции является спектр, в каком вы желаете отыскать среднее арифметическое.
5. СЧЕТ: Подсчет количества значений
Функция СЧЕТ подсчитывает количество ячеек в обозначенном спектре, которые содержат числа. К примеру, у вас есть 9 позиций продуктов, а нужно выяснить, сколько из их было куплено. Выделяем нужную область со значениями и жмем «ОК» — функция выдаст итог. В нашем случае все можно было посчитать и вручную, а что созодать, если таковых значений сотки? Правильно — употреблять операцию СЧЕТ.
Если же для вас нужно посчитать заполненные ячейки, используйте функцию СЧЕТЗ. Она работает по аналогичному принципу, но возвращает количество ячеек со значениями в данном спектре.
6 СЧЕТЕСЛИ: Подсчет определенных значений
Также в Excel есть функция СЧЕТЕСЛИ — она дозволяет подсчитывать ячейки по данному аспекту. Аргументами в таком случае является не только лишь спектр, но и условие подсчета.
Для наиболее сложных операций существует функция СЧЕТЕСЛИМН — в ее аргументах можно задать несколько критериев счета.
7 СУММЕСЛИ: Сложение при условии
Функция СУММЕСЛИ служит для того, чтоб ложить избранные числа из выделенного спектра. На самом деле, это сочетание СУММ и ЕСЛИ. Аргументами операции являются спектр условия, аспект и область суммирования.
К примеру, в таблице с продуктами есть фрукты по разной стоимости. Необходимо посчитать, сколько было куплено продуктов стоимостью меньше 2. Для этого выделим спектр цен, укажем условие «<2» и область сложения — столбец с количеством. Функция поддерживает и наиболее сложные условия, к примеру, вложенные. Стопроцентно раскрыть ее способности можно при работе с большенными таблицами в бухгалтерии либо экономике.
8. СУММЕСЛИМН: Сложение по почти всем аспектам
Если для вас нужно сложить значения, которые недозволено обрисовать одним условиям, используйте функцию СУММЕСЛИМН. Она дозволяет задать до 127 различных критериев. Данная операция — одна из самых нужных в экономике, потому что открывает вправду широкие способности для работы с таблицами в Excel.
8 СЦЕПИТЬ: Объединение текстовых значений
Весьма нередко в текстовых таблицах хранится много отдельных значений, которые комфортно читать вкупе. К примеру, в таблице с ФИО. Для объединения текста служит операция СЦЕПИТЬ. Все просто: выделите нужные строчки, которые желаете соединить, и нажмите «ОК». Но помните, что пробелы придется добавить самому — сходу в функции либо в строчках с текстом.
10. ВПР: Поиск значений
В работе с несколькими таблицами понадобится функция ВПР. К примеру, у вас есть два перечня — в одном номера продуктов, его заказчики, стоимость и количество, а в другом — те же номера продуктов и их описание. Такие таблицы могут быть весьма большенными, а ID предметов обычно стоят не по порядку и повторяются. Чтоб выяснить, какой продукт прячется под определенным номером, используйте функцию ВПР.
В Excel ВПР обрисовали так: отыскивает значение в последнем левом столбце таблицы и возвращает значение ячейки, находящейся в обозначенном столбце той же строчки. Другими словами, она может отыскать информацию в одной таблице, делая упор на схожее значения из иной.
Аргументами ВПР являются разыскиваемое значение, таблица, номер столбца, из которого нужно вынуть информацию, и интервальный просмотр. В крайнем аргументе нужно задать ЛОЖЬ, если вы ищете четкое значение, и ИСТИНА для поиска ориентировочного.
ВПР считается сложной функцией. Она может обработать огромное количество данных и в считанные секунды отыщет необходимое для вас значение.
Функции в Excel.
Функция в Excel – это предустановленная формула, которая делает вычисления, используя данные значения в определенном порядке. В Excel употребляется огромное количество разных функций. При помощи функций можно ускорять выполнение задач, упрощать формулы и реализовывать вычисления, которые нереально было бы выполнить без их использования.
Синтаксис функций в Excel
Для корректной работы, функция обязана быть написана в определенной последовательности, которая именуется синтаксис. К базисному синтаксису функции относятся символ равенства (=), имя функции (к примеру, СУММ) и один либо наиболее аргументов. Аргументы содержат информацию, которую нужно вычислить. В последующем примере функция суммирует значения в спектре A1:A20.
Вставка функций в Excel
Давайте познакомимся с некими более всераспространенными и нередко применяемыми функциями:
- СУММ – эта функция суммирует значения всех аргументов.
- СРЗНАЧ – описывает среднее арифметическое величин, содержащихся в аргументах. Функция вычисляет сумму значений ячеек, а потом разделяет итог на их количество.
- СЧЁТ – подсчитывает количество чисел в перечне аргументов. Функция полезна для резвого подсчета числа частей в спектре.
- МАКС – описывает наибольшее значение из перечня аргументов.
- МИН – описывает малое значение из перечня аргументов.
В последующем примере мы сделаем ординарную формулу для расчета средней цены за единицу заказанных продуктов, используя функцию СРЗНАЧ.
1. Выделяем ячейку, в которую нужно вставить формулу. В нашем примере мы выделяем ячейку C11.
2. Вводим символ равенства (=) и необходимое имя функции. В нашем случае вводим =СРЗНАЧ.
3. Вводим спектр ячеек в качестве аргумента в круглых скобках. В нашем примере мы вводим (C3:C10). Эта формула суммирует значения в спектре С3:С10, а потом разделяет итог на количество ячеек в этом спектре, тем определяя среднее значение.
Вставка функций при помощи команды Автосумма
1. Выделяем ячейку, в которую нужно вставить формулу. В нашем примере мы выделяем ячейку D12.
2. В группе установок Редактирование на вкладке Основная находим и жмем стрелку рядом с командой Автосумма, а потом выбераем подходящую функцию в раскрывающемся меню. В нашем случае мы выберем Сумма.
3. Избранная функция покажется в ячейке. Команда Автосумма автоматом описывает спектр ячеек для аргумента. В данном примере спектр D3:D11 был избран автоматом, а значения просуммированы, чтоб вычислить полную стоимость. Также можно вручную ввести подходящий спектр.