Обробка декількох умов у формулі IF. У цій статті порівнюються три різні методи.
Коли вам потрібно зробити умовний розрахунок, функцією IF є відповідь. Якщо тоді інакше. На наступному малюнку простий ПФ розраховує бонус, якщо ваші продажі становили 20 000 доларів США або більше.
Але що відбувається, коли потрібно виконати дві умови? Більшість людей вкладуть одне твердження IF у інше, наприклад:
Але це виходить з-під контролю, якщо у вас є багато умов, які потрібно виконати. Функція AND скоротить і спростить формулу. = AND (Test, Test, Test, Test) буде True, лише якщо всі логічні тести є True. Наступний приклад показує коротшу формулу з однаковими результатами.
Якщо вам подобається І, ви можете знайти застосування АБО і НІ. = АБО (Test, Test, Test, Test) буде True, якщо будь-який з логічних тестів є True. NOT поверне відповідь назад. =NOT(True)є Помилковим. =NOT(False)правда. Якщо вам коли-небудь доведеться зробити щось химерне, як NAND, ви можете НЕ (І (Тест, Тест, Тест, Тест)).
Обережно
Незважаючи на те, що Excel 2013 представив XOR як ексклюзивний варіант, він працює не так, як очікували б бухгалтери. =XOR(True,False,True,True)це правда з причин, які тут занадто складні для пояснення. XOR дійсно враховує, чи є у вас непарна кількість справжніх значень. Непарні Дійсно дивно.
Бонусна порада
Використання логічної логіки
Я завжди висвітлюю ІФ на своїх семінарах. І я завжди запитую, як люди можуть вирішити проблему двох умов. Результати часто однакові; 70-80% людей використовують вкладені IF, а 20-30% використовують AND. Одного разу у Вірджинії жінка з Price Waterhouse запропонувала таку формулу:
Це працює. Це дає таку ж відповідь, як і інші формули. Обчисліть бонус .02 * B4. Але потім помножте цей бонус на логічні тести в дужках. Коли ви змушуєте Excel помножувати число на True або False, True стає 1, а False стає 0. Будь-яке число, помножене на 1, є самим собою. Будь-яке число, помножене на 0, дорівнює 0. Помноження бонусу на умови гарантує, що виплачуються лише ті рядки, які відповідають обом умовам.
Це круто. Це працює. Але це здається заплутаним, коли ви це вперше бачите. Мій жарт на семінарі завжди звучить так: «Якщо ви залишите роботу наступного місяця і ненавидите своїх колег, почніть використовувати цю формулу».
Переглянути відео
- Найпростішою функцією IF є
=IF(Logical Test,Formula if True, Formula if False) - Але що робити, якщо вам доведеться перевірити дві умови?
- Багато людей це зроблять
=IF(Test 1, IF(Test 2, Formula if True, False), False) - Це стає громіздким, якщо 3, 5, 17 умови!
- Натомість використовуйте
=IF(AND(t1,t2,t3,t4),Formula if True, Formula if False) - Якщо вам подобається І, розгляньте АБО, НЕ для інших ситуацій
- NAND можна зробити за допомогою NOT (AND ())
- NOR можна зробити за допомогою NOT (АБО ())
- Будьте обережні, використовуючи XOR, оскільки результати не такі, як ви очікуєте
Стенограма відео
Дізнайтеся Excel з подкасту, серія 2025 - Кілька умов у випадку, якщо IF!
Я буду підкастувати всю цю книгу, “i” у верхньому правому куті приведе вас до списку відтворення для всіх цих подкастів!
Добре, ми почнемо з найпростішого у світі випадку ІФ, фіктивний віце-президент з продажів приходить до нас і каже: "Гей, цього місяця кожен, хто має продажі понад 20000 доларів, отримує 2% бонус". Гаразд, отже, функція IF складається з трьох частин: логічний тест, який має сказати “Чи є B4> 20000 доларів?” Кома, тоді що робити, якщо це ІСТИНА? Якщо це TRUE, .02 * B4, кома, що робити, якщо це FALSE? Ну, якщо ви не заробили 20000 доларів, жоден бонус для вас не дорівнює нулю, добре. Ctrl + Enter, щоб скопіювати це, і ви бачите, що ми маємо бонус лише за рядки, які перевищували 20000 доларів, цей закритий, але все одно бонусу немає, добре. Зараз ніколи не існувало такого простого бонусного плану, правда, завжди існує безліч правил, тому тут ми повинні перевірити, чи дохід> 20000 доларів І чи валовий відсоток прибутку> 50%.
Добре, і якщо ти знаєш, як це вирішити, подумай, як ти це вирішиш, добре, і я збираюся передбачити, що багато хто з вас каже: "Ну, ми почнемо з одного Оператор IF, а потім пізніше мати ще одне твердження IF, подібне до цього, вкладені IF. " І тоді є кілька з вас, які говорять: "Ну, давайте зробимо твердження IF, а потім відразу тут, у дужках, ми переходимо до іншої функції під назвою AND." І тоді, є спосіб зробити це взагалі без ПІ. Гаразд, я подивлюсь на них через ці, отже, ось перший, найпоширеніший спосіб, вкладений ІФ, Добре, ось як би це робили більшість людей.
= IF, зробіть перший тест, подивіться, чи дохід у B4> $ 20000, якщо це тоді зробіть інший IF, подивіться, чи відсоток валового прибутку> .05? Якщо це TRUE, то обидві умови TRUE, ми можемо зробити .02 * B4, інакше бонус не буде. Добре, але ми не закінчили, закриваємо внутрішні дужки, коми, а потім, якщо перший тест не відповідав дійсності, бонус не буде, Ctrl + Enter для копіювання. Ви бачите, що бонус отримують лише ті рядки, які перевищують 20000 доларів США і перевищують валовий відсоток 50%. Гаразд, ще в той час, колись існувала ця жахлива межа, коли ви не могли вкласти більше 7 тверджень IF. Це був болісний день, особливо якщо ви просто повільно додавали умови протягом місяців, і у вас нарешті з’явився такий, який мав 7, і вам потрібно було додати 8-й. Добре, ну сьогодні ти можеш піти на 32, я не думаю, що ти повинен коли-небудь йти на 32,але якщо вам просто відчайдушно потрібно перейти з 7 на 8, то це добре, добре. Отже, це підхід до вкладених тверджень IF, коли я проводжу свої семінари в прямому ефірі, це робить приблизно половина залу, але є набагато кращий спосіб.
= IF, а потім негайно переходимо до функції, що називається AND, тому всередині AND ми ставимо всі тести: чи дохід> 20000, кома, це відсоток валового прибутку> .5. Якщо тестів було більше, продовжуйте ставити коми з додатковими тестами, а потім закривайте кінець, все в підсумку має бути TRUE, щоб кінець був TRUE. Тож якщо ми дійдемо до цієї точки, якщо кінець TRUE, .02 * дохід, інакше 0, це коротша формула, легше вводити, ви отримуєте однакові результати, життя чудове.
Добре, з усіх семінарів, які я проводив за останні 15 років, лише один раз хтось зайшов і вдарив мене цією божевільною формулою. Вона сказала: "Ось, ми просто зробимо = .02 * дохід, як такий". Добре, розрахуй бонус, я начебто “Ого, тримайся, це буде дорого, ти даси бонус усім”. вона як "Зачекайте, я ще не закінчив, разів, а потім у дужках ми поставимо кожну умову, тобто дохід> 20000 разів, у дужках відсоток валового прибутку> .5". Добре, і ось що трапляється, ми обчислюємо бонус, а потім оцінюємо або TRUE, або FALSE. І коли ми змушуємо Excel помножувати TRUE або FALSE на число, TRUE стає 1, будь-що * 1 є самим собою, FALSE стає 0! Гаразд, отже, ми маємо тут 2% * дохід * 1 * 0, що завгодно * 0 = 0, так,так що це очищає бонус. Це логічна логіка, TRUE * TRUE, 1 * 1 = 1, якщо вони або FALSE, або всі вони FALSE, це буде оцінено як 0, і ми отримаємо точно такий же результат. Чи думаю, що вам слід перейти на цей? Ні, це бентежить, якщо ви не залишите роботу наступного тижня і не зненавидите своїх колег, тоді сміливо переходьте до цього, добре.
Якщо вам подобається функція І, є інші функції або перевірки, чи є якась із умов ІСТИННОЮ, тому ця чи ця чи ця, вона поверне ІСТИННУ. NOT не збирається змінити значення TRUE на FALSE і FALSE на TRUE, що корисно, коли ви намагаєтесь виконати логічні концепції NAND або NOR. NAND означає не-і, це ІСТИНА, коли принаймні одна умова - FALSE, добре. Отже, якщо жоден з них ІСТИНА, це чудово, якщо деякі з них ІСТИНА, це чудово, але як тільки всі вони ІСТИНА, тоді ми не платимо. NOR означає «не-або», це означає, що жодна з умов не відповідає ІСТИНІ - якщо це трапляється, або це трапляється, або це трапляється, для вас немає бонусу. А потім XOR, будьте обережні з цим, він був введений в Excel 2013, і він не робить того, що ми, як бухгалтери, вважаємо, що слід робити.Ексклюзивно - або означає, що лише один із тестів ІСТИННИЙ, і він працює, коли є дві умови. Але для інженерів-електриків вони роблять це в парі, тому це не дає результатів, які ви можете подумати.
Гаразд, отже, ось тест 1, тест 2, тест 3, тест 4, три з них ІСТИННІ, а XOR каже: "Це саме одна з цих ІСТИН?" І коли ми робимо цей XOR, він говорить: "Так, саме один із тих, що ІСТИННИЙ." і це тому, що функція Excel дублює роботу дуже поширеного чіпа, який використовується в електротехніці, я знаю, це шокує, так? Ви думаєте, що Excel призначений лише для бухгалтерів, але інженери також використовують Excel, і вони, очевидно, додали XOR для них, а не для бухгалтерів. Тож, як це оцінюється, коли вони дивляться на перші два, “чи є один із цих 2 ІСТИННИМ? Так!" Гаразд, ми отримуємо цю ІСТИНУ, а потім вони беруть відповідь із XOR цього та порівнюють із ІСТИНОЮ: “Це одна з цих 2 ІСТИНИ? Ні, 2 з них ІСТИНА, тож це стає НЕВИЩИМ! " Потім вони приймають цю відповідь і XOR з останньою,отже, вони роблять це парами, так? “Це одна з цих 2 ПРАВДА? Так!" Гаразд, так ми це отримуємо. Виявляється, те, що він робить насправді, полягає в підрахунку електротехніки, якщо непарна кількість входів ІСТИНА. Не обов'язково корисно для бухгалтерів, які очікують, що він зробить те, що означає XOR англійською мовою.
Гаразд, у цій книзі багато чудових порад, корисних речей і навіть цієї жахливої дискусії про NAND та XOR та подібні речі. Купуйте книгу, і ви отримаєте всі ці поради як на долоні. Підсумок за сьогодні: найпростіша функція IF, = логічний тест IF, що робити, якщо це TRUE, що робити, якщо це FALSE, але якщо у вас є 2 умови, багато людей вкладають оператори IF, але тільки уявіть, якщо у вас було 3 , 5 або 17 умов для гніздування. І вирішить це, трохи скоротіть, тому, якщо вам подобається І, є також АБО чи НІ, ви можете зробити NAND, ви можете зробити НІ, але будьте обережні, використовуючи цей новий Excel 2013 XOR, результати можуть бути не такими, як ви очікувати.
Гаразд, привіт, я хочу подякувати вам за заїзд, ми побачимось наступного разу для чергової трансляції від!
Завантажити файл
Завантажте зразок файлу тут: Podcast2025.xlsx
