Обобщение
За да изчислите обща сума за престой в хотел, при която цената на нощта варира в зависимост от типа стая и датите на календара, можете да използвате формула, базирана на функцията SUMPRODUCT. В показания пример формулата в J8 е:
=SUMPRODUCT((dates>=J5)*(dates
where dates (B5:B15), rooms (C4:G4), and rates (C5:G15) are named ranges.
With check-in on December 3 and check-out on December 7 (4 nights) in a King room, the formula returns a total of $460.
Explanation
The SUMPRODUCT function multiplies arrays together and returns the sum of products. The trick is to use simple array expressions to "cancel out" the irrelevant rates in the table. SUMPRODUCT then simply sums the rates that remain.
Working from the inside out, this formula uses boolean logic to "filter" the rate data. The filter is constructed with three expressions, provided as the first argument to SUMPRODUCT:
(dates>=J5)* // date greater than or equal to Dec 3 (dates
Each of these expressions returns an array of TRUE FALSE values:
(FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)* (TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE)* (FALSE,FALSE,TRUE,FALSE,FALSE)
The math operation of multiplying the arrays together coerces the TRUE FALSE values to 1s and 0s, so we can visualize the operation more simply like this:
(0;0;1;1;1;1;1;1;1;1;1)* (1;1;1;1;1;1;0;0;0;0;0)* (0,0,1,0,0)
The result is a two-dimensional array like this:
(0,0,0,0,0;0,0,0,0,0;0,0,1,0,0;0,0,1,0,0;0,0,1,0,0;0,0,1,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0)
This is the array that does the filtering in this example. If we overlay the filter array on the rate area of the worksheet, it's easier to understand what's happening:
Notice the array only contains 1s where we want the rate information to come through. Every other value is zero. When this array is multiplied by the rate data (see below) the zeros will cancel out rate data that is not relevant to room, check-in, and check-out information.
The second argument provided to SUMPRODUCT contains all room rates, in the named range rates (C5:G15). This also is a two-dimensional array:
(65,80,90,105,175;80,95,105,120,250;80,95,105,120,250;80,95,105,120,250;90,105,125,150,250;100,115,125,150,300;100,115,125,150,300;65,80,90,105,300;80,95,105,120,250;80,95,105,120,250;80,95,105,120,250)
Inside SUMPRODUCT, the filtering array described above is multiplied by the named range rates. Both arrays are the same size, the result is an array of the same dimensions. Notice only the rates associated the stay (as defined by check-in, check-out, and room) have survived the filter:
(0,0,0,0,0;0,0,0,0,0;0,0,105,0,0;0,0,105,0,0;0,0,125,0,0;0,0,125,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0)
Finally, SUMPRODUCT sums all values in the array and returns a final result, 460.
Note: although the rate information is organized with vertical dates and horizontal rooms, it can be transposed and the formula will still function correctly.
With the FILTER function
You can also solve this problem nicely with the new FILTER function:
=SUM(FILTER(INDEX(rates,0,MATCH(J4,rooms,0)),(dates>=J5)*(dates
The gist of the solution is that we use the INDEX function to extract just the rates for a King room, then we feed those rates to FILTER, which extracts just the rates for relevant dates. FILTER deliverers these rates to the SUM function, which returns a final result.
