webdonsk.ruКак сделать сложную функцию в excel
Добавил пользователь Дмитрий К. Обновлено: 31.08.2024
Прежде чем приступить к обзору, рассмотрим значения определений, которые встретятся в статье.
Синтаксис – это формула функции, которая начинается со знака равенства и состоит из 2 частей: названия функции и аргументов, имеющих определенную последовательность и заключенных в круглые скобки.
Аргументы функции могут быть представлены текстовыми, числовыми, логическими значениями или ссылками на ячейки, диапазон ячеек. Между собой аргументы разделяются точкой с запятой.
1. ВПР
ВПР позволяет найти данные в текстовой строке таблицы или в диапазоне ячеек и добавить их в другую таблицу.
Синтаксис
Функция состоит из 4 аргументов и представлена следующей формулой:
? Истина – функция, вводимая по умолчанию, ищет ближайшее к искомому значение. Данные первого столбца должны быть упорядочены по возрастанию или в алфавитном порядке.
? Ложь – ищет точное значение в первом столбце.
Примеры
Рассмотрим несколько примеров использования функции ВПР.
Чтобы найти в табличке нужное значение, понадобится функция ВПР
Используем формулу ВПР
В следующих двух примерах применен интервальный просмотр с двумя вариантами функций: ИСТИНА и ЛОЖЬ.
Применение интервального просмотра с функцией ЛОЖЬ позволяет найти значение, в точности равное искомому
2. ЕСЛИ
Функция ЕСЛИ выполняет проверку заданных условий, выбирая один из двух возможных результатов: если сравнение истинно и если сравнение ложно.
Синтаксис
Формула функции состоит из трех аргументов и выглядит следующим образом:
Примеры
Рассмотрим пример использования обычной функции ЕСЛИ.
Выполнение плана продаж здесь обусловлено наличием суммы выручки больше 30 000 руб.
Чтобы узнать, кто из продавцов выполнил план, а кто нет, нужно ввести следующую формулу:
Вложенные функции ЕСЛИ
В этом примере одна функция вложена в другую, и всего внесено 3 результата
Вложенные функции довольно сложны в использовании и часто выдают ошибки в формуле, поэтому рекомендую пользоваться ими в самых исключительных случаях.
Существует еще один способ использования функции ЕСЛИ – для проверки, пуста ячейка или нет. Для этого ее можно использовать с функцией ЕПУСТО:
Пример совмещения 2 функций, позволяющего выявить верный результат
Вместо функции ЕПУСТО также можно использовать другую формулу:
Формула не требует добавления других функций
ЕСЛИ – одна из самых популярных функций в Excel. Она помогает определить истинность тех или иных значений, получить результаты по разным данным и выявить пустые ячейки, к тому же ее можно использовать в сочетании с другими функциями.
Функция ЕСЛИ является основой других формул: СУММЕСЛИ, СЧеТЕСЛИ, ЕСЛИОШИБКА, СРЕСЛИ. Мы рассмотрим три из них – СУММЕСЛИ, СЧеТЕСЛИ и ЕСЛИОШИБКА.
3. СУММЕСЛИ и СУММЕСЛИМН
Функция СУММЕСЛИ позволяет суммировать данные, соответствующие условию и находящиеся в указанном диапазоне.
Синтаксис
Функция состоит из трех аргументов и имеет формулу:
Пример
В приведенном ниже примере функция суммировала данные запросов, количество переходов по которым больше 100 000.
Если нужно суммировать ячейки в соответствии с несколькими условиями, можно воспользоваться функцией СУММЕСЛИМН.
Синтаксис
Формула функции имеет следующий вид:
4. СЧЕТЕСЛИ и СЧЕТЕСЛИМН
Функция СЧеТЕСЛИ считает количество непустых ячеек, соответствующих заданному условию внутри указанного диапазона.
Синтаксис
Пример
В примере функция подсчитала количество ключей, переходов по которым больше 100 000.
Функция подсчитывает количество элементов, не указывая ссылок на них
В функции СЧЕТЕСЛИ можно использовать только один критерий. Если нужно сделать подсчет по нескольким условиям, примените функцию СЧЕТЕСЛИМН.
Синтаксис
Функция позволяет подсчитать количество ячеек, соответствующих нескольким заданным условиям. Каждому условию соответствует один вариант диапазона ячеек.
5. ЕСЛИОШИБКА
Функция возвращает указанное значение, если вычисление по формуле дает ошибочный результат. Правильный результат она оставляет.
Синтаксис
Функция имеет 2 аргумента и представлена формулой:
Что-то не сходится
Функция знает, что на ноль делить нельзя, поэтому вводит значение, указываемое при возможной ошибке
6. ЛЕВСИМВ
Функция ЛЕВСИМВ позволяет выделить необходимое количество знаков с левой стороны строки.
Синтаксис
Функция состоит из 2 аргументов и представлена формулой:
Пример
Использование функции позволяет посмотреть, как будут выглядеть тайтлы к страницам сайта или статьям.
Получаем автоматическое заполнение тайтла
7. ПСТР
Функция ПСТР позволяет извлечь необходимое количество символов внутри текста, начиная с указанной позиции.
Синтаксис
Формула функции состоит из 3 аргументов:
Пример
Функцию можно применять, чтобы упростить названия тайтлов, убрав стоящие в их начале слова.
Тайтл стал более читабельным
8. ПРОПИСН
Функция ПРОПИСН делает все буквы в тексте прописными.
Синтаксис
Пример
Если ячеек много, мы сэкономим кучу времени
9. СТРОЧН
Функция СТРОЧН делает все буквы в тексте строчными.
Синтаксис
Пример
10. ПОИСКПОЗ
Функция ПОИСКПОЗ помогает найти указанный элемент в массиве ячеек и определяет его положение.
Синтаксис
1 – значение меньше или равно искомому (при указании данного типа нужно учитывать, что просматриваемый массив должен быть упорядочен по возрастанию);
0 – точное совпадение;
-1 – наименьшее значение, которое больше или равно искомому.
Примеры
Рассмотрим следующий пример. Здесь я попыталась узнать, какой из запросов в приведенной таблице имеет количество переходов, которое равно или меньше 900.
Получаем значение 3
Формула функции здесь:
- 900 – искомое значение.
- B2:B6 – просматриваемый массив.
- 1 – тип сопоставления (меньше или равно искомому).
11. ДЛСТР
Функция ДЛСТР позволяет определить длину текста, содержащегося в указанной ячейке.
Синтаксис
Формула функции имеет всего один аргумент – текст (номер ячейки):
Пример
Функцию можно использовать для проверки длины символов в description.
Длина – 125 символов
12. СЦЕПИТЬ
Функция СЦЕПИТЬ позволяет объединить несколько текстовых элементов в одну строку. В формуле для объединения элементов указываются как номера ячеек, содержащих текст, так и сам текст. Можно указать до 255 элементов и до 8192 символов.
Синтаксис
Для того чтобы объединить текстовые элементы без пробелов, используются следующие формулы:
Примеры
В приведенном ниже примере введена формула:
Сделаем ФИО читаемым при помощи формулы
Для того же, чтобы слова в строке разделялись пробелами, в формулу необходимо вставить знаки пробелов в кавычках:
В следующем примере функция представлена формулой:
Получилось. Текст стал читаемым
Существует и другой вариант добавления пробелов в формулу функции – введение слов, заключенных в кавычки вместе с пробелами:
Не перепутайте. Номера ячеек не сработают
13. ПРОПНАЧ
Функция ПРОПНАЧ преобразует заглавные буквы всех слов в тексте в прописные (верхний регистр), а все остальные буквы – в строчные (нижний регистр).
Синтаксис
Функция представлена короткой формулой, имеющей всего один аргумент:
Пример
Рассмотрим пример, в котором представлены образцы с различными вариантами написания букв. Функция быстро привела их в читабельное состояние.
Получили читаемый текст, исправив регистровку букв в ячейках
Эту функцию очень удобно использовать при составлении списков с именами собственными, для преобразования текстовых элементов, напечатанных строчными, прописными или различными по размеру буквами.
14. ПЕЧСИМВ
Функция ПЕЧСИМВ позволяет удалить все непечатаемые знаки из текста.
Синтаксис
Пример
В приведенном примере текст в ячейке A1 содержит непечатаемые знаки конца абзаца.
Если в ячейке присутствовали непечатаемые знаки конца абзаца, то они будут автоматически удалены
Эту функцию нужно использовать в тех случаях, когда текст переносится в таблицу из других приложений, имеющих знаки, печать которых невозможна в Excel.
15. СЖПРОБЕЛЫ
Функция удаляет все лишние пробелы между словами.
Синтаксис
Пример
Лишние пробелы удалены сразу
Функция простая и полезная. Единственный минус состоит в том, что она не различает границ слов, и если внутри него стоят пробелы, функция этого не поймет и не удалит их.
16. НАЙТИ
Функция НАЙТИ позволяет обнаружить искомый текст внутри текстовой строки и указывает на начальную позицию этого текста относительно начала просматриваемой строки.
Синтаксис
Функция НАЙТИ состоит из 3 аргументов и представлена формулой:
В первом случае функция находит начальную позицию символа, с которого начинается искомый текст, а во втором начальная позиция определяется указанным количеством байтов.
Пример
В примере функция представлена следующей формулой: =НАЙТИ("чай";A4)
Аналогичный результат даст функция НАЙТИБ (1 байт равен одному символу)
17. ИНДЕКС
Функция ИНДЕКС позволяет возвращать искомое значение.
Синтаксис
Формула функции ИНДЕКС имеет следующий вид:
Пример
Можно заменить ВПР, применив ИНДЕКС + ПОИСКПОЗ
Функцию ИНДЕКС можно использовать вместе с функцией ПОИСКПОЗ с целью замены функции ВПР.
18. СОВПАД
Функция проверяет идентичность двух текстов, и, если они совпадают, выдает значение ИСТИНА, если различаются – значение ЛОЖЬ.
Синтаксис
Пример
Получаем значение true
Пары слов из строк 1 (A1 и B1) и 2 (A2 и B2) различны по написанию, поэтому функция выдает значение ЛОЖЬ, а слова из 3-й строки идентичны, поэтому определяются как ИСТИНА.
Использование функции будет полезно при анализе большого объема информации, чтобы выявить разное написание одних и тех же слов.
19. ИЛИ
Логическая функция ИЛИ возвращает значение ИСТИНА, если хотя бы один аргумент в формуле имеет значение ИСТИНА, и значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Синтаксис
Пример
Формула в примере появляется значение ИСТИНА, так как 2 из 3 аргументов имеют значение ИСТИНА.
Функция И возвращает значение ИСТИНА, если все аргументы в формуле имеют значение ИСТИНА, и значение ЛОЖЬ, если хотя бы один из аргументов имеет значение ЛОЖЬ.
Синтаксис
Функция может содержать множество аргументов и имеет формулу:
Проверили корректность формулы. Получилось значение true
В этом примере все аргументы имеют значение ИСТИНА, поэтому результат соответствующий.
Функции И и ИЛИ просты в использовании, но, если сочетать их друг с другом или с другими функциями (ЕСЛИ и НЕ), можно вывести более сложные и интересные формулы.
21. СМЕЩ
Функция СМЕЩ возвращает ссылку на диапазон, отстоящий от ячейки или группы ячеек на указанное число строк и столбцов.
Синтаксис
Функция состоит из 5-ти аргументов и представлена следующей формулой:
Рассмотрим каждый из аргументов:
Пример
Рассмотрим пример использования функции СМЕЩ, имеющую следующую формулу:
A4 – ссылка на ячейку, от которой вычисляется смещение.
Обратите внимание на подсказку. Мы получили значение 27 – корректно
Зачем маркетологу Excel
Функции Excel помогут при анализе данных страниц сайта, подсчете количества символов в тайтле и description, преобразовании текста, поиске различных элементов в таблице.
В качестве первого примера для Excel рассмотрим самую популярную функцию F(x)=X^2. График от этой функции в большинстве случаев должен содержать точки, что мы и реализуем при его составлении в будущем, а пока разберем основные составляющие.
-
Создайте строку X, где укажите необходимый диапазон чисел для графика функции.
Если график должен быть точечным, но функция не соответствует указанной, составляйте его точно в таком же порядке, формируя требуемые вычисления в таблице, чтобы оптимизировать их и упростить весь процесс работы с данными.
Вариант 2: График функции y=sin(x)
Функций очень много и разобрать их в рамках этой статьи просто невозможно, поэтому в качестве альтернативы предыдущему варианту предлагаем остановиться на еще одном популярном, но сложном — y=sin(x). То есть изначально есть диапазон значений X, затем нужно посчитать синус, чему и будет равняться Y. В этом тоже поможет созданная таблица, из которой потом и построим график функции.
-
Для удобства укажем всю необходимую информацию на листе в Excel. Это будет сама функция sin(x), интервал значений от -1 до 5 и их шаг весом в 0.25.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Начинающие пользователи Office часто спрашивают, как составить формулу в Excel, и какие возможности доступны при работе с программой. Ниже приведем подробную инструкцию для чайников, позволяющую быстро разобраться с вопросом и научиться выполнять вычисления любой сложности.
Математические операторы
Одно из главных преимуществ Excel — проведение расчетов с помощью математических формул, делающих таблицы более удобными и гибкими.
Программа способна выполнять не только простые задачи, но и делать весьма трудные расчеты. Для начала рассмотрим стандартные действия:
Для составления любой формулы Excel вначале необходимо поставить знак равно (=).
Для удобства в программе предусмотрена опция, позволяющая делать математические действия в привязке к номерам ячеек.
Суть в том, что пользователь использует конкретные адреса, после чего Excel выполняет расчеты.
Применение метода дает плюсы в виде уменьшения числа помарок и облегчения внесения изменений.
С учетом сказанного можно подвести итог, как правильно составить формулу. Для этого поставьте в нужную ячейку знак равно, а после этого укажите необходимые действия, к примеру, В1+В2 или А1*А2.
Thank you!
We will contact you soon.
Простые формулы
Как правило, пользователи Excel обходятся простыми математическими действиями со знаками вычитания, сложения и другими. С рассмотрения таких расчетов мы начнем инструкцию.
Правила создания формул
Любой пользователь Excel должен уметь составлять формулы разной сложности. Это позволяет задействовать весь потенциал программы и сделать простыми расчеты с большим числом действий.
Приведем пример простой формулы по сложению двух цифр. Алгоритм такой:
- Выделите ячейку, в которой должен быть итоговый результат (С3).
- Поставьте знак равно. Учтите, что он появляется не только в ячейке, но и в верхней строке.
- Укажите адрес секции, которая должна быть первой, к примеру, С1.
- Поставьте знак, который интересует в конкретной формуле, например, плюс (+).
- Введите адрес графы для второго числа, к примеру, С2.
- Проверьте, что у вас получилась надпись = С1+С2, жмите на Ввод.
Выше мы привели простое пояснение для начинающих, позволяющее понять общий принцип расчетов в Excel.
Удобство в том, что пользователь Excel может вносить правки в данные таблице, не корректируя при этом формулу.
Если поменять в С1 или С2 значение, в С3 итоговый параметр также поменяется. Недостаток в том, что Эксель не проверяет формулы на ошибки, поэтому этот момент необходимо контролировать самому.
Выделение с помощью мышки
Для упрощения работы можно минимизировать применение клавиатуры и использовать для ввода формулы кнопку мышки. Сделайте следующие шаги:
- Выделите секцию в таблице, где будет находиться формула (В3).
- Поставьте знак равно (=).
- Жмите на ячейку, которая должна стоять первой в расчетах. Для текущего примера В1. Цифра будет обведена, а номер секции появится в итоговой графе.
- Укажите действие, которое необходимо сделать. Как вариант, жмите на умножение (*).
- Выделите ту ячейку, цифра в которой должна стоять второй в формуле (В2). Обратите внимание на то, что необходимый участок будет обведен пунктирной линией.
- Жмите на Ввод, чтобы система выполнила расчет
В дальнейшем можно копировать формулы в другие ячейки, чтобы не переносить их несколько раз.
Редактирование
Бывают ситуации, когда необходимо изменить уже сделанную формулу в Excel. Причина может быть любой — пересмотр алгоритма расчетов, ошибка или другие проблемы.
Для внесения правок сделайте следующее:
- Укажите секцию, где необходимо внести изменения. В нашем случае это С3.
- Перейдите в строку формулы вверху, чтобы внести коррективы. Как вариант, можно нажать прямо на ячейку, чтобы глянуть и внести изменения в формулу.
- Внесите новые данные и обратите внимание, какие ячейки при этом подсвечиваются программой. На этом этапе будьте внимательны, чтобы избежать ошибок.
- Жмите Ввод и подтвердите внесенные изменения. К примеру, если раньше формула имела вид С1+С2, ее можно поменять на С1-С2 или любой другой вариант. Главное, чтобы в указанных ячейках стояли цифры.
При желании проделанные действия можно отметить. Для этого жмите на Esc клавиатуры или выберите знак Отмена в строчке с формулой.
Для просмотра заданных арифметических действий можно сделать проще — жмите на комбинацию Ctrl+’.
Сложные формулы
Бывают ситуации, когда простым действием не обойдешься и необходимо задать сложную формулу.
Здесь необходимо учесть общие правила для математических действий. Они просты.
Умножение и деление выполняется в первую очередь. При этом операции в скобках имеют приоритет перед остальными действиями.
Приведем вариант формулы для ячейки С4. В ней можно указать следующие данные (В2+В3)*0,3.
Это означает, что формула сначала суммирует два показателя, а полученное значение рассчитывается с процентами (30% записывается в виде 0,3).
Во время расчетов Excel придерживается заданного порядка. Сначала выполняются действия в скобках, а уже потом делается умножение.
Записывая сложную формулу, важно учитывать правила расчетов, ведь в ином случае результат будет ошибочным. Применение скобок — лучший способ задать правильный порядок вычислений.
Помните, что Excel делает расчет с учетом введенных данных и не предупреждает об ошибках. Проверяйте правильность ввода самостоятельно. Главное — корректно расставить скобки и задать приоритеты вычислений.
Функции программы
Одна из главных особенностей Excel — наличие специальных функций. По сути, это формула, делающая определенные расчеты с учетом заданных параметров. Они созданы для ускорения и упрощения вычислений разного уровня сложности.
Синтаксис
Для правильной работы Excel функция должна записываться в конкретной последовательности.
К примеру, вам надо сложить значения в ячейках В1, В2, В3, В4. СУММ – функция добавляющая значения. При этом формат записи имеет следующий вид.
Сначала ставится знак равно (=). После него идет функция СУММ, а за ней диапазон ячеек (В1:В4).
В программе имеются опции, в которых вообще не указываются аргументы. Если написать СЕГОДНЯ (), приложение вернет день с учетом времени в ОС компьютера.
Основные функции
Чтобы выполнять действия с несколькими условиями и проводить более серьезные расчеты, разберитесь с базовыми функциями.
Кратко рассмотрим их названия и особенности:
В Эксель применяются и другие функции, но они менее востребованы.
Правила использования
Для лучшего понимания рассмотрим, как правильно добавлять функцию в Excel. Используем параметр СРЗНАЧ.
Алгоритм действий такой:
- Жмите на ячейку, где необходимо установить формулу — В11.
- Пропишите знак равно =, а после укажите название нужной опции СРЗНАЧ.
- Укажите диапазон секций в круглых скобках (В3:В10).
- Кликните на Ввод.
После указания этих параметров программа суммирует данные в ячейках с В3 по В10, а после этого вычисляет их среднее значение.
Применение Автосуммы
Для удобства почти любую опцию можно вставить с помощью Автосуммы. Сделайте следующее:
- Выберите и жмите на секцию, в которую необходимо вбить формулу (С 11).
- В группе Редактирования в разделе Главная отыщите и нажмите на стрелку возле надписи Автосумма.
- Выберите нужную опцию в появившемся меню, например, Сумма.
- Программа автоматически выбирает диапазон ячеек для суммирования, но эти данные можно задать вручную путем внесения правок в формулу.
Как и в рассмотренных выше случаях, результат необходимо проверять во избежание ошибок.
Комбинированные формулы
Дополнительное удобство Excel состоит в возможности комбинирования нескольких формул для проведения более сложных расчетов.
Рассмотрим ситуацию, когда необходимо просуммировать три числа и умножить их на коэффициент 1,5 или 1,6 в зависимости от того, какое получилось число (больше или меньше 100).
В таком случае запись имеет следующий вид: =ЕСЛИ(СУММ(А2:С2) Транскрибация онлайн
При сложных вычислениях Вам может потребоваться использовать "формулы в формуле" Excel. Можно ли в Excel использовать вложенные формулы? В статье рассмотрены основы данного вопроса, в том числе приведены простые примеры и видеоурок.
При использовании Excel только для простых вычислений формулы или не используются вообще, или применяются крайне редко. Однако если Вы используете программу для сложной обработки данных, то рано или поздно столкнётесь с ситуацией, при которой встроенных формул Excel окажется явно недостаточно для решения задачи.
То есть, например, Вам нужно что-то вычислить, но такой стандартной функции нет. В таком случае можно пойти двумя путями: разбить вычисление на части или создать комплексную формулу из имеющихся стандартных. Стоит однако помнить, что разбиение вычислений на части (в разных ячейках) приводит к усложнению понимания работы листа Excel, в том числе из-за дополнительных (и по сути ненужных) ячеек с промежуточными результатами.
Использование вложенных формул в Excel
[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов
Пример создания формулы в формуле в Excel
Рассмотрим очень простой пример, в котором продемонстрируем применения вложенных формул. Изначально у нас есть два столбца с числами:
| А | B | |
|---|---|---|
| 1 | 10 | 60 |
| 2 | 20 | 70 |
| 3 | 30 | 80 |
| 4 | 40 | 90 |
| 5 | 50 | 100 |
Что нужно сделать: вычислим сумму средних значений обоих столбцов.
Если не использовать комплексные вычисления, то нам бы потребовалось целых три ячейки:
- В первой ячейке: вычислить среднее значение для колонки 1 (столбец А) при помощи функции СРЗНАЧ;
- Во второй колонке сделать то же самое, но для колонки 2 (столбец B);
- В третьей ячейке при помощи функции СУММ сложить два промежуточных результата;
Это типичный подход к решению задач такого рода. В принципе, две лишние ячейки вроде бы не жалко, но если вычислений на листе много, то получится не очень красиво. Поэтому решим вопрос без использования промежуточных ячеек через комплексную формулу с использованием вложения стандартных функций друг в друга.
В качестве аргументов встроенных формул разрешается использовать другие формулы, если они возвращают приемлемое для данной формулы значение (в данном случае это должно быть число).
Сумма будет равна 110. Вы можете проверить это, вычислив среднее значение отдельно для каждого столбца и сложив их.
Комплексные формулы в Excel — это не сложно
Выше был приведён очень простой пример. Тем не менее, в сложных случаях принцип создания комплексных формул Excel ничем не отличается от показанного в примере. Самое сложное здесь заключается в том, что нужно очень аккуратно вкладывать формулы друг в друга, чтобы не возникали ошибки синтаксиса.
Также стоит помнить о том, что вложенные формулы большой сложности получаются сложно читаемыми, поскольку к сожалению в Excel любая формула пишется в одну строку.
Дополнительные сложности могут возникнуть при автоматическом заполнении ячеек формулами. Если Вы используете автоввод (протяжку мышкой, копирование) для созданной комплексной вложенной формулы, то внимательно проверяйте полученный результат.
Скачайте пример использования вложенных формул
Простой пример, демонстрирующий принцип создания комплексных формул путём вложения их друг в друга Вы можете скачать ниже на странице. В архиве один Excel файл, в котором есть рабочий пример, рассмотренный в данной статье. Разобравшись с нашим примером, попробуйте создать свой собственный для закрепления результата.
Свои примеры по использованию вложенных формул Вы можете привести после статьи в комментариях. Пожалуйста, не просите в комментариях создать формулу для решения какой-либо задачи, поскольку мы не можем этим заниматься .
Уникальная метка публикации: 5080313C-6C47-CFED-F141-288A2303640A
Источник: //artemvm.info/information/uchebnye-stati/microsoft-office/formula-v-formule-excel-vlozhennye-formuly/
Смотреть видео
Формула в формуле Excel (вложенные формулы)
Файлы для загрузки
Вы можете скачать прикреплённые ниже файлы для ознакомления. Обычно здесь размещаются различные документы, а также другие файлы, имеющие непосредственное отношение к данной публикации.
Читайте также: