Komplekse Oppslag Formler

April 20  by Eliza

Eddie har en serie med en del tall i formatet 123/45678 eller 011/00345. Han trenger å slå opp navnet på den tilsvarende delenummer fra en annen tabell. Tabellen består av tre kolonner. Den første kolonnen inneholder den delen av delenummer før skråstreken (for eksempel 123 eller 011) og den andre kolonnen inneholder den delen etter skråstreken (for eksempel 45 678 eller 00 345). Den tredje kolonnen inneholder navn som Eddie vil slå opp.

En del av problemet Eddie er å ha er at i bordet, de to kolonner som hver inneholder en del av delenummeret er numeriske verdier. Dermed hele delenummer er ikke 011 og 00 345, men 11 og 345. Eddie lurer på hvordan å sette sammen et oppslag formel for delenumrene (123/45678 eller 011/00345) for å returnere riktig del navn fra bordet.

Til å begynne med, må du gjøre noen endringer i tabellen som inneholder delenumre. De to første kolonnene må formateres som tekst, ikke som tall. Dette vil behandle verdiene i de kolonnene som tekst, slik at når du skriver inn "011" i den første kolonnen, den beholder den ledende null. Hvis du allerede har lagt inn verdien som 011 før du formaterer kolonnen som tekst, vil det fortsatt bli vist som 11 (uten innledende null). Du må ikke bare endre formatet på kolonnen, men også igjen velger en del-nummer fragmenter som inneholder ledende nuller.

Neste må du sørge for at delenummer Tabellen inkluderer kolonneoverskriftene. For dette eksempelet, sørg for de tre kolonneoverskrifter er Venstre, Høyre og navn. (Du kan gjøre disse overskriftene fet og understreket, som hjelper sette dem av fra verdiene i hver kolonne.) Deretter oppretter navn for de enkelte kolonner ved å gjøre følgende:

  1. Velg cellene i den delen talltabellen. Pass på at du også velge de nyopprettede overskrifter for hver kolonne i tabellen.
  2. Klikk Sett inn | Name | Definer. Excel viser Lag Names dialogboksen.
  3. Pass på at bare den øverste raden er merket.
  4. Klikk på OK.

Med din del-talltabellen forberedt, er du nå klar til å lete opp delenumre. I cellene bare til høyre for dine oppslags verdier (123/45678 og 011/00345) skal du legge inn en matriseformel. Forutsatt at den første delenummer er i celle A1, ville du skrive inn følgende matrise formel i celle B1:

= INDEKS (Navn, MATCH (A1, Venstre og "/" & Right, 0))

Husk at for å betegne dette som en matriseformel du skrive det inn ved å trykke Shift + Ctrl + Enter. Formelen vises deretter på formellinjen med {bukseseler} rundt det. Man kan kopiere formelen ned til andre celler i kolonne B, som det er nødvendig.

Den formelen fungerer ved å trekke verdien fra kolonnen Navn av tabellen når den tilsvarende Venstrekolonneverdi sammenkjedet med en verdi Høyre-kolonne slash og matcher det som er i celle A1. Hvis det ikke er kamp, ​​returnerer formelen en # N / A feilverdien, ellers returnerer den ønskede delenummer.

Merk at denne tilnærmingen fungerer bare hvis du formaterer de to første kolonnene i din del-talltabellen som tekst og sørge for at de ulike cellene inneholder noen ledende nuller. Om for noen grunn, kan du ikke formatere de to første kolonnene i tabellen på denne måten (kanskje det er for mange av dem), så må du endre oppslags formel du bruker:

= INDEKS (Navn, MATCH (A1, TEKST (Venstre, "000") og "/" & TEKST (Høyre, "00000"), 0))

Merk at denne versjonen av formelen (som fortsatt bør legges inn som en matriseformel) bruker TEKST funksjonen på to steder, for å konvertere verdier fra venstre og høyre kolonner slik at de har ledende nuller.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (2787) gjelder for Microsoft Excel 97, 2000, 2002, og 2003.