Trekke et telefonnummer med en kjent, fornavn og etternavn

September 10  by Eliza

Kimm har et regneark som har tre kolonner i den. Kolonne A inneholder det siste navnet på en person, inneholder kolonne B fornavn, og kolonne C inneholder telefonnummeret til den enkelte. Hvis Kimm vet personens fornavn og etternavn (sier de er i cellene F1 og F2, henholdsvis), lurer hun på hva slags oppslags formel hun ville bruke til å returnere telefonnummeret til den første personen matchende at for- og etternavn.

Det er faktisk flere forskjellige formler du kan bruke til å finne ut telefonnummeret. De fleste av de tilnærminger innebære å bruke matriseformler, som alltid er lagt inn i en celle ved å bruke Ctrl + Shift + Enter. For å gjøre formlene enklere å forstå, er det best å jobbe med navngitte områder. For eksempel kan du sette opp følgende navn:

  • Velg alle etternavn, i kolonne A, og gi dem et navn som LNames.
  • Velg alle fornavn, i kolonne B, og gi dem et navn som FNames.
  • Velg alle telefonnumre, i kolonne C, og gi dem et navn som Phones.

Her er et utvalg av matriseformler du kan bruke for å finne telefonnummeret:

= SUMPRODUCT (- (LNames = F2), - (FNames = F1), telefon)
= INDEKS (Phones, MATCH (F1 og F2, FNames & LNames,))
= Indeks (Phones, INDEX (MATCH (F1 og F2, FNames & LNames, 0),))
= FORSKYVNING (C1, MATCH (F1 og F2, FNames & LNames,),)

I tillegg kan du konstruere en matriseformel som er avhengig av ROW funksjon, som vist her:

= Indeks (Phones, SUMPRODUCT ((F1 og F2 = FNames & LNames) * (ROW (FNames) -1)))
= INDEKS (Telefoner, MIN (IF ((FNames = F1) * (LNames = F2), (ROW (Phones -1)))))

Hvis du bruker denne tilnærmingen (stole på ROW funksjon), må du kanskje justere "-1" delen av formlene for å reflektere antallet rader som vises før de faktiske data. I dette tilfelle er rekken dekrementert med en, fordi den første raden i datatabellen består av hoder for hver kolonne; selve dataene begynner i rad 2.

Merk at alle formlene som brukes så langt stole på å kombinere den første navn med etternavnet for å gjøre en sammenligning. Dette har sjansen for å kjøre inn "falske positive" i enkelte tilfeller. For eksempel, la oss si at to av navnene du har i dine data er Thom Astonfield og Thomas Tonfield. Siden det gjelder bokstavene i navnene spiller ingen rolle i disse formlene, når du kombinerer første og siste navn på disse personene, de er akkurat det samme. Dermed, hvis du er ute etter telefonnummeret til Thomas Tonfield og hans navn står på listen etter Thom Astonfield Da vil du alltid få Thom telefonnummer i stedet for Thomas '.

For å komme rundt dette potensielle problemet, kan det være lurt å ta med en slags separator mellom fornavn og etternavn. Ved hjelp av en av formler fra tidligere i spissen, er alt du trenger å gjøre gjøre en justering, som vist her:

= Indeks (Phones, SUMPRODUCT ((F1 & ":" & F2 = FNames & ":" & LNames) * (ROW (FNames -1))))

Tillegg av kolon mellom fornavn og etternavn fungerer som en separator, eliminerer muligheten for falske positiver.

Du bør også innse at hvis cellene F1 og F2 inneholde et navn som ikke vises i data i det hele tatt, den informasjonen du får tilbake fra formelen vil bli feilaktig. Avhengig av variasjon av funksjon som brukes, kan du få tilbake en faktisk feiltilstand (for eksempel # N / A eller #REF), eller du kan få tilbake vanlig feil data. I de tilfeller hvor en feiltilstand er returnert, kan det være lurt å justere formelen for å ta høyde for muligheten av å ikke finne en match, på denne måten:

= IF (ERFEIL (indeks (Phones, MATCH (F1 & ":" & F2, FNames & ":" & LNames, 0))),
"Ingen telefon", INDEX (Phones, MATCH (F1 & ":" & F2, FNames & ":" & LNames, 0)))

Det er noen ting du bør huske på når du bruker disse typer formler. Først og fremst, er påliteligheten av informasjonen du får tilbake kommer til å avhenge i stor grad av kvaliteten på informasjonen i dataliste. Hvis dataene har feilstavelser i det, inneholder blanks, er sortert i noen merkelige orden, eller det er flere poster for samme person, kan det påvirke hva formelen returnerer.

Hvis du ikke er så sikker på om kvaliteten på dataene dine, kan det være lurt å bare bruke Excel filtrering evner heller enn en formel. Påfør en Autofilter, og du kan bruke de to første kolonnene av dine data for å plukke fornavn og etternavn. Dette vil da gå tilbake telefonnumre for den enkelte du velger. Det er veldig enkelt å gjøre og gjør velge dataene du trenger lett.

Til slutt bør du innse at det finnes andre metoder du kan bruke til å ta opp problemet. For eksempel kan du lage en formel som bruker DGET funksjon, men dette ville kreve tillegg av en liten kriterier bord til regnearket eller arbeidsboken. Siden Kimm spesifisert at hun ikke kunne legge til mellomresultater i et regneark, og deretter en redaksjonell ble besluttet å ikke inkludere DGET funksjon som en løsning, siden det ville kreve tillegg av kriteriene tabellen.

I tillegg, hvis du er komfortabel med å bruke makroer, kan du også lage en brukerdefinert funksjon som ville undersøke dataene og returnere den forespurte telefonnummer. Fordelen med å bruke en slik tilnærming er at det gir deg større fleksibilitet i behandlingen av informasjonen at funksjonen faktisk returnerer.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (10478) gjelder for Microsoft Excel 97, 2000, 2002, og 2003. Du kan finne en versjon av dette tipset for Båndet av Excel (Excel 2007 og senere) her: Trekke et telefonnummer med en kjent Først og Sist navn.