Specielt opslag i matrice

I artiklen Opslag via koordinater, beskæftiger jeg med med, hvordan man kan finde en værdi i en matrix på baggrund af række og kolonneoverskrifter.

  A B C D E
1   A B C  
2 1 Birthe Georg Grethe  
3 2 Christina Hans Helle  
4 3 Dorte Ivan Inge  
5 4 Else Jens Jill  

Ved at indtaste fx B og 3 i de celler, som refereres i formlen =INDEKS(A1:D5;SAMMENLIGN(E2;A1:A5;0);SAMMENLIGN(E1;A1:D1;0)), altså E2 og E1 returneres værdien Hans. Temaet i denne artikel er en udvidelse af ovenstående.

For det første befinder opslagsmatrixen sig i et andet ark, her kaldet "Opslag". Det er der i sig selv ikke noget svært i. Data i dette ark kunne se ud som følger:

  A B C D E F G
1 Opslag 20 25 30 35 40 45
2 0   24             39             62             99           160           263
3 1             23             37             60             95           154           253
4 2             22             36             57             91           147           242
5 3             21             34             55             87           141           231
6 4             20             33             52             83           134           220
7 5             19             31             49             79           127           209
8 6             18             29             47             74           120           198
9 7             17             28             44             70           113           186
10 8             16             26             41             66           106           174
11 9             15             24             38             61             99           162
12 10             13             22             35             56             91           150
13 11             12             20             32             52             84           138
14 12               10            18              26               40               60               112

 

Jeg vil nu gerne kunne indtaste en værdi mellem 1 og ... (lodret), idet alle værdier over 12 skal fortolkes som 12 og en værdi mellem 20 og 45 (værdier over 45 skal returnere en fejl) vandret og få returneret en værdi. Forskellen på denne opgave og opgaven ovenfor, ligger i, at jeg denne gang også gerne vil kunne indtaste værdier, der ikke findes, fx 32 og stadig få returneret en værdi efter princippet, 31 og 32 skal returnere værdien for 30 mens 33 og 34 skal returnere værdien for 35. 30 og 35 skal selvfølgelig returnere deres respektive værdier.

En formel til dette forslag kunne se således ud

=INDEKS(Opslag!A1:G14;SAMMENLIGN(LOPSLAG(B2;Opslag!A2:A14;1;SAND);Opslag!A2:A14;0);SAMMENLIGN((AFRUND(B3/5;0)*5);Opslag!B1:G1;1))

B2 og B3 er de celler i arket med formlen, hvor jeg indtaster de værdier, der skal bruges som opslagsværdier.

-Tilbage til Excel -