Excel - IFS Tutorial

In this video, you will learn about the Intelligent Search feature in Microsoft 365. The video covers the search tools in Microsoft 365, including searching on your computer, using Microsoft Search, finding content and people in SharePoint, searching your emails, and more.
This will help you save time and easily find documents, information, and people within the Microsoft 365 suite.

  • 1:05
  • 3546 views

Objectifs :

Understand the IFS function in Excel, its application in calculating bonuses based on years of service, and how it simplifies the process compared to nested IF statements.

Chapitres :

  1. Introduction to the IFS Function
    The IFS function in Excel is a powerful tool that allows users to evaluate multiple conditions and return a value corresponding to the first TRUE condition. This function simplifies the process of handling multiple criteria, making it easier to read and manage compared to traditional nested IF statements.
  2. Application of the IFS Function
    In this example, we will calculate the bonus that each employee receives based on their years of service. The IFS function allows us to set specific conditions for different ranges of years of experience, streamlining the calculation process.
  3. Setting Conditions for Bonuses
    To illustrate the use of the IFS function, we will define the following conditions for calculating bonuses: - If years of experience is less than or equal to 5, the bonus will be $100. - Additional conditions can be added for other ranges of years of service, allowing for a comprehensive bonus structure.
  4. Advantages of Using the IFS Function
    Using the IFS function offers several advantages: - **Readability**: The IFS function is much easier to read than multiple nested IF statements. - **Efficiency**: It allows testing of up to 127 different conditions in a single formula, making it a versatile choice for complex calculations.
  5. Conclusion
    The IFS function is an essential tool for anyone working with Excel, especially when dealing with multiple conditions. By simplifying the process of evaluating criteria, it enhances both the efficiency and clarity of your calculations, making it a preferred choice over traditional nested IF statements.

FAQ :

What is the IFS function used for?

The IFS function is used to evaluate multiple conditions in a spreadsheet and return a value for the first condition that is TRUE, making it easier to manage than nested IF statements.

How many conditions can the IFS function test?

The IFS function can test up to 127 different conditions, allowing for complex evaluations without the need for multiple nested IF statements.

Can I use the IFS function for calculating bonuses?

Yes, the IFS function can be effectively used to calculate bonuses based on various conditions, such as years of service.

What are the advantages of using the IFS function over nested IF statements?

The IFS function is easier to read and manage compared to nested IF statements, reducing complexity and improving clarity in formulas.

What happens if none of the conditions in the IFS function are met?

If none of the conditions are met, the IFS function will return an error. To handle this, you can include a final condition that acts as a default value.

Quelques cas d'usages :

Calculating Employee Bonuses

A human resources manager can use the IFS function to calculate bonuses for employees based on their years of service. For example, if an employee has 5 years or less, they receive a $100 bonus; for 6 to 10 years, they might receive $200, and so on. This method simplifies the calculation process and makes it easy to adjust bonus amounts as needed.

Performance Evaluation

A team leader can apply the IFS function to evaluate employee performance ratings. By setting conditions for different performance levels, the leader can automatically assign ratings or rewards based on the criteria defined, streamlining the evaluation process.

Sales Commission Calculation

A sales manager can utilize the IFS function to calculate commissions based on sales targets achieved. For instance, if a salesperson meets their target, they receive a 10% commission; if they exceed it by 20%, they receive 15%. This allows for quick adjustments to commission structures without complex formulas.

Glossaire :

IFS function

A function in spreadsheet software that checks multiple conditions and returns a value corresponding to the first TRUE condition. It simplifies the process of using multiple nested IF statements.

bonus

An additional payment or reward given to an employee, often based on performance or length of service.

years of service

The total number of years an employee has worked for a particular organization.

nested IF instructions

A method of using multiple IF statements within one another to evaluate several conditions in a spreadsheet. This can become complex and difficult to read.

TRUE condition

A condition in a logical test that evaluates to true, allowing the corresponding value to be returned.

résultat(s)
00:00:14
In our example, we will calculate the bonus that each person will receive based on their years of service.
00:00:20
We could have nested several IF instructions, which would have given this
00:00:24
Let's try with the IFS function
00:00:31
If the years of experience is under or equal to 5....
00:00:39
The bonus will be €100....
00:00:46
And so on...
00:00:54
Using this function is like using several IF nested instructions but is much easier to read and enables you to test up to 127 different conditions.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
Die IFS-Funktion prüft, ob eine oder mehrere Bedingungen erfüllt sind, und gibt einen Wert zurück, der der ersten TRUE-Bedingung entspricht.
00:00:14
In unserem Beispiel berechnen wir den Bonus, den jede Person auf der Grundlage ihrer Dienstjahre erhält.
00:00:20
Wir hätten mehrere IF-Anweisungen verschachteln können, die diese
00:00:24
Probieren wir es mit der IFS-Funktion
00:00:31
Wenn die Jahre der Erfahrung unter oder gleich 5....
00:00:39
Der Bonus beträgt €100....
00:00:46
Und so weiter...
00:00:54
Die Verwendung dieser Funktion ist wie die Verwendung mehrerer IF-Verschachtelter Anweisungen, ist aber viel einfacher zu lesen und ermöglicht es Ihnen, bis zu 127 verschiedene Bedingungen zu testen.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
La función IFS comprueba si se cumplen una o varias condiciones y devuelve un valor correspondiente a la primera condición TRUE.
00:00:14
En nuestro ejemplo, calcularemos el bono que cada persona recibirá en función de sus años de servicio.
00:00:20
Podríamos haber anidado varias instrucciones if, que habrían dado este
00:00:24
Vamos a probar con la función IFS
00:00:31
Si los años de experiencia son inferiores o iguales a 5....
00:00:39
El bono será de 100 libras esterlinas...
00:00:46
Y así sucesivamente...
00:00:54
El uso de esta función es como usar varias instrucciones anidadas IF, pero es mucho más fácil de leer y le permite probar hasta 127 condiciones diferentes.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
A função IFS verifica se uma ou mais condições são atendidas e retorna um valor correspondente à primeira condição TRUE.
00:00:14
Em nosso exemplo, calcularemos o bônus que cada pessoa receberá com base em seus anos de serviço.
00:00:20
Poderíamos ter aninhado várias instruções IF, o que teria dado este
00:00:24
Vamos tentar com a função IFS
00:00:31
Se os anos de experiência é inferior ou igual a 5....
00:00:39
O bônus será $100....
00:00:46
E assim por diante...
00:00:54
Usar essa função é como usar várias instruções aninhadas IF, mas é muito mais fácil de ler e permite que você teste até 127 condições diferentes.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
De IFS-functie controleert of aan een of meer voorwaarden wordt voldaan en retourneert een waarde die overeenkomt met de eerste werkelijke voorwaarde.
00:00:14
In ons voorbeeld berekenen we de bonus die elke persoon ontvangt op basis van zijn dienstjaren.
00:00:20
We hadden verschillende IF-instructies kunnen genest, wat dit zou hebben gegeven
00:00:24
Laten we proberen met de IFS-functie
00:00:31
Als de jarenlange ervaring is onder of gelijk aan 5....
00:00:39
De bonus is €100....
00:00:46
En zo op...
00:00:54
Het gebruik van deze functie is als het gebruik van verschillende als geneste instructies, maar is veel gemakkelijker te lezen en stelt u in staat om te testen tot 127 verschillende omstandigheden.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
La funzione IFS controlla se una o più condizioni sono soddisfatte e restituisce un valore corrispondente alla prima condizione TRUE.
00:00:14
Nel nostro esempio, calcoleremo il bonus che ogni persona riceverà in base ai loro anni di servizio.
00:00:20
Avremmo potuto annidare diverse istruzioni IF, che avrebbero dato questo
00:00:24
Proviamo con la funzione IFS
00:00:31
Se gli anni di esperienza è inferiore o uguale a 5....
00:00:39
Il bonus sarà di 100 dollari....
00:00:46
E così via...
00:00:54
L'utilizzo di questa funzione è simile all'utilizzo di diverse istruzioni annidate IF, ma è molto più semplice da leggere e consente di testare fino a 127 condizioni diverse.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
IFS 函數檢查是否滿足一個或多個條件，並返回對應于第一個 TRUE 條件的值。
00:00:14
在我們的示例中，我們將根據每個人的服務年數計算每個人將獲得的獎金。
00:00:20
我們可以嵌套幾個IF指令，這將給予這個
00:00:24
讓我們嘗試使用 IFS 函數
00:00:31
如果經驗年數低於或等於 5....
00:00:39
獎金為100英鎊。
00:00:46
等等...
00:00:54
使用此函數就像使用多個 IF 嵌套指令，但更易於閱讀，使您能夠測試多達 127 個不同的條件。

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
IFS 関数は、1 つ以上の条件が満たされているかどうかをチェックし、最初の TRUE 条件に対応する値を返します。
00:00:14
この例では、各人が勤務年数に基づいて受け取るボーナスを計算します。
00:00:20
いくつかの IF 命令をネストすることができました。
00:00:24
IFS 関数を使ってみましょう
00:00:31
経験の年が5以下の場合.
00:00:39
ボーナスは€100になります。
00:00:46
などなど。。。
00:00:54
この関数を使用すると、いくつかの IF 入れ子になった命令を使用するようなものですが、読みやすく、最大 127 の異なる条件をテストできます。

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
Chức năng IFS kiểm tra xem một hoặc nhiều điều kiện được đáp ứng và trả về một giá trị tương ứng với tình trạng TRUE đầu tiên.
00:00:14
Trong ví dụ của chúng tôi, chúng tôi sẽ tính toán tiền thưởng mà mỗi người sẽ nhận được dựa trên những năm phục vụ của họ.
00:00:20
Chúng tôi có thể có lồng nhau một số hướng dẫn, trong đó sẽ có được điều này
00:00:24
Hãy thử với các chức năng IFS
00:00:31
Nếu những năm kinh nghiệm là theo hoặc bằng 5....
00:00:39
Tiền thưởng sẽ được €100....
00:00:46
Và như vậy...
00:00:54
Sử dụng chức năng này giống như sử dụng một số hướng dẫn nếu lồng nhau, nhưng dễ dàng hơn nhiều để đọc và cho phép bạn kiểm tra lên đến 127 điều kiện khác nhau.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
Fungsi IFS memeriksa apakah satu atau lebih kondisi terpenuhi dan mengembalikan nilai yang sesuai dengan kondisi TRUE pertama.
00:00:14
Dalam contoh kita, kita akan menghitung bonus yang setiap orang akan menerima berdasarkan tahun layanan mereka.
00:00:20
Kita bisa bersarang beberapa instruksi IF, yang akan memberikan ini
00:00:24
Mari kita coba dengan fungsi IFS
00:00:31
Jika tahun pengalaman berada di bawah atau sama dengan 5....
00:00:39
Bonus akan €100....
00:00:46
Dan seterusnya...
00:00:54
Menggunakan fungsi ini adalah seperti menggunakan beberapa instruksi jika bersarang tetapi jauh lebih mudah untuk membaca dan memungkinkan Anda untuk menguji hingga 127 kondisi yang berbeda.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
Функция IFS проверяет, выполнено ли одно или несколько условий, и возвращает значение, соответствующее первому условию TRUE.
00:00:14
В нашем примере мы рассчитаем бонус, который каждый человек получит на основе своего года службы.
00:00:20
Мы могли бы вложены несколько инструкций ЕСЛИ, которые дали бы это
00:00:24
Давайте попробуем с функцией IFS
00:00:31
Если лет опыта находится под или равна 5....
00:00:39
Бонус будет 100 евро....
00:00:46
И так далее...
00:00:54
Использование этой функции, как с помощью нескольких IF вложенных инструкций, но гораздо легче читать и позволяет проверить до 127 различных условиях.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
Функцията IFS проверява дали са изпълнени едно или повече условия и връща стойност, отговаряща на първото условие TRUE.
00:00:14
В нашия пример ще изчислим бонуса, който всеки човек ще получи въз основа на годините на обслужване.
00:00:20
Можехме да гнездим няколко ИФ инструкции, които биха дали
00:00:24
Нека да опитаме с функцията IFS
00:00:31
Ако годините на стаж са под или са равни на 5...
00:00:39
Бонусът е 100 евро....
00:00:46
И така нататък...
00:00:54
Използването на тази функция е като използването на няколко IF вложени инструкции, но е много по-лесно да се чете и ви позволява да тествате до 127 различни условия.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
Funcția IFS verifică dacă una sau mai multe condiții sunt îndeplinite și returnează o valoare corespunzătoare primei condiții TRUE.
00:00:14
În exemplul nostru, vom calcula bonusul pe care fiecare persoană îl va primi pe baza anilor de serviciu.
00:00:20
Am fi putut imbrica mai multe instrucțiuni IF, care ar fi dat acest
00:00:24
Să încercăm cu funcția IFS
00:00:31
În cazul în care anii de experiență este sub sau egal cu 5 ....
00:00:39
Bonusul va fi de € 100....
00:00:46
Și așa mai departe...
00:00:54
Utilizarea acestei funcții este ca folosind mai multe instrucțiuni IF imbricate, dar este mult mai ușor de citit și vă permite să testați până la 127 de condiții diferite.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
Функція IFS перевіряє, чи виконуються одна або кілька умов і повертає значення, яке відповідає першій умові TRUE.
00:00:14
У нашому прикладі ми розрахумо бонус, який кожна людина отримає на основі своїх років служби.
00:00:20
Ми могли б вкладені кілька інструкцій IF, які б дали це
00:00:24
Спробуємо з функцією IFS
00:00:31
Якщо багаторічний досвід перебуває під або дорівнює 5 ....
00:00:39
Бонус буде € 100 ....
00:00:46
І так далі ...
00:00:54
Використання цієї функції схоже на використання декількох вкладених інструкцій IF, але набагато простіше читати і дозволяє перевірити до 127 різних умов.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
Funkcja IFS sprawdza, czy spełnione jest co najmniej jedno warunki, i zwraca wartość odpowiadającą pierwszemu warunkowi PRAWDA.
00:00:14
W naszym przykładzie obliczymy bonus, który każda osoba otrzyma na podstawie swoich lat pracy.
00:00:20
Moglibyśmy zagnieździć kilka instrukcji IF, które dałyby
00:00:24
Spróbujmy z funkcją IFS
00:00:31
Jeśli lata doświadczenia są poniżej lub równe 5....
00:00:39
Bonus wyniesie 100 €....
00:00:46
I tak dalej...
00:00:54
Korzystanie z tej funkcji jest jak przy użyciu kilku instrukcji zagnieżdżonych IF, ale jest znacznie łatwiejsze do odczytania i umożliwia przetestowanie do 127 różnych warunków.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
IFS işlevi bir veya daha fazla koşulun karşılanıp karşılanmadığını denetler ve ilk TRUE koşuluna karşılık gelen bir değeri döndürür.
00:00:14
Örneğimizde, her bir kişinin alacağı bonusu, hizmet yıllarına göre hesaplayacağız.
00:00:20
Birkaç IF talimatları iç içe olabilirdik, ki bu da bunu verirdi.
00:00:24
IFS fonksiyonu ile deneyelim
00:00:31
Deneyim yıl altında veya 5 eşit ise ....
00:00:39
Bonus 100 € olacaktır ....
00:00:46
Ve böylece ...
00:00:54
Bu işlevi kullanarak birkaç IF iç içe yönergesi kullanarak gibi ama okumak çok daha kolaydır ve 127 farklı koşullara kadar test etmenizi sağlar.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
A função IFS verifica se uma ou mais condições estão satisfeitas e devolve um valor correspondente à primeira condição TRUE.
00:00:14
No nosso exemplo, calcularemos o bónus que cada pessoa receberá com base nos seus anos de serviço.
00:00:20
Poderíamos ter aninhado várias instruções IF, que teria dado este
00:00:24
Vamos tentar com a função IFS
00:00:31
Se os anos de experiência forem inferiores ou iguais a 5......
00:00:39
O bónus será de €100....
00:00:46
E assim por diante...
00:00:54
Utilizar esta função é como utilizar várias instruções if aninhadas, mas é muito mais fácil de ler e permite testar até 127 condições diferentes.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
Funkcija IFS proverava da li su ispunjeni jedan ili više uslova i daje vrednost koja odgovara prvom TRUE uslovom.
00:00:14
U našem primeru, izračunaćemo bonus koji će svaka osoba dobiti na osnovu godina svog višegodišnjeg rada.
00:00:20
Mogli smo da ugnezdimo nekoliko instrukcija IF, koje bi dale ovo
00:00:24
Hajde da pokušamo sa IFS funkcijom
00:00:31
Ako su godine iskustva ispod ili jednake 5....
00:00:39
Bonus će biti 100 €...
00:00:46
I tako dalje...
00:00:54
Korišćenje ove funkcije je kao korišćenje nekoliko ugnežđenih IF uputstava, ali je mnogo lakše za čitanje i omogućava vam da testirate do 127 različitih uslova.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:14
في مثالنا، سوف نقوم بحساب المكافأة التي سيحصل عليها كل شخص بناء على سنوات خدمته.
00:00:20
كان يمكن أن نعشش عدة تعليمات IF، والتي من شأنها أن تعطي هذا
00:00:24
دعونا نحاول مع وظيفة IFS
00:00:31
إذا كانت سنوات الخبرة أقل من 5 أو مساوية لها.
00:00:39
المكافأة ستكون 100 يورو....
00:00:46
وهكذا...
00:00:54
استخدام هذه الوظيفة هو مثل استخدام العديد من التعليمات المتداخلة IF ولكن من الأسهل بكثير لقراءة وتمكنك من اختبار ما يصل إلى 127 شروط مختلفة.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:14
우리의 예에서, 우리는 각 사람이 봉사의 년을 기반으로받을 보너스를 계산합니다.
00:00:20
우리는 이것을 주었을 몇 가지 IF 지침을 중첩 할 수 있습니다.
00:00:24
IFS 기능을 사용해 보겠습니다.
00:00:31
경험의 년이 아래 또는 5와 같은 경우 ....
00:00:39
보너스는 €100입니다....
00:00:46
그리고 등등 ...
00:00:54
이 함수를 사용하는 것은 여러 IF 중첩 지침을 사용하는 것과 유사하지만 읽기가 훨씬 쉬우며 최대 127개의 조건을 테스트할 수 있습니다.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:14
在我们的示例中，我们将根据每个人的服务年限计算他们将获得的奖金。
00:00:20
我们可以嵌套几个IF指令，这将给出这个
00:00:24
让我们尝试使用 IFS 函数
00:00:31
如果经验年限低于或等于5....
00:00:39
奖金将为100欧元。
00:00:46
等等...
00:00:54
使用此功能就像使用多个IF嵌套指令一样，但更易于阅读，并使您能够测试多达127种不同的条件。

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:14
ในตัวอย่างของเราเราจะคํานวณโบนัสที่แต่ละคนจะได้รับตามปีที่ให้บริการ
00:00:20
เราอาจทําแบบ IF คําแนะนําหลายอัน, ซึ่งน่าจะให้นี่
00:00:24
ลองกับฟังก์ชัน IFS
00:00:31
หากปีของประสบการณ์ต่ํากว่าหรือเท่ากับ 5....
00:00:39
โบนัสจะอยู่ที่€ 100....
00:00:46
และอื่น ๆ ...
00:00:54
การใช้ฟังก์ชั่นนี้เหมือนกับการใช้คําแนะนําที่ซ้อนกันของ IF หลายตัว แต่อ่านง่ายกว่ามากและช่วยให้คุณสามารถทดสอบเงื่อนไขที่แตกต่างกันได้ถึง 127 ข้อ

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:05
Az IFS függvény ellenőrzi, hogy egy vagy több feltétel teljesült-e, és az első TRUE feltételnek megfelelő értéket ad vissza.
00:00:14
Példánkban kiszámítjuk azt a bónuszt, amelyet minden személy szolgálati évei alapján kap.
00:00:20
Több IF utasítást is beágyazhattunk volna, ami ezt megadta volna
00:00:24
Próbáljuk meg az IFS funkcióval
00:00:31
Ha az évek tapasztalata alatti vagy egyenlő 5...
00:00:39
A bónusz 100 euró lesz...
00:00:46
És így tovább...
00:00:54
Ennek a funkciónak a használata olyan, mintha több IF beágyazott utasítást használ, de sokkal könnyebben olvasható, és lehetővé teszi akár 127 különböző körülmény tesztelését.

No elements match your search in this video....
Do another search or back to content !

 

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2325 Views

Video Training Microsoft Excel - Advanced level

With our "advanced" tutorials, discover and master the full potential of Excel, using complex functions, macros and the power of DCTs and Po...

ALL COURSES

An Error Has Occurred

Unable to save your progress. Please reload the page, log in, and try again. If the problem persists, please contact an administrator.

Close

Reminder

Show

/

Join Live Training Session