Mataupu
O ananafi i le malafoni 30 Excel galue i le 30 aso we figured out the details of our operating environment with the function INFO (INFORM) and found that she could no longer help us with memory issues. Neither ours, nor Excel’s memory!
On the fifth day of the marathon, we will study the function Filifili (CHOICE). This function belongs to the category Fa'asinomaga ma fa'asologa, it returns a value from a list of possible choices according to the numeric index. It is worth noting that in most cases it is better to choose another function, for example, INDEX (INDEX) ma Afitusi (MORE FA'AALIGA) po'o VLOOKUP (VPR). We’ll cover these features later in this marathon.
So, let’s turn to the information we have and examples on the function Filifili (CHOICE), let’s see it in action, and also note the weaknesses. If you have other tips and examples for this feature, please share them in the comments.
Function 05: CHOOSE
galuega tauave Filifili (SELECT) returns a value from a list, selecting it according to the numeric index.
How can you use the CHOOSE function?
galuega tauave Filifili (SELECT) can return the item in the list at a specific number, like this:
- By month number, return the fiscal quarter number.
- Based on the start date, calculate the date of the next Monday.
- By store number, show the amount of sales.
Syntax CHOOSE
galuega tauave Filifili (SELECT) has the following syntax:
CHOOSE(index_num,value1,value2,…)
ВЫБОР(номер_индекса;значение1;значение2;…)
- index_num (index_number) must be between 1 and 254 (or 1 through 29 in Excel 2003 and earlier).
- index_num (index_number) can be entered into a function as a number, a formula, or a reference to another cell.
- index_num (index_number) will be rounded down to the nearest integer.
- finauga tāua (value) can be numbers, cell references, named ranges, functions, or text.
Traps CHOOSE (CHOICE)
I Excel 2003 ma muamua atu, o le galuega Filifili (SELECT) supported only 29 arguments tāua (uiga).
It is much more convenient to search through a list on a worksheet than to enter all the elements in a formula. With functions VLOOKUP (VLOOKUP) poʻo Afitusi (MATCH) You can refer to lists of values located in Excel worksheets.
Example 1: Fiscal quarter by month number
galuega tauave Filifili (SELECT) works fine with simple lists of numbers as values. For example, if cell B2 contains the number of the month, the function Filifili (SELECT) can calculate which fiscal quarter it belongs to. In the following example, the fiscal year starts in July.
The formula lists 12 values corresponding to months 1 to 12. The fiscal year starts in July, so months 7, 8, and 9 fall into the first quarter. In the table below, you can see the fiscal quarter number under each month number.
I gaioiga Filifili (SELECT) Quarter number must be entered in the order in which they appear in the table. For example, in the list of function values Filifili (SELECT) in positions 7, 8 ma 9 (July, August and September) should be number 1.
=CHOOSE(C2,3,3,3,4,4,4,1,1,1,2,2,2)
=ВЫБОР(C2;2;3;3;3;4;4;4;1;1;1;2;2;2)
Enter the number of the month in cell C2, and the function Filifili (SELECT) will calculate the fiscal quarter number in cell C3.
Example 2: Calculate the date of next Monday
galuega tauave Filifili (SELECT) can work in combination with the function VAIASO (DAYWEEK) to calculate future dates. For example, if you are a member of a club that meets every Monday evening, then by knowing today’s date, you can calculate the date for next Monday.
The figure below shows the serial numbers of each day of the week. Column H for each day of the week contains the number of days to add to the current date to get the next Monday. For example, you need to add only one day to Sunday. And if today is Monday, then there are still seven days until next Monday.
If the current date is in cell C2, then the formula in cell C3 uses the functions VAIASO (DAY) and Filifili (SELECT) to calculate the date of next Monday.
=C2+CHOOSE(WEEKDAY(C2),1,7,6,5,4,3,2)
=C2+ВЫБОР(ДЕНЬНЕД(C2);1;7;6;5;4;3;2)
Example 3: Show the amount of sales for the selected store
E mafai ona e faʻaogaina le gaioiga Filifili (SELECT) in combination with other functions such as SUM (SUM). In this example, we will get the sales totals for a specific store by specifying its number in the function Filifili (SELECT) as an argument, as well as listing the data ranges for each store to calculate the totals.
In our example, the store number (101, 102, or 103) is entered in cell C2. To get an index value such as 1, 2, or 3 instead of 101, 102, or 103, use the formula: =C2-100.
The sales data for each store is in a separate column as shown below.
Inside a function SUM (SUM) function will be executed first Filifili (SELECT), which will return the desired summation range corresponding to the selected store.
=SUM(CHOOSE(C2-100,C7:C9,D7:D9,E7:E9))
=СУММ(ВЫБОР(C2-100;C7:C9;D7:D9;E7:E9))
This is an example of a situation where it is much more efficient to use other functions such as INDEX (INDEX) ma Afitusi (SEARCH). Later in our marathon, we will see how they work.