Yadda za a Yi amfani da aikin DGET a Excel

01 na 01

Nemo Bayanan Musamman a cikin Bayanan Excel

Ayyukan DGET yana ɗaya daga cikin ayyukan ayyuka na Excel. An tsara wannan rukunin ayyuka don yin sauƙi don taƙaita bayanin daga manyan manyan bayanai na bayanai. Suna yin haka ta hanyar dawo da bayanan da suka dace akan daya ko fiye da ma'auni da mai amfani ya zaɓi.

Za'a iya amfani da aikin DGET don komawa ɗaya filin bayanai daga wani shafi na wani babban fayil wanda ya dace da yanayin da ka saka.

DGET yayi kama da aikin VLOOKUP wanda za'a iya amfani dashi don dawowa da matakan bayanai.

DGET Syntax da Arguments

Haɗin aikin aikin DGET shine:

= DGET (database, filin, ma'auni)

Dukkan ayyuka na ƙididdiga suna da nau'i uku guda uku:

• Database : (da ake buƙata) Yana ƙayyade kewayon tantanin salula wanda ke dauke da bayanan. Dole ne a hada sunayen sunaye a cikin kewayon.
• Field : (da ake bukata) Ya nuna wane shafi ko filin ya kamata a yi amfani da shi a cikin lissafinsa. Shigar da hujja ta hanyar buga sunan filin (kamar #Orders) ko shigar da lambar mahallin (kamar 3).
• Criteria : (da ake buƙata) Lissafin kewayon kwayoyin da ke dauke da yanayin da mai amfani ya ƙayyade. Tsarin ya kamata ya haɗa da akalla sunan filin daya daga cikin bayanai kuma a kalla sauran tantancewar salula wanda ya nuna yanayin da za a kimantawa ta hanyar aikin.

Misali Yin amfani da aikin DGET na Excel na:

Wannan misali zai yi amfani da DGET don neman adadin takardun tallace-tallace da wani takamaiman tallace-tallace ya sanya wata wata.

Shigar da Bayanan Tutorial

Lura: Koyarwar ba ta haɗa matakan tsarawa ba.

1. Shigar da tarin bayanai a cikin sel D1 zuwa F13
2. Bar wayar E5 blank; Wannan shi ne inda za a samo takardar DGET
3. Za a yi amfani da sunayen filin a cikin kwayoyin D2 zuwa F2 a matsayin ɓangare na aikin ƙwararriyar Criteria

Zabi Takaddun

Don samun DGET don kawai duba bayanai don takamaiman tallace-tallace muna shigar da sunan wani wakili a karkashin sunan SalesRep a jere 3.

1. A cikin salula F3 rubuta ma'auni Harry
2. A cikin tantanin halitta E5 kamar rubutun #Orders: don nuna bayanin da za mu samu tare da DGET

Namar da Database

Yin amfani da madaidaicin labaran don manyan jeri na bayanai irin su database ba zai iya sauƙaƙe kawai ya shigar da wannan hujjar a cikin aikin ba, amma kuma zai iya hana kurakurai ta hanyar zaɓin ɓangaren ba daidai ba.

Jirgin da ake kira suna da amfani sosai idan ka yi amfani da irin wannan jinsin yawancin sau da yawa a cikin lissafin ko a lokacin da aka tsara sigogi ko sigogi.

1. Sanya sassa D7 zuwa F13 a cikin takardun aiki don zaɓin kewayon
2. Danna sunan akwatin sama a sama da shafi na A a cikin takardun aiki
3. Rubuta SalesData a cikin akwatin sunan don ƙirƙirar mai suna
4. Latsa maɓallin shigarwa akan keyboard don kammala shigarwa

Ana bude Akwatin Gidan DGET

Maganar maganganun aiki yana samar da hanya mai sauƙi don shigar da bayanai ga kowane muhawarar aikin.

Ana buɗe akwatin maganganu don ƙungiyar bayanai na ayyuka da aka aikata ta danna kan maɓallin wizard na aiki ( fx ) dake kusa da maɓallin tsari a sama da takardun aiki.

1. Danna kan tantanin halitta E5 - wurin da za a nuna sakamakon aikin
2. Danna maɓallin wizard ɗin aiki ( fx ) don ɗaga akwatin akwatin Saƙo
3. Rubuta DGET a cikin Bincike don ginin aikin a saman akwatin maganganu
4. Danna maɓallin GO don bincika aikin
5. Dole ne akwatin maganganu ya sami DGET kuma ya lissafa shi a Zaɓi aikin aikin
6. Danna Ya yi don buɗe akwatin maganganun DGET

Ƙarshen Magana

1. Danna kan Database Database na akwatin maganganu
2. Rubuta adireshin mai suna SalesData cikin layi
3. Danna kan filin filin akwatin maganganu
4. Rubuta sunan filin #Bayan cikin layin
5. Danna maɓallin Lissafi na akwatin maganganu
6. Sanya sassa D2 zuwa F3 a cikin takardar aiki don shigar da kewayon
7. Danna Ya yi don rufe akwatin maganganun DGET kuma kammala aikin
8. Amsar 217 ya kamata ya bayyana a cikin cell E5 kamar yadda wannan shi ne adadin takardun kaya da Harry ya sanya a wannan watan
9. Lokacin da ka danna kan tantanin halitta E5 cikakken aikin
   = DGET (SalesData, "#Orders", D2: F3) ya bayyana a cikin maɓallin tsari a sama da takardun aiki

Kuskuren Matsala na Database

#Value : Yana faruwa mafi sau da yawa idan ba a hada sunayen filin a cikin bayanan bayanai ba.

Ga misali a sama, tabbatar da cewa sunaye sunaye a cikin sel D6: F6 an haɗa su cikin tallan tallace-tallace SalesData .