Kopsavilkums
Lai izveidotu darbības tabulu Excel tabulā, varat izmantot funkciju INDEX ar strukturētu atsauci, lai izveidotu paplašinošu diapazonu. Parādītajā piemērā formula F5 ir:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Nokopējot kolonnā uz leju, šī formula atgriezīs katras krāsas kolonnas Krāsa skaitu.
Dažās Excel versijās šī ir masīva formula, un tā jāievada ar vadības taustiņu + shift + enter.
Paskaidrojums
Būtībā šī formula izmanto INDEX, lai izveidotu šādu paplašinošu atsauci:
INDEX((Color),1):(@Color) // expanding range
Resnās zarnas (:) kreisajā pusē funkcija INDEX atgriež atsauci uz kolonnas kolonnas pirmo šūnu.
INDEX((Color),1) // first cell in color
Tas darbojas, jo funkcija INDEX atgriež atsauci uz pirmo šūnu, nevis faktisko vērtību. Resnās zarnas labajā pusē mēs iegūstam atsauci uz pašreizējo krāsu kolonnas rindu šādi:
(@Color) // current row of Color
Šī ir standarta strukturēta atsauces sintakse "šai rindai". Šīs divas atsauces apvienojumā ar kolu izveido diapazonu, kas paplašinās, formulu nokopējot tabulā. Tātad, mēs samainām šīs atsauces uz funkciju SUM, mums ir:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Katra no iepriekš minētajām izteiksmēm ģenerē TRUE / FALSE vērtību masīvu, un dubultnegatīvs (-) tiek izmantots, lai šīs vērtības pārvērstu par 1s un 0s. Tātad pēdējā rindā mēs nonākam pie:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Pārējā formula vienkārši savieno krāsu no pašreizējās rindas ar SUM atgriezto skaitli:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Vienkārši paplašināms diapazons?
Kāpēc neizmantot tādu vienkāršu paplašināmo diapazonu kā šis?
SUM(--($B$5:B5=(@Color)))
Kādu iemeslu dēļ šāda veida jauktā atsauce tiek bojāta Excel tabulā, pievienojot rindas. Izmantojot INDEX ar strukturētu atsauci, problēma tiek atrisināta.
