Yadda za a ƙirƙiri Database a Excel

Biyan lambobin sadarwa, tattarawa, da sauran bayanan da ke da wani asusun Excel

A wasu lokuta, muna buƙatar ci gaba da lura da bayanai da kuma kyakkyawan wuri ga wannan yana cikin fayil ɗin Excel. Ko yana da jerin lambobin lambobin waya, jerin lambobin sadaukarwa ga membobin kungiya ko ƙungiya, ko tarin tsabar kudi, katunan, ko littattafai, wani fayil na bayanan Excel ya sa ya sauƙi shigarwa, adana, da kuma samun bayanan bayani.

Microsoft Excel ta gina kayan aiki don taimaka maka ci gaba da lura da bayanai da kuma samun bayanan bayani idan kana so. Bugu da ƙari, tare da daruruwan ginshiƙai da dubban layuka , ɗakin lissafin Excel zai iya ɗaukar adadin bayanai .

Shigar da Bayanan

Mahimman tsari na adana bayanai a cikin wani shafin Excel shine tebur.

Da zarar an halicci tebur, za'a iya amfani da kayan aikin na Excel don bincika, raba, da kuma tace fayiloli a cikin database don samun bayani na musamman.

Don bi tare da wannan koyawa, shigar da bayanai kamar yadda aka nuna a cikin hoto a sama.

Shigar da ID ɗin ID ɗin da sauri:

1. Rubuta ID na farko na farko - ST348-245 da ST348-246 zuwa cikin sassan A5 da A6, bi da bi.
2. Ƙarrafa abubuwan ID guda biyu don zaɓar su.
3. Danna kan cika cika kuma ja shi zuwa cell A13 .
4. Sauran ID na ID ya kamata a shiga cikin sassan A6 zuwa A13 daidai.

Shiga Data Daidai

Lokacin shigar da bayanai, yana da muhimmanci a tabbatar da an shigar da shi daidai. Baya ga jere 2 tsakanin maɓallin lissafi da kuma rubutun shafi, kada ku bar kowane layuka marar launi lokacin shigar da bayanai. Har ila yau, tabbatar da cewa ba za ku bar kowane nau'in komai ba.

Kuskuren bayanai , lalacewa ta hanyar shigar da bayanai ba daidai ba, sune tushen matsala masu yawa dangane da gudanar da bayanai. Idan an shigar da bayanai daidai a farkon, shirin zai iya ba ku sakamakon da kuke so.

Rows ne Records

Kowane jeri na bayanan bayanai, a cikin bayanai an san shi a matsayin rikodin . Lokacin shigar da rubuce-rubucen kiyaye waɗannan sharuɗɗa a tuna:

• Kada a bar layuka marar launi a cikin tebur da aka halitta. Wannan ya hada da BAYA bar jeri marar layi a tsakanin shafukan shafi da jere na farko na bayanai.
• Mai rikodin zai iya ƙunsar bayanai game da abu ɗaya kawai.
• Dole ne rikodin ya ƙunshi dukkanin bayanai a cikin database game da wannan abu. Ba za'a iya samun bayani game da abu a cikin jere fiye da ɗaya ba.

Gumomin suna Fields

Duk da yake layuka a cikin wani shafin Excel an kira su a matsayin littattafai, ana kiran ginshiƙai a matsayin filayen . Kowace shafi yana buƙatar wata don gano bayanan da ya ƙunshi. Wadannan maƙallan suna kiransa sunayen filin.

• Ana amfani da sunaye don tabbatar da cewa an shigar da bayanai don kowane rikodin a cikin jerin.
• Tabbatar cewa an shigar da dukkanin bayanai a cikin wani shafi ta yin amfani da wannan tsari. Idan ka fara shigar da lambobi kamar lambobi (kamar 10 ko 20) kiyaye shi. Kada ku canza hanyar hanya ta hanyar shiga lambobi kamar kalmomi (kamar goma ko ashirin). Kasancewa.
• Kada ku bar ginshiƙai a cikin tebur.

Samar da Table

Da zarar an shigar da bayanai, ana iya canza shi cikin tebur . Don yin haka:

1. Yi amfani da kwayoyin A3 zuwa E13 a cikin takardun aiki.
2. Danna kan shafin shafin.
3. Danna kan Tsarin a matsayin Zaɓin Zaɓin a kan rubutun don buɗe menu da aka sauke.
4. Zabi madaukiyar salon Style Style 9 don buɗe Siffar maganganu a matsayin Table .
5. Duk da yake akwatin maganganun ya buɗe, kullun A3 zuwa E13 a kan takarda ya kamata a kewaye da tururuwa masu tafiya.
6. Idan tururuwa masu tafiya suna kewaye da kewayon kwayoyin halitta, danna Ok a cikin Siffar maganganu kamar Table .
7. Idan tururuwa masu tafiya ba su kewaye da kewayon kwayoyin halitta ba, nuna haskakawa a cikin takardun aiki sa'annan ka danna Ok a cikin Magana a matsayin akwatin maganganu.
8. Teburin ya kamata a sauƙaƙe kiban da aka haɓaka kusa da kowanne sunan filin kuma wajibi ne a tsara su a madaidaicin haske da duhu.

Yin Amfani da Bayanan Kayan Gida

Da zarar ka ƙirƙiri bayanan, za ka iya amfani da kayan aikin da ke ƙarƙashin jerin kiban da ke gefen kowane filin filin don tsara ko tace bayananka.

Bayaniyar Bayanai

1. Danna maɓallin zaɓi na gaba kusa da sunan Sunan Sunan.
2. Danna maɓallin Zaɓin A zuwa Z don rarraba jerin bayanai a cikin haruffa.
3. Da zarar an ware shi, Graham J. ya kasance farkon rikodin a teburin da Wilson. R ya zama na karshe.

Bayanin Juyawa

1. Danna maɓallin saukewa kusa da sunan filin Shirin .
2. Danna akwati kusa da Zaži Duk wani zaɓi don share duk akwati.
3. Danna kan akwati kusa da Zaɓin Kasuwancin don ƙara alama a akwatin.
4. Danna Ya yi .
5. Sai kawai dalibai biyu - G. Thompson da F. Smith ya kamata a bayyane tun lokacin da suke kawai ne kawai a cikin shirin kasuwanci.
6. Don nuna duk rubutun, danna kan gefen saukewa kusa da sunan filin Shirin .
7. Danna kan Filta mai haske daga zaɓi "Shirin" .

Ƙara fadan Database

Don ƙara ƙarin rubutun to your database:

• Sanya maƙallan linzamin ka a kan kananan dot a cikin kusurwar dama na kusurwar tebur.
• Mainter mainter zai canza cikin arrow biyu.
• Lokacin da wannan ya faru, danna ka riƙe maɓallin linzamin linzamin dama sa'annan ka jawo maɓallin ƙasa don ƙara jeri marar kyau zuwa kasan bayanan.
• Ƙara bayanai masu zuwa zuwa wannan jere na gaba:
  Cell - Data
  A14 - ST348-255
  B14 - Christopher
  C14 - A.
  D14 - 22
  E14 - Kimiyya

Ana kammala Databaseing

1. Fassara sel A1 zuwa E1 a cikin takardun aiki.
2. Danna kan shafin shafin.
3. Danna kan Ƙungiya da Cibiyar Cibiyar Rubuta don sanya maƙallin take.
4. Danna kan Ƙarshen Launi (kamar launi na iya) a kan rubutun don buɗe jerin layi na launi mai cika.
5. Zaɓi Blue, Lissafi 1 daga jerin don canja launi na bango a cikin kwayoyin A1 - E1 zuwa blue blue.
6. Latsa gunkin Font Color a kan Toolbar Tsarin (yana da babban harafi "A") don buɗe jerin layin launi na launi.
7. Zaɓi Fira daga jerin don canza launi na rubutun a cikin kwayoyin A1 - E1 zuwa farar fata.
8. Sanya siffofin A2 - E2 a cikin takardun aiki.
9. Danna Ƙaƙwalwar Launi a kan rubutun don buɗe layin rubutun cika layi.
10. Zaɓi Blue, Gyara 1, Mai sauƙi 80 daga jerin don canja launi na bango a cikin kwayoyin A2 - E2 zuwa blue blue.
11. Sanya siffofin A4 - E14 a cikin takardun aiki.
12. Danna kan Zaɓin Cibiyar a kan rubutun don zartar da rubutu a cikin sassan A14 zuwa E14.
13. A wannan lokaci, idan kun bi duk matakai na wannan koyaswar daidai, toshe ɗinku ya kamata ya zama kama da maƙallan da aka hoton a Mataki na 1 na wannan tutorial.

Ayyukan bayanai

Syntax : Dfunction (Database_arr, Field_str | num, Criteria_arr)

A ina D aiki yana ɗaya daga cikin wadannan:

• DAVERAGE
• DCOUNT
• DCOUNTA
• DGET
• DMAX
• DMIN
• KARANTA
• DSTDEV
• DSTDEVP
• DSUM
• DVAR
• DVARP

Rubuta : Database

Ayyukan bayanai suna da amfani sosai lokacin da ake amfani da Google Sheets don kula da bayanan da aka tsara, kamar database. Kowane ɗumbin bayanai, Dfunction , yana ƙunshi aikin daidai a kan wani ɓangare na tantanin tantanin halitta wanda aka ɗauka a matsayin tebur. Ayyukan bayanan bayanai sunyi la'akari da uku:

• Database_arr yana da kewayon, jigilar tsararraki ko tsararren samfurin da aka samo ta hanyar maganganun tsararren da aka tsara don haka kowane jeri bayan Row 1 shine rikodin bayanan bayanai, kuma kowane shafi ne filin filin bayanai. Jerin 1 yana dauke da alamu ga kowane filin.
• Field_str | num ya nuna wane shafi (filin) ​​ya ƙunshi dabi'un da za a ƙaddara. Ana iya bayyana wannan a matsayin ko dai sunan filin (rubutu na rubutu) ko lambar mahallin, inda za a wakilci mafi yawan ginshiƙan a matsayin 1.
• Criteria_arr wani lamari ne, jigilar kayan aiki ko tsararren samfurin da aka tsara ta hanyar maganganun da aka tsara don haka jeri na farko ya ƙunshi sunan filin (s) wanda za'a yi amfani da ma'auni (ma'auni), kuma layuka na gaba sun ƙunshi gwajin gwaji (s) .

Jerin farko a cikin Criteria ya danganta sunaye. Kowace jere a cikin Criteria yana wakiltar tace, wanda shine saitin ƙuntatawa a filayen da aka dace. An ƙayyade ƙuntatawa ta yin amfani da Bayanan tambayoyi-by-Example, kuma zai iya haɗa da darajar da za ta dace ko mai kwatanta kwatankwacin bayanan kwatankwacin. Misalan ƙuntatawa sune: "Cakulan", "42", "> = 42", "<> 42". Hanya maras amfani bata nufin ƙuntatawa akan filin daidai.

Tacewa ta dace da jerin jere na yanar gizo idan duk ƙuntatawa ta ƙuntatawa (ƙuntatawa a jere na tace) an haɗu. Saitunan layi (rikodin) ya gamsar da Criteria idan kuma kawai idan akalla daya tace ya dace da ita. Sunan filin zai iya bayyana fiye da sau ɗaya a cikin Yanayin Criteria don ƙyale ƙuntatawa da yawa waɗanda suka shafi lokaci daya (misali, zazzabi> = 65 da zazzabi <= 82).

DGET ita ce kawai sabis na database wanda ba ya tara dabi'u. DGET ya dawo darajar filin da aka ƙayyade a gardama na biyu (kamar VLOOKUP) kawai lokacin da rikodin daidai ya dace da Criteria; In ba haka ba, yana dawo da kuskure da ya nuna babu matsala ko matakai masu yawa