В экселе формулы: Самые полезные формулы Excel — Раздел 5. Урок 1 — Онлайн-курс «Digital-аналитика» — This is Data

x

=ТРАНСП() — для того чтобы вертикальный диапазон ячеек сделать горизонтальным и наоборот.

Из данной статьи вы узнаете, как в Excel ввести формулу массива.

Принцип ввода формулы массива расскажу на примере 2-х формул 

=ЛИНЕЙН() и =ТРАНСП().  

Для того, чтобы с помощью формулы =ЛИНЕЙН() рассчитать коэффициенты линейного тренда y=a+bx (a) и (b), необходимо:

1. Ввести в формулу данные =ЛИНЕЙН(известные значения y (например, объём продаж по месяцам), известные значения x (номера периодов), константа (коэффициент (a) в формуле y=a+bx, для его расчета ставим «1»), статистика (вводим «0»)) (см. файл с примером).

2. Установить курсор в ячейку с формулой и выделить соседнюю справа, как на рисунке:

3. Для ввода формулы массива нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

Коэффициенты линейного тренда y=a+bx (a) и (b) рассчитаны.

 

2-й пример

(см. вложенный файл), в нём мы рассмотрим, как перевернуть диапазон и сделать из горизонтального вертикальный. Для этого воспользуемся функцией =ТРАНСП().

Как она работает:

1. В формулу вводим горизонтальный диапазон, который хотим сделать вертикальным:

2. Выделяем вертикальный диапазон, равный по количеству ячеек выделенному горизонтальному, вверху диапазона должна быть введена формула =ТРАНСП();

3. Для ввода формулы массива нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

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

 

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

1. выделить массив — это диапазон ячеек, в которые Excel выведет данные, 
2. и нажать чудо комбинацию клавиш  — F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

Всё просто, попробуйте, и если остались вопросы, задавайте их в комментариях. 

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

• Novo Forecast Lite — автоматический расчет прогноза в Excel.
• 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

• Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Статья полезная? Поделитесь с друзьями

 

Добавить комментарий

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

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

Создание формулы в Экселе начинается с написания знака равенства «=» в ячейке, в которой будет создаваться формула. Например, введя в ячейке следующую формулу «=2+3», после нажатия «Enter» мы увидим уже результат вычислений цифру «5», а не саму формулу. Но возможности Экселя не ограничиваются таким простым способом записи формулы. Для проведения вычислений в формуле можно указывать ссылки на другие ячейки, данные в которых должны использоваться для расчетов.

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

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

 

 

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

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

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

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

Также статьи на сайте chajnikam.ru связанные с программой Excel:
Как добавить столбец в Экселе?
Как создание выпадающего списка в Excel
Создание таблиц в Excel
Объединение ячеек в Экселе

Как использовать Excel If Function: подробное руководство

MS Excel — это мощный инструмент, помогающий повысить производительность пользователя при работе с данными.

Это связано с широким набором функций, присутствующих в Excel. Функция ЕСЛИ в Excel — одна из таких функций, которая позволяет легко классифицировать данные в соответствии с заданным условием. Существует пять вариантов функции ЕСЛИ в Excel, которые мы будем обсуждать.

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

Функция ЕСЛИ — это встроенная функция Excel, которая проверяет условие. Если условие выполнено, возвращается истинное значение; в противном случае возвращается ложное значение. Синтаксис следующий:

ЕСЛИ(условие, истинное_значение,ложное_значение)

Давайте рассмотрим пример, чтобы лучше понять это.

Здесь мы используем данные управления запасами, чтобы продемонстрировать использование функции ЕСЛИ.

Формула  ЕСЛИ(G2=»В ожидании»,»ДА»,»НЕТ»)   проверяет, находится ли заказ в состоянии ожидания. Если это так, выполняется ИСТИННАЯ часть оператора (т. е. «ДА»), в противном случае выполняется значение ЛОЖЬ, отображающее «НЕТ».

Где еще можно использовать эту функцию?

Функцию ЕСЛИ можно использовать для выполнения вычислений на основе условия. Вот пара примеров формул:

1. Когда функция проверяет условие, вместо возврата значений (true_value или false_value) она может выполнить вычисление и вернуть результирующее значение.

• ЕСЛИ(С2>20, С2*0,05, С2*0,07)

Формула проверяет, превышает ли значение в ячейке C2 20. Если значение TRUE, значение в ячейке C2 умножается на 0,05 (или пять процентов). Если FALSE, оно умножается на 0,07 (или семь процентов).

2. Условием внутри ЕСЛИ может быть любая функция. Например, ЕПУСТО, ЕЧИСЛО, СРЗНАЧ, СУММА и т. д.

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

• ЕСЛИ(ПУСТО(C2), «ИСТИНА», «ЛОЖЬ»)

 Формула возвращает ИСТИНА, если ячейка C2 пуста, и ЛОЖЬ в противном случае.

Что такое функция IFNA?

Функция IFNA относится к группе функций проверки ошибок в Excel. Эта функция помогает обрабатывать ошибки #N/A.

Синтаксис следующий:

IFNA(формула, значение_если_ошибка)

Формула выполняется первой, и функция IFNA проверяет, является ли результат ошибкой #N/A. Это ошибка «нет доступного значения», которая возникает, когда значение не может быть найдено.

В этом случае возвращается значение_если_ошибка. Если ошибка #Н/Д не возникает, будет возвращен результат формулы.

Рассмотрим следующий пример:

• Чтобы понять функцию IFNA, давайте сначала разберемся с функциями, которые генерируют ошибку #N/A. К таким функциям относятся ВПР, ГПР и т. д. 
• ВПР — это функция, помогающая искать значение в таблице по вертикали. Синтаксис этой функции: ВПР (значение, таблица, индекс_столбца, [диапазон_просмотра])

Как мы видим, функция ВПР генерирует ошибку #Н/Д, так как значение не найдено, поэтому попробуем использовать ее с IFNA для перехвата ошибки #Н/Д.

Мы будем использовать следующую формулу: IFNA(VLOOKUP(B9,A2:F5,6,TRUE),» NOT FOUND»)

• Функция IFNA сначала выполняет функцию ВПР, которая генерирует ошибку #Н/Д, если значение не найдено. Поэтому мы будем использовать функцию IFNA вместе с VLOOKUP.
• В случае ошибки #Н/Д выполняется и отображается альтернативное значение (значение_если_ошибка).

Поскольку значение в этом примере недоступно, мы получим сообщение «НЕ НАЙДЕНО».

Что такое функция ЕСЛИОШИБКА?

ЕСЛИОШИБКА также относится к группе функций проверки ошибок в Excel. Это разновидность функции ЕСЛИ. Функция ЕСЛИОШИБКА помогает распознавать ошибки и эффективно их обрабатывать.

Синтаксис следующий:

ЕСЛИОШИБКА(формула, значение_если_ошибка)

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

Демонстрация, показанная ниже, дает более четкое представление о функции ЕСЛИОШИБКА.

В этом примере используется формула ЕСЛИОШИБКА(C1/C2, «Ошибка в расчете»)

Сначала выполняется C1/C2. Если это приведет к какой-либо ошибке, будет отображено альтернативное значение. (т. е. «Ошибка в расчете»).

Как использовать вложенные ЕСЛИ в Excel

Вложенную функцию ЕСЛИ можно использовать для проверки нескольких условий. Он состоит из одной функции ЕСЛИ внутри другой функции ЕСЛИ. Синтаксис:

ЕСЛИ (условие1, истинное_значение1, ЕСЛИ (условие2, истинное_значение2, ложное_значение2))

• Выполняется первая функция ЕСЛИ и оценивается условие1
• Если условие1 истинно, то выполняется истинное_значение1.
• Значение false_value1 встроено в следующую функцию ЕСЛИ. Следовательно, если условие1 ложно, следующая функция ЕСЛИ выполнит
• Условие2 проверено, и если условие2 истинно, возвращается true_value2. В противном случае возвращается false_value2.

При необходимости можно продолжить вложение до семи функций ЕСЛИ.

В следующей демонстрации показано, как работает вложенный оператор IF:

IF(B4=»Красота»,»7%», IF(B4=»Электроника», IF(B4=»Спорт»,»4%»,»Не применимо»)))

• В таблице справа показан процент налога для каждой линейки продуктов. Мы формулируем это с помощью вложенной функции ЕСЛИ.
• Мы проверяем, является ли значение ячейки «Красота», которое возвращает «7%», если TRUE.
•  Если значение FALSE, значение ячейки проверяется на «Электроника», в которой отображается «5%», если TRUE.
• В противном случае он снова проверит, является ли значение ячейки «Спорт». На этот раз отображается «4%», если ИСТИНА, и «Неприменимо», если ЛОЖЬ.

Функция IFS

Функция IFS устраняет сложность использования вложенных ЕСЛИ в Excel. Он использует более короткий и простой синтаксис, ориентированный на несколько критериев.

Эта функция доступна только в Excel 2019 и Excel для Office 365.

Синтаксис для IFS:

IFS(условие1, значение1, [условие2, значение2],…)

Функция IFS состоит из пары условие-значение. Он проверяет условия по порядку и возвращает значение, соответствующее первому результату TRUE. Эта функция может проверять до 127 условий.

Например:

=IFS(C2=»Бакалея»,»5%»,C2=»Электроника»,»12%»,C2=»Автомобили»,»28%»)

• В этом примере проверяется значение C2.
• Если C2 содержит значение «Бакалея», ставка налога будет отображаться как «5%».
• В противном случае снова проверяется значение в C2, чтобы убедиться, что оно содержит «Электроника». Если TRUE, налоговая ставка будет «12%».
• В противном случае значение C2 окончательно проверяется на «Автомобили». Если это условие выполнено, отображается «18%».
• Если ни одно из условий не выполняется, эта функция вернет ошибку #Н/Д.

Подробнее об Excel IF

Сочетание функции ЕСЛИ с другими функциями Excel повысит вашу производительность и поможет выполнять сложные вычисления.

Вот несколько примеров, которые показывают, как комбинировать функцию ЕСЛИ Excel с другими:

Функция ЕСЛИ с И

Синтаксис для этого будет:

ЕСЛИ(И(условие1, условие2,…), истинное_значение, ложное_значение)

Эта функция проверит, выполнены ли все \условия (условие1, условие2 и т. д.), и только после этого будет выполнено значение true_value. Даже если одно условие не выполнено, false_value будет выполнено.

Например:

ЕСЛИ(И(B2>5,C2<5),"Отлично","Удовлетворительно")

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

Функция ЕСЛИ с ИЛИ

Синтаксис для этого будет следующим:

ЕСЛИ(ИЛИ(условие1, условие2,…), истинное_значение, ложное_значение)

Если выполняется хотя бы одно условие из всех условий (условие1, условие2 и т. д.), то будет выполнено значение true_value. Если все условия FALSE, то выполняется false_value.

Например:

ЕСЛИ(ИЛИ(B2=»Фрукты»,B2=»Овощи»),»Бакалея»,»Другое»)

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

Получите опыт работы с новейшими инструментами и методами бизнес-аналитики с помощью магистерской программы для бизнес-аналитиков. Зарегистрируйтесь сейчас!

Заключение

Мы надеемся, что эта статья помогла вам познакомиться с функцией ЕСЛИ в Excel. Вы можете улучшить свои навыки работы с Excel на практике, работая с большим количеством данных, и вы будете на пути к тому, чтобы стать профессионалом в Excel. Вы можете улучшить свои навыки работы с Excel, записавшись на бесплатный курс Business Analytics with Excel от Simplilearn.

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

Shruti M

Старший аналитик-исследователь

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

Popular from Shruti M

Статья

25 лучших формул Excel, которые вы должны знать

В этой статье мы обсудим различные функции и формулы Microsoft Excel.

By

Shruti MLПоследнее обновление

2 февраля 2023 г.

Видеоурок

60 лучших вопросов и ответов на интервью с аналитиком данных за 2023 год

В этой статье вы познакомитесь с ведущими вопросами аналитика данных.

По

Shruti MLПоследнее обновление

7 февраля 2023 г.

Артикул

Как использовать функцию ВПР в Excel? Пошаговое руководство

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

Автор:

Shruti MLПоследнее обновление:

1 февраля 2023 г. интервью.

По

Shruti MLПоследнее обновление

30 января 2023 г.

Видеоруководство

10 лучших приложений для работы с большими данными в различных отраслях

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

By

Shruti MLПоследнее обновление

7 февраля 2023 г.

Видеоруководство

10 типов кибератак, о которых следует знать в 2023 году

предотвратить.

Автор

Shruti MLПоследнее обновление

31 января 2023 г.

Статья

Как удалить дубликаты в Excel? Пошаговое руководство

В этой статье мы обсудим четыре простых подхода к удалению дубликатов в Excel.

Автор:

Shruti MLПоследнее обновление

28 октября 2022 г.

Видеоруководство

80 лучших вопросов и ответов из интервью с Hadoop

В этой статье перечислены наиболее распространенные вопросы, которые задают в ходе интервью с разработчиками Hadoop, а также типовые ответы.

By

Shruti MLПоследнее обновление

12 декабря 2022 г.

Видеоруководство

10 лучших вакансий в сфере кибербезопасности в 2023 году: информация о карьере и зарплате

вы можете начать свою карьеру.

By

Shruti MLПоследнее обновление

24 января 2023 г.

Статья

Excel Vs. Google Таблицы: что нужно знать?

В этой статье более подробно рассматриваются два приложения и основные различия между Excel и Google Sheets.

Автор:

Shruti MLПоследнее обновление

6 февраля 2023 г.

Статья

Как создать абсолютную ссылку в Excel?

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

Автор:

Shruti MLПоследнее обновление:

9 ноября 2022 г.0003

21 июля 2022 г.

Статья

Что такое СЧЁТ в Excel и как использовать функцию СЧЁТ?

В этой статье мы обсудим функцию СЧЁТ в Excel.

Автор:

Shruti MLПоследнее обновление:

27 октября 2022 г. проект.

По

Shruti MLПоследнее обновление

8 ноября 2022 г.

Статья

SQL UPDATE: Урок по обновлению таблиц базы данных

В этой статье о команде SQL Update мы узнаем, как обновлять существующие записи с помощью SQL.

By

Shruti MLПоследнее обновление

16 сентября 2022 г.

Статья

Как сортировать данные в Excel: лучшее руководство

В этой статье вы узнаете, как сортировать данные в Excel. Кроме того, вы также узнаете, как фильтровать данные.

By

Shruti MLПоследнее обновление

29 июня 2022 г.

Статья

Как транспонировать данные Excel? Три метода, которые необходимо знать

В этой статье вы узнаете о различных способах транспонирования данных Excel.

Автор:

Shruti MLПоследнее обновление

13 сентября 2022 г.

Видеоурок

Учебник по Mapreduce: все, что вам нужно знать

В этой статье объясняется концепция MapReduce.

By

Shruti MLПоследнее обновление

12 сентября 2022 г.

Статья

Анализ «что, если» в Excel: универсальное решение для управления сценариями в Excel

Вот все, что вам нужно знать об анализе «что, если» в Эксель.

Автор:

Shruti MLПоследнее обновление

15 июля 2022 г.0003

Shruti MLПоследнее обновление

12 декабря 2022 г.

Видеоурок

Учебник по пряже

YARN — это аббревиатура от Yet Another Resource Negotiator. Хотите узнать больше? Посмотрите этот урок!

By

Shruti MLПоследнее обновление

11 октября 2022 г.

Видеоруководство

Ваше универсальное решение для изучения слияния писем в Excel и его реализации

Узнайте все о слиянии писем в Excel.

По

Shruti MLПоследнее обновление

13 сентября 2022 г.

Статья

Типы данных в MongoDB: распространенные типы данных MongoDB

Узнайте о некоторых наиболее часто используемых типах данных в MongoDB на примерах.

By

Shruti MLПоследнее обновление

4 октября 2022 г.

Статья

Параллельная обработка Spark

Изучите наиболее важные элементы Spark, то есть параллельную обработку.

По

Shruti MLПоследнее обновление

12 декабря 2022 г.

Статья

Ваше универсальное решение для изучения Deque в структуре данных с нуля

Изучите deque в структуре данных, чтобы понять его функции и приложения 14, 2021

Видеоруководство

Ваше универсальное решение, которое поможет вам научиться конвертировать PDF в Excel

Узнайте, как конвертировать PDF в Excel и сохранить данные в таблице данных Excel

By

Shruti MLПоследнее обновление

12 сентября 2022 г.

Видеоруководство

Как установить Hadoop на Ubuntu

Этот блог представляет собой руководство по установке Hadoop на Ubuntu.

By

Shruti MLПоследнее обновление

2 июня 2021 г.

Видеоурок

PHP Вопросы для интервью

Вот 40 лучших вопросов и ответов на PHP-интервью, которые помогут вам пройти собеседование.

По

Shruti MLПоследнее обновление

4 ноября 2022 г.

Видеоруководство

Лучшие компьютерные взломы всех времен

Вы познакомитесь с самыми смертоносными кибератаками, с которыми когда-либо сталкивалось человечество.

Автор:

Shruti MLПоследнее обновление

5 марта 2021 г.