Розширені формули Excel (Зміст)
- Вступ до розширених формул в Excel
- Приклади розширеної формули в Excel
Вступ до розширених формул в Excel
Як тільки ви досягнете проміжного рівня в excel, вам потрібно наполегливо працювати, щоб перейти до просунутого рівня. Щоб вийти на просунутий рівень, ви повинні знати про деякі часто використовувані розширені формули. У цій статті я висвітлю топ-10 вдосконалених формул, які повинні знати учні, які мають найкращі знання. Дотримуйтесь цієї статті, щоб дізнатися та вивчити.
Приклади розширеної формули в Excel
Давайте розберемося, як використовувати розширені формули в Excel з деякими прикладами.
Ви можете завантажити цей розширений шаблон формул Excel тут - Розширений шаблон Excel шаблонПриклад №1 - Часткова функція VLOOKUP
Напевно, ви стикалися з ситуацією, коли VLOOKUP видає помилку, навіть якщо у значенні пошуку було незначне невідповідність. Наприклад, якщо ви знайдете зарплату на ім’я Абхішек Сінга і якщо у вас є лише Абхішек, то VLOOKUP не зможе отримати ваші дані.
Однак, використовуючи символ зірочки на будь-якому кінці значення пошуку, ми можемо отримати дані для часткового значення пошуку.
У таблиці 1 у мене є повне ім’я та зарплата, але в таблиці 2 я маю лише часткові імена та мені потрібно знайти зарплату кожного працівника.
Крок 1: Відкрийте формулу VLOOKUP у комірці E3. Перед вибором значення пошуку поставте зірочку (*) з обох боків від значення пошуку.
Крок 2: Як завжди, ви можете виконати формулу VLOOKUP зараз, і ми отримаємо результати.
Після використання наведеної формули вихідний показник показаний нижче.
Така ж формула використовується і в інших клітинах.
Приклад №2 - COUNTIFS з символами оператора
Ви повинні використовувати функцію COUNTIF та IFS для підрахунку речей у списку. Ми також можемо розраховувати на основі символів оператора, таких як (>) менше (<) та знак рівності (=).
Тепер подивіться, наприклад, наведені нижче дані. Якщо ви хочете порахувати загальну кількість рахунків для регіону ПІВДЕНЬ після дати 10 січня 2018 року, як ви рахуєте?
Приклад №3 - ЯКЩО Стан із умовами І & АБО
Логічні функції - це частина нашої повсякденної діяльності. Вам потрібно освоїти їх, щоб перейти на наступний рівень. Якщо ви розраховуєте бонус на основі декількох умов, тоді вам потрібно вкласти гніздо AND або АБО з умовою IF, щоб виконати роботу.
Припустимо, що потрібно розраховувати суму бонусу для кожного відділу, виходячи з відділу та років служби, які вам потрібні. Виходячи з наведених нижче критеріїв, нам потрібно обчислити бонус.
Якщо сервіс перевищує 4 роки, а відділ або бонус за продаж або підтримку - 50000, або бонус - 25000.
Застосуйте нижче формулу, щоб отримати суму бонусу.
Після використання вищевказаної формули вихідний показник показаний нижче.
Та ж формула, що застосовується в комірці Е3-Е9.
Приклад №4 - Функція ТЕКСТУ, щоб зробити заголовка динамічним
Скажімо, ви ведете щоденну таблицю продажів, і вам доведеться оновлювати таблицю щодня. На початку таблиці ви маєте заголовок, в якому сказано: «Консолідовані дані про продажі від DD-MM-YYYY до DD-MM-YYYY». Як і коли оновлення таблиці потрібно змінити дату заголовка. Чи не засмучує це завдання робити те ж саме знову і знову? Ми можемо зробити цей заголовок динамічним за допомогою функції TETX, MIN та Max разом із символом об'єднаного оператора ampersand (&).
Приклад №5 - INDEX + MATCH + MAX для пошуку найвищої торгової особи
У вас є список торгових осіб та продажів, які вони здійснили проти свого імені. Як вам сказати, хто найкращий чи найвищий продавець у списку? Звичайно, у нас є кілька інших методик, щоб повідомити результат, але ця функція може повернути найвищого продавця з партії.
Застосуйте нижче функцію, щоб отримати найвище ім'я продавця.
Після використання вищевказаної формули вихідний показник показаний нижче.
Приклад №6 - Отримайте підрахунок унікальних значень зі списку
Якщо у вас багато повторюваних значень, і вам потрібно сказати, скільки унікальних значень існує в тому, як вам сказати? Ви можете дізнатись, видаливши повторюване значення, але це не динамічний спосіб визначення унікального значення.
За допомогою цієї функції масиву ми можемо визначити унікальні значення з партії.
Застосуйте формулу нижче, щоб отримати унікальний список значень.
Приклад №7 - Використовуйте іменований діапазон, щоб зробити спадне динамічне
Якщо ви часто працюєте зі спадним списком і оновлюєте спадний список, вам потрібно повернутися до діапазону списку джерел, щоб видалити або додати значення. Після цього вам потрібно повернутися до випадаючих комірок і знову оновити діапазон випадаючого списку.
Однак якщо ви можете створити названий діапазон для випадаючого списку, ви можете зробити спадний список динамічним та оновленим.
Створіть діапазон імен, як показано на малюнку нижче.
Тепер перейдіть до спадної комірки та відкрийте спадне діалогове вікно.
У джерелі натисніть клавішу F3, вона покаже всі визначені імена, виберіть назву випадаючого списку.
Гаразд, випадаючий список готовий, і він автоматично оновлюватиме значення, коли відбудеться зміна діапазону випадаючого списку.
Приклад № 8 - Позбавлення значень помилок за допомогою функції IFERROR
Я впевнений, що у вас виникли значення помилок під час роботи з розрахунками VLOOKUP, Division. Поводження з цими значеннями помилок - копітка задача. Але ми можемо позбутися цих значень помилок, використовуючи функцію IFERROR у формулі.
Після використання вищевказаної формули вихідний показник показаний нижче.
Та ж формула, що використовується в інших клітинах.
Приклад №9 - Використовуйте функцію PMT для створення власної діаграми EMI
Сьогодні варіант EMI - це не дивна річ для всіх нас. У перекладі ми можемо оцінити власну EMI-діаграму за допомогою функції PMT. Для створення власної діаграми виконайте наведені нижче дії.
Застосуйте нижче формулу в комірці B4, щоб отримати кількість EMI.
Приклад №10 - INDEX + MATCH як альтернатива функції VLOOKUP
Сподіваюся, вам відомо про обмеження функції VLOOKUP. Одне головне обмеження - VLOOKUP може отримувати дані зліва направо, а не справа наліво. Далеко не всі дані добре організовані та готові до використання. Часто стовпчик значень, який ми шукаємо, знаходиться зліва від значення пошуку, тому VLOOKUP не допомагає у цих випадках.
Комбінація функції INDEX + MATCH служить альтернативою функції VLOOKUP.
Після використання наведеної формули вихідний показник показаний нижче.
Така ж формула використовується і в інших клітинах.
Виходячи з ідентифікатора продукту, нам потрібно отримати значення продажів. У головній таблиці Ідентифікатор товару знаходиться в правій частині стовпця продажів. Тож VLOOKUP не може отримати дані. Використовуйте формулу нижче, щоб отримати дані.
Рекомендовані статті
Це посібник з розширеними формулами в Excel. Тут ми обговорюємо, як використовувати розширені формули в Excel разом із практичними прикладами та шаблоном Excel, який можна завантажити. Ви також можете ознайомитися з іншими запропонованими нами статтями -
- Як користуватися функцією Excel INDEX?
- Розширений Excel | Функція бази даних
- Приклади формули TRIM в Excel
- Керівництво по формулі Excel OFFSET