Excel - SWITCH Tutorial

In this video, you will learn about the Office 365 tools that can help you search and find what you need.
The video covers OneDrive, Yammer, and Delve, and demonstrates how to use these tools to access and share documents, collaborate with colleagues, and discover relevant information.
This will help you stay updated on the latest updates, reports, and modifications made by your coworkers, even when working remotely.
By using these tools effectively, you can enhance your productivity and easily find the content you need.

1:03
3024 views

Available in Microsoft365

Objectifs :

Understand the SWITCH function in Excel, its usage for evaluating expressions against a list of values, and how to determine the day of the week from a given date.

Chapitres :

Introduction to the SWITCH Function
The SWITCH function in Excel is a powerful tool that allows users to evaluate an expression against a list of values. It returns the result corresponding to the first matching value found in the list. This function is particularly useful for simplifying complex nested IF statements.
Using SWITCH to Determine the Day of the Week
In this example, we will explore how to use the SWITCH function to find the day of the week corresponding to a specific date. The expression we will evaluate is the day of the week, with a return type of 2, indicating that Monday is considered the first day of the week.
Example of the SWITCH Function
To illustrate, if the value evaluated is 1, the result returned will be 'Monday'. The SWITCH function can evaluate up to 126 values, making it a versatile option for various applications. Here’s how the function works: - If the evaluated value is 1, return 'Monday'. - If the evaluated value is 2, return 'Tuesday'. - Continue this pattern through to 7, which would return 'Sunday'.
Benefits of Using the SWITCH Function
The SWITCH function simplifies the process of evaluating multiple conditions. Instead of using multiple IF statements, which can become cumbersome, SWITCH provides a cleaner and more readable approach. This not only enhances clarity but also reduces the likelihood of errors in complex formulas.
Conclusion
In summary, the SWITCH function is an efficient way to evaluate expressions against a list of values in Excel. By using this function to determine the day of the week from a date, users can streamline their calculations and improve the readability of their spreadsheets. Understanding how to implement the SWITCH function can significantly enhance your data analysis capabilities.

FAQ :

What is the SWITCH function used for?

The SWITCH function is used to evaluate an expression against a list of values and return the result corresponding to the first matching value.

How many values can the SWITCH function evaluate?

The SWITCH function can evaluate up to 126 values and corresponding results.

What does the return type of the SWITCH function indicate?

The return type indicates the type of value that the SWITCH function will return based on the evaluated expression.

Can the SWITCH function be used to determine the day of the week?

Yes, the SWITCH function can be used to determine the day of the week corresponding to a specific date by evaluating the date against predefined values.

What happens if there is no matching value in the SWITCH function?

If there is no matching value, the SWITCH function will return an error or a default value if specified.

Quelques cas d'usages :

Determining the Day of the Week

Using the SWITCH function to evaluate a date and return the corresponding day of the week, which can be useful in scheduling applications or calendar management.

Conditional Formatting in Reports

Applying the SWITCH function in data analysis tools to categorize data based on specific criteria, enhancing the readability of reports.

Dynamic User Interfaces

Implementing the SWITCH function in programming to create dynamic user interfaces that change based on user input, improving user experience.

Automating Data Entry

Utilizing the SWITCH function in spreadsheets to automate data entry processes by categorizing inputs based on predefined values, increasing efficiency.

Error Handling in Data Processing

Using the SWITCH function to manage and handle errors in data processing by providing alternative outputs for unexpected values.

Glossaire :

SWITCH function

A function that evaluates an expression against a list of values and returns the result corresponding to the first matching value.

expression

A combination of values, variables, operators, and functions that are evaluated to produce a result.

return type

The type of value that a function will return after execution, which can vary based on the function's design.

day of the week

A classification of days based on a seven-day cycle, typically including Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, and Sunday.

value

A specific piece of data that can be evaluated or compared in a function.

00:00:15

In our example, we would like to know the day of the week corresponding to the date.

00:00:21

With the SWITCH function…

00:00:28

The expression will be as follows:

00:00:31

the day of the week,

00:00:35

corresponding to a date,

00:00:39

with return type "2" corresponding to Monday as the first day of the week.

00:00:46

If the value is "1"

00:00:48

The result will be "Monday"

00:00:51

And so on...

00:00:57

This function enables you to evaluate up to 126 values and corresponding results.

00:00:05

Die SWITCH-Funktion wertet einen Ausdruck anhand einer Werteliste aus und gibt das Ergebnis zurück, das dem ersten übereinstimmenden Wert entspricht.

00:00:15

In unserem Beispiel möchten wir den Wochentag kennen, der dem Datum entspricht.

00:00:21

Mit der SWITCH-Funktion...

00:00:28

Der Ausdruck lautet wie folgt:

00:00:31

am Wochentag,

00:00:35

entspricht einem Datum,

00:00:39

mit Rücksendeart "2" entsprechend Montag als erstem Wochentag.

00:00:46

Wenn der Wert "1" ist

00:00:48

Das Ergebnis wird "Montag" sein

00:00:51

Und so weiter...

00:00:57

Mit dieser Funktion können Sie bis zu 126 Werte und entsprechende Ergebnisse auswerten.

00:00:05

La función SWITCH evalúa una expresión con una lista de valores y devuelve el resultado correspondiente al primer valor coincidente.

00:00:15

En nuestro ejemplo, nos gustaría saber el día de la semana correspondiente a la fecha.

00:00:21

Con la función SWITCH...

00:00:28

La expresión será la siguiente:

00:00:31

el día de la semana,

00:00:35

correspondiente a una fecha,

00:00:39

con el tipo de devolución "2" correspondiente al lunes como el primer día de la semana.

00:00:46

Si el valor es "1"

00:00:48

El resultado será "Lunes"

00:00:51

Y así sucesivamente...

00:00:57

Esta función le permite evaluar hasta 126 valores y los resultados correspondientes.

00:00:05

A função SWITCH avalia uma expressão em relação a uma lista de valores e retorna o resultado correspondente ao primeiro valor correspondente.

00:00:15

No nosso exemplo, gostaríamos de saber o dia da semana correspondente à data.

00:00:21

Com a função SWITCH...

00:00:28

A expressão será a seguinte:

00:00:31

o dia da semana,

00:00:35

correspondente a uma data,

00:00:39

com o tipo de retorno "2" correspondente a segunda-feira como o primeiro dia da semana.

00:00:46

Se o valor for "1"

00:00:48

O resultado será "Monday"

00:00:51

E assim por diante...

00:00:57

Essa função permite que você avalie até 126 valores e resultados correspondentes.

00:00:05

De functie SWITCH evalueert een expressie op basis van een lijst met waarden en retourneert het resultaat dat overeenkomt met de eerste overeenkomende waarde.

00:00:15

In ons voorbeeld zouden we graag de dag van de week willen weten die overeenkomt met de datum.

00:00:21

Met de schakelfunctie...

00:00:28

De uitdrukking is als volgt:

00:00:31

de dag van de week,

00:00:35

overeenkomend met een datum,

00:00:39

met Return type "2" overeenkomend met maandag als de eerste dag van de week.

00:00:46

Als de waarde "1" is

00:00:48

Het resultaat is "maandag"

00:00:51

En zo op...

00:00:57

Met deze functie u tot 126 waarden en bijbehorende resultaten evalueren.

00:00:05

La funzione SWITCH valuta un'espressione rispetto a un elenco di valori e restituisce il risultato corrispondente al primo valore corrispondente.

00:00:15

Nel nostro esempio, vorremmo conoscere il giorno della settimana corrispondente alla data.

00:00:21

Con la funzione SWITCH...

00:00:28

L'espressione sarà la seguente:

00:00:31

il giorno della settimana,

00:00:35

corrispondente ad una data,

00:00:39

con tipo restituito "2" corrispondente a Lunedi come primo giorno della settimana.

00:00:46

Se il valore è "1"

00:00:48

Il risultato sarà "Lunedi"

00:00:51

E così via...

00:00:57

Questa funzione consente di valutare fino a 126 valori e risultati corrispondenti.

00:00:05

SWITCH 函數根據值清單計算運算式，並返回對應于第一個匹配值的結果。

00:00:15

在我們的示例中，我們想知道與日期對應的星期一天。

00:00:21

使用 SWITCH 功能...

00:00:28

運算式如下：

00:00:31

星期一的一天，

00:00:35

對應日期，

00:00:39

返回類型"2"對應于星期一作為一周的第一天。

00:00:46

如果值為"1"

00:00:48

結果將是"星期一"

00:00:51

等等...

00:00:57

此功能使您能夠評估多達 126 個值和相應的結果。

00:00:05

SWITCH 関数は、値のリストに対して式を評価し、最初の一致する値に対応する結果を返します。

00:00:15

この例では、日付に対応する曜日を知りたい。

00:00:21

SWITCH機能付き...

00:00:28

式は次のようになります。

00:00:31

曜日、

00:00:35

日付に対応し、

00:00:39

週の最初の曜日として月曜日に対応する戻り値タイプ "2" を使用します。

00:00:46

値が "1" の場合

00:00:48

結果は「月曜日」になります。

00:00:51

などなど。。。

00:00:57

この機能を使用すると、最大 126 の値と対応する結果を評価できます。

00:00:05

Chức năng SWITCH thẩm định một biểu thức đối với một danh sách các giá trị và trả về kết quả tương ứng với giá trị phù hợp đầu tiên.

00:00:15

Trong ví dụ của chúng tôi, chúng tôi muốn biết ngày trong tuần tương ứng với ngày.

00:00:21

Với chức năng SWITCH...

00:00:28

Biểu thức sẽ như sau:

00:00:31

trong ngày trong tuần,

00:00:35

tương ứng với một ngày,

00:00:39

với loại trở về "2" tương ứng với thứ hai là ngày đầu tiên của tuần.

00:00:46

Nếu giá trị là "1"

00:00:48

Kết quả sẽ là "thứ hai"

00:00:51

Và như vậy...

00:00:57

Chức năng này cho phép bạn đánh giá lên đến 126 giá trị và kết quả tương ứng.

00:00:05

Fungsi SWITCH mengevaluasi ekspresi terhadap daftar nilai dan mengembalikan hasil yang sesuai dengan nilai pencocokan pertama.

00:00:15

Dalam contoh kami, kami ingin mengetahui hari dalam seminggu yang sesuai dengan tanggal.

00:00:21

Dengan fungsi SWITCH...

00:00:28

Ekspresi akan sebagai berikut:

00:00:31

hari dalam seminggu,

00:00:35

sesuai dengan tanggal,

00:00:39

dengan tipe Return "2" yang sesuai dengan hari Senin sebagai tanggal pertama minggu.

00:00:46

Jika nilainya adalah "1"

00:00:48

Hasilnya akan "Senin"

00:00:51

Dan seterusnya...

00:00:57

Fungsi ini memungkinkan Anda untuk mengevaluasi hingga 126 nilai dan hasil yang sesuai.

00:00:05

Функция SWITCH оценивает выражение по списку значений и возвращает результат, соответствующий первому соответствующему значению.

00:00:15

В нашем примере мы хотели бы знать день недели, соответствующий дате.

00:00:21

С функцией SWITCH...

00:00:28

Выражение будет следующим:

00:00:31

день недели,

00:00:35

в соответствии с датой,

00:00:39

с обратным типом "2", соответствующим понедельнику как первому дню недели.

00:00:46

Если значение "1"

00:00:48

Результатом станет "Понедельник"

00:00:51

И так далее...

00:00:57

Эта функция позволяет оценить до 126 значений и соответствующих результатов.

00:00:05

Функцията SWITCH оценява израз спрямо списък от стойности и връща резултата, съответстващ на първата съвпадаща стойност.

00:00:15

В нашия пример бихме искали да знаем деня от седмицата, съответстващ на датата.

00:00:21

С функцията SWITCH...

00:00:28

Изразът ще бъде както следва:

00:00:31

в деня от седмицата,

00:00:35

съответстващ на дата,

00:00:39

с върнат тип "2", съответстващ на понеделник като първи ден от седмицата.

00:00:46

Ако стойността е "1"

00:00:48

Резултатът ще бъде "понеделник"

00:00:51

И така нататък...

00:00:57

Тази функция ви позволява да оцените до 126 стойности и съответните резултати.

00:00:05

Funcția SWITCH evaluează o expresie în raport cu o listă de valori și returnează rezultatul corespunzător primei valori corespondente.

00:00:15

În exemplul nostru, am dori să știm ziua săptămânii corespunzătoare datei.

00:00:21

Cu funcția SWITCH...

00:00:28

Expresia va fi după urmează:

00:00:31

ziua săptămânii,

00:00:35

corespunzătoare unei date,

00:00:39

cu tipul de retur "2" corespunzător zilei de luni ca prima zi a săptămânii.

00:00:46

Dacă valoarea este "1"

00:00:48

Rezultatul va fi "Luni"

00:00:51

Și așa mai departe...

00:00:57

Această funcție vă permite să evaluați până la 126 de valori și rezultate corespunzătoare.

00:00:05

Функція SWITCH обчислює вираз зі списком значень і повертає результат, що відповідає першому відповідному значенням.

00:00:15

У нашому прикладі ми хотіли б знати день тижня, що відповідає даті.

00:00:21

За допомогою функції SWITCH...

00:00:28

Вираз буде таким чином:

00:00:31

день тижня,

00:00:35

відповідно до дати,

00:00:39

з типом повернення "2", що відповідає понеділок як перший день тижня.

00:00:46

Якщо значення "1"

00:00:48

Результатом буде "понеділок"

00:00:51

І так далі ...

00:00:57

Ця функція дає змогу оцінити до 126 значень і відповідні результати.

00:00:05

Funkcja SWITCH oblicza wyrażenie względem listy wartości i zwraca wynik odpowiadający pierwszej pasującej wartości.

00:00:15

W naszym przykładzie chcielibyśmy poznać dzień tygodnia odpowiadający dacie.

00:00:21

Z funkcją SWITCH...

00:00:28

Wyrażenie będzie następujące:

00:00:31

w dniu tygodnia,

00:00:35

odpowiadające daty,

00:00:39

z typem zwrotnym "2" odpowiadającym poniedziałek jako pierwszy dzień tygodnia.

00:00:46

Jeśli wartość to "1"

00:00:48

Rezultatem będzie "Poniedziałek"

00:00:51

I tak dalej...

00:00:57

Ta funkcja umożliwia ocenę do 126 wartości i odpowiadających im wyników.

00:00:05

SWITCH işlevi bir ifadeyi değerler listesine göre değerlendirir ve ilk eşleşen değere karşılık gelen sonucu döndürür.

00:00:15

Örneğimizde, haftanın gününün tarihe karşılık geldiğini bilmek isteriz.

00:00:21

SWITCH fonksiyonu ile...

00:00:28

İfade aşağıdaki gibi olacaktır:

00:00:31

haftanın günü,

00:00:35

bir tarihe karşılık gelen,

00:00:39

haftanın ilk günü olarak Pazartesi'ye karşılık gelen iade türü "2" ile.

00:00:46

Değer "1" ise

00:00:48

Sonuç "Pazartesi" olacak

00:00:51

Ve böylece ...

00:00:57

Bu işlev, en fazla 126 değeri ve ilgili sonuçları değerlendirmenizi sağlar.

00:00:05

A função SWITCH avalia uma expressão contra uma lista de valores e devolve o resultado correspondente ao primeiro valor de correspondência.

00:00:15

No nosso exemplo, gostaríamos de saber o dia da semana correspondente à data.

00:00:21

Com a função SWITCH...

00:00:28

A expressão será a seguinte:

00:00:31

no dia da semana,

00:00:35

correspondente a uma data,

00:00:39

com o tipo de retorno "2" correspondente a segunda-feira como o primeiro dia da semana.

00:00:46

Se o valor for "1"

00:00:48

O resultado será "Segunda-feira".

00:00:51

E assim por diante...

00:00:57

Esta função permite avaliar até 126 valores e resultados correspondentes.

00:00:05

Funkcija SWITCH daje izraz u odnosu na listu vrednosti i daje rezultat koji odgovara prvoj vrednosti koja se podudara.

00:00:15

U našem primeru, želeli bismo da znamo da je dan u nedelji odgovarajući datumu.

00:00:21

Sa funkcijom SWITCH...

00:00:28

Izraz će biti sledeći:

00:00:31

dana u nedelji,

00:00:35

odgovarajući datumu,

00:00:39

sa tipom povraćaja "2" koji odgovara ponedeljak kao prvom danu u nedelji.

00:00:46

Ako je vrednost "1"

00:00:48

Rezultat će biti "Ponedeljak"

00:00:51

I tako dalje...

00:00:57

Ova funkcija vam omogućava da procenite do 126 vrednosti i odgovarajuće rezultate.

00:00:15

في مثالنا، نود أن نعرف يوم الأسبوع المقابل للتاريخ.

00:00:21

مع الدالة SWITCH...

00:00:28

سيكون التعبير كما يلي:

00:00:31

في يوم من أيام الأسبوع

00:00:35

المقابلة لتاريخ،

00:00:39

مع نوع الإرجاع "2" المقابل ليوم الاثنين كأول يوم في الأسبوع.

00:00:46

إذا كانت القيمة "1"

00:00:48

وستكون النتيجة "الاثنين"

00:00:51

وهكذا...

00:00:57

تمكنك هذه الوظيفة من تقييم ما يصل إلى 126 قيمة ونتائج مناظرة.

00:00:15

우리의 예에서, 우리는 날짜에 해당하는 요일을 알고 싶습니다.

00:00:21

스위치 기능으로...

00:00:28

식은 다음과 같습니다.

00:00:31

요일,

00:00:35

날짜에 해당하며,

00:00:39

월요일에 해당하는 반품 유형 "2"를 요일로 합니다.

00:00:46

값이 "1"인 경우

00:00:48

결과는 "월요일"이 될 것입니다.

00:00:51

그리고 등등 ...

00:00:57

이 기능을 사용하면 최대 126개의 값과 해당 결과를 평가할 수 있습니다.

00:00:15

在我们的示例中，我们希望知道与日期相对应的星期几。

00:00:21

使用开关功能...

00:00:28

表达式将如下所示：

00:00:31

星期几，

00:00:35

对应于日期，

00:00:39

返回类型"2"对应于星期一作为一周的第一天。

00:00:46

如果值为"1"

00:00:48

结果将是"星期一"

00:00:51

等等...

00:00:57

此功能使您能够评估多达 126 个值和相应的结果。

00:00:15

ในตัวอย่างของเราเราต้องการทราบวันในสัปดาห์ที่สอดคล้องกับวันที่

00:00:21

ด้วยฟังก์ชัน SWITCH...

00:00:28

นิพจน์จะเป็นดังนี้:

00:00:31

วันในสัปดาห์

00:00:35

สอดคล้องกับวันที่

00:00:39

ด้วยประเภทการส่งคืน "2" ที่สอดคล้องกับวันจันทร์เป็นวันแรกของสัปดาห์

00:00:46

ถ้าค่าเป็น "1"

00:00:48

ผลลัพธ์จะเป็น "วันจันทร์"

00:00:51

และอื่น ๆ ...

00:00:57

ฟังก์ชันนี้ช่วยให้คุณสามารถประเมินค่าได้สูงสุด 126 ค่าและผลลัพธ์ที่สอดคล้องกัน

Mandarine AI: CE QUI POURRAIT VOUS INTÉRESSER

RELATED COURSES This tutorial is part of the following courses

1898 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...

Cours E-learning

ALL COURSES