✅ Складні формули в Excel - Приклади складних формул в Excel

Вступ до складних формул в Excel

Складні формули в Excel (Зміст)

Вступ до складних формул в Excel
Виберіть Функція Служить функцією пошуку
VLOOKUP З ВИБОРУ
ЯКЩО З функцією І & АБО

Вступ до складних формул в Excel

Excel поставляється з багатьма корисними формулами, у нас є категорія "Фінанси", "Логіка", "Текст", "Дата та час", "Пошук і посилання" та "Математика та триггер". Кожна окрема формула є достатньо хорошою для проміжних користувачів, де вони стикаються з прямими ситуаціями вперед, але, коли ви переходите на наступний рівень, ваша майстерність потребує підвищення рівня, тому в цьому плані застосування більш складних формул в excel відіграє важливу роль у вирішенні складних ситуацій. Маючи на увазі цю статтю, ми проведемо вас через деякі важливі і дуже потрібні складні формули. Читайте далі.

Виберіть Функція Служить функцією пошуку

Не багато з вас використовували функцію CHOOSE, але це дуже корисно і для функції пошуку.
Ви можете завантажити цей комплексний шаблон формул Excel тут - Комплексний формул шаблон Excel. Наприклад, подивіться наведені нижче дані.

Ми щомісяця маємо дані клієнтів під цією таблицею, у нас є кожна таблиця клієнтів. У цій таблиці в комірці B13 я створив випадаючий список місяця, так що при зміні цього випадаючого списку ми повинні отримати загальну кількість вибраного місяця.

Створіть одну таблицю, як показано нижче.

Застосуйте VLOOKUP до комірки D13.

Тепер відкрийте функцію CHOOSE у комірці B14.

Виберіть номер INDEX як клітинку VLOOKUP і зробіть її абсолютною еталонною.

Тепер виберіть для кожного аргументу кожен місяць номери продажів.

Закрийте дужку і натисніть клавішу введення.

Тепер скопіюйте та вставте формулу також у комірки нижче.

Тепер формула CHOOSE покаже числа вибраного місяця.

VLOOKUP з функцією CHOOSE

Ми бачили традицію VLOOKUP, але ми також можемо поєднати її з функцією CHOOSE. Наприклад, подивіться наведені нижче дані.

У клітинках від A1 до B11 у нас є таблиця продажів, а в комірці D2 я створив випадаючий список місяців і в комірці E2 нам потрібно застосувати VLOOKUP і отримати вибране значення продажів за місяць. Але проблема застосувати VLOOKUP - це структура даних, де значення пошуку знаходиться праворуч від стовпця результатів, тому VLOOKUP не може отримати дані справа наліво, у цьому випадку ми можемо використовувати функцію CHOOSE.

Для того щоб вибрати масив таблиці, нам тут потрібно відкрити функцію CHOOSE.

У аргументі INDEX Number у квітці дужки введіть 1 і 2. Тепер виберіть перший діапазон як стовпець "Місяць".

Виберіть стовпчик "Продажі" як другий діапазон.

Згадайте номер індексу стовпців як 2, а пошук діапазону - 0.

Тож ми отримали результат за місяць квітня зі столу. Отже, коли у вас немає точної структури даних, яку ми можемо вибрати, ОБРАЗУЙТЕ функцію.

ЯКЩО З функцією І & АБО

Логічні функції повні логіки для обчислення матеріалу. Погляньте на наведені нижче приклади даних.

З наведеної таблиці нам потрібно розрахувати бонус, виходячи з наведених нижче умов.

Якщо Сервісу більше 1, 5 років, а департаменту - продаж або підтримка, бонус - 1000.
Якщо нічого іншого, то бонус - 3000.

Для обчислення бонусу нам потрібно застосовувати IF, AND і OR разом. Відкрийте стан IF у комірці D2.

Нам потрібно перевірити декілька умов, відкрити функцію І.

Тепер наступним логічним є або продаж, або підтримка, для цього відкритого АБО умови.

Перевірте, чи назва "Продажі чи підтримка".

Отже, якщо всі надані умови ПРАВИЛЬНІ, то бонус повинен становити 10000.

Якщо логічні тести, що надаються, ЛІЖНІ, то бонус становить 3000.

Закрийте дужку і натисніть клавішу Enter, ми повинні отримати результат.

Скопіюйте та вставте формулу в комірки нижче.

Тож відповідно до умови ми отримали результат. Якщо послуга становить> 1, 5 року, а назва - Продажі чи підтримка, ми отримаємо 10000, або ж отримаємо 3000.

Складна формула з символами підстановки

Для отримання даних VLOOKUP потрібне точне значення пошуку. Це традиційний слоган, але ми все одно можемо отримати дані, використовуючи значення пошуку частинок. Наприклад, якщо значення пошуку - "VIVO", а в головній таблиці, якщо це "VIVO Mobile", ми все одно можемо співставити, використовуючи символи підстановки. Зараз ми побачимо один із прикладів, нижче наведено приклади даних.

У стовпці A. Маємо таблицю пошуку в стовпці А. У стовпці C у нас є значення пошуку, ці значення пошуку точно не збігаються зі значеннями таблиці пошуку. Тож ми побачимо, як застосувати VLOOKUP за допомогою макіяжів.

Спочатку відкрийте функцію VLOOKUP у комірці D1.

Перший аргумент - значення пошуку . Однією з проблем зі значенням пошуку тут є те, що ми не маємо точного відповідності, тому нам потрібно додавати це значення пошуку зірочкою до і після значення пошуку.

Тут ми застосували два символи зірочки “*” & C2 & ”*”. Тут зірочка вказує, що все, що знаходиться між підстановкою, має відповідати і повертати відповідний результат.

Незважаючи на те, що у нас просто символ "Infosys" зірочка відповідав значенню в таблиці пошуку і повертав точний результат як "Infosys Ltd".

Аналогічно, у комірці D6 ми отримали значення помилки як # VALUE! тому що в таблиці пошуку немає слова "Mintra".

Складні формули в Excel - Приклади складних формул в Excel