Contents
Lokacin ambaton bayanan bayanai (DB), abu na farko da ke zuwa hankali, ba shakka, shine kowane nau'in buzzwords kamar SQL, Oracle, 1C, ko aƙalla Access. Tabbas, waɗannan shirye-shirye ne masu ƙarfi (kuma masu tsada ga galibi) shirye-shirye waɗanda zasu iya sarrafa aikin babban kamfani mai rikitarwa tare da bayanai masu yawa. Matsalar ita ce, wani lokacin irin wannan iko ba a buƙatar kawai. Kasuwancin ku na iya zama ƙanana kuma tare da hanyoyin kasuwanci masu sauƙi, amma kuna son sarrafa ta ta atomatik. Kuma ga ƙananan kamfanoni ne sau da yawa wannan lamari ne na rayuwa.
Da farko, bari mu tsara TOR. A mafi yawan lokuta, rumbun adana bayanai don lissafin kuɗi, alal misali, tallace-tallace na yau da kullun yakamata su iya:
- ci gaba a cikin tebur bayanai game da kaya (farashin), kammala ma'amaloli da abokan ciniki da haɗa waɗannan tebur zuwa juna
- samun dadi siffofin shigarwa bayanai (tare da jerin abubuwan da aka saukar, da sauransu)
- cika wasu bayanai ta atomatik bugu siffofin (biyan kuɗi, lissafin kuɗi, da sauransu)
- fitar da wajibi rahotanni don sarrafa duk tsarin kasuwanci daga ra'ayi na manajan
Microsoft Excel na iya ɗaukar duk waɗannan tare da ɗan ƙoƙari. Mu yi kokarin aiwatar da wannan.
Mataki 1. Bayanan farko a cikin nau'i na tebur
Za mu adana bayanai game da samfurori, tallace-tallace da abokan ciniki a cikin tebur uku (a kan takarda ɗaya ko a kan daban-daban - ba kome ba). Yana da mahimmanci don juya su zuwa "smart tables" tare da girman kai, don kada kuyi tunani game da shi a nan gaba. Ana yin wannan tare da umarnin Tsara azaman tebur tab Gida (Gida - Tsarin azaman Tebur). A shafin da ya bayyana sannan Constructor (Zane) ba allunan sunaye siffantawa a cikin filin Sunan tebur don amfani daga baya:
Gabaɗaya, ya kamata mu sami “Tables masu wayo” guda uku:
Lura cewa allunan na iya ƙunsar ƙarin bayanan bayyanawa. Don haka, alal misali, mu priceya ƙunshi ƙarin bayani game da nau'in (ƙungiyar samfur, marufi, nauyi, da sauransu) na kowane samfur, da tebur Abokin ciniki - birni da yanki (adireshi, TIN, bayanan banki, da sauransu) na kowannensu.
Table Tallace-tallace za a yi amfani da mu daga baya don shigar da kammala ma'amaloli a cikinta.
Mataki 2. Ƙirƙiri fom ɗin shigarwar bayanai
Tabbas, zaku iya shigar da bayanan tallace-tallace kai tsaye cikin tebur kore Tallace-tallace, amma wannan ba koyaushe ya dace ba kuma yana haifar da bayyanar kurakurai da typos saboda "halin ɗan adam". Saboda haka, zai fi kyau a yi wani nau'i na musamman don shigar da bayanai akan takarda daban na wani abu kamar haka:
A cikin tantanin halitta B3, don samun sabunta kwanan wata na yanzu, yi amfani da aikin Farashin TDATA (YANZU). Idan ba a buƙatar lokaci, to a maimakon haka Farashin TDATA ana iya amfani da aikin TODAY (YAU).
A cikin cell B11, nemo farashin samfurin da aka zaɓa a cikin shafi na uku na tebur mai wayo price amfani da aikin VPR (VLOOKUP). Idan baku ci karo da shi ba, to ku fara karantawa ku kalli bidiyon nan.
A cikin cell B7, muna buƙatar jerin zaɓuka tare da samfurori daga lissafin farashin. Don wannan zaka iya amfani da umarnin Data - Tabbatar da Data (Bayanai - Tabbatarwa), saka a matsayin takura list (Jeri) sa'an nan kuma shiga cikin filin source (Source) hanyar haɗi zuwa shafi sunan daga teburinmu mai wayo price:
Hakazalika, an ƙirƙiri jerin zaɓuka tare da abokan ciniki, amma tushen zai zama kunkuntar:
= INDIRECT ("Customers[Client]")
aiki GASKIYA (GASKIYA) ana buƙatar, a cikin wannan yanayin, saboda Excel, da rashin alheri, ba ya fahimtar hanyoyin kai tsaye zuwa tebur mai wayo a cikin filin Source. Amma wannan hanyar haɗin "nannade" a cikin aiki GASKIYA a lokaci guda, yana aiki tare da bang (ƙarin wannan shine a cikin labarin game da ƙirƙirar jerin zaɓuka tare da abun ciki).
Mataki 3. Ƙara macro shigarwar tallace-tallace
Bayan cika fom ɗin, kuna buƙatar ƙara bayanan da aka shigar a ciki zuwa ƙarshen tebur Tallace-tallace. Yin amfani da hanyoyi masu sauƙi, za mu samar da layin da za a ƙara a ƙasan tsari:
Wadancan. cell A20 zai sami hanyar haɗi zuwa = B3, cell B20 zai sami hanyar haɗi zuwa = B7, da sauransu.
Yanzu bari mu ƙara macro na farko mai layi biyu wanda ke kwafin kirtani da aka ƙirƙira kuma ya ƙara shi zuwa teburin Talla. Don yin wannan, danna haɗin haɗin Alt+F11 ko button Kayayyakin aikin Basic tab developer (Mai haɓakawa). Idan ba a ganuwa wannan shafin, to sai ku fara kunna shi a cikin saitunan Fayil - Zaɓuɓɓuka - Saitin Ribbon (Fayil - Zaɓuɓɓuka - Keɓance Ribbon). A cikin taga Editan Kayayyakin Kayayyakin da ke buɗewa, saka sabon tsarin komai a cikin menu Saka - Module kuma shigar da macro code na mu a can:
Sub Add_Sell() Sheets Works("Form Input").Range("A20:E20").Kwafi 'Kwafi layin bayanai daga nau'in n = Sheets ("Sales").Range("A100000").Karshe(xlUp) . Layi 'ƙayyade adadin layin ƙarshe a cikin tebur. Tallace-tallacen Ayyukan Talla ("Sales").Cells(n + 1, 1) . Manna Na Musamman: = xlPasteValues 'manna cikin layi na gaba maras amfani ("Form Input").Range("B5,B7,B9"). ClearContents' share sub form Yanzu za mu iya ƙara maɓalli zuwa fom ɗinmu don gudanar da macro da aka ƙirƙira ta amfani da jerin zaɓuka Saka tab developer (Mai haɓakawa - Saka - Maɓallin):
Bayan kun zana shi, riƙe maɓallin linzamin kwamfuta na hagu, Excel zai tambaye ku wane macro kuke buƙatar sanya shi - zaɓi macro. Ƙara_Saya. Kuna iya canza rubutu akan maɓalli ta danna dama akansa kuma zaɓi umarnin Canja rubutu.
Yanzu, bayan cika fam ɗin, zaku iya danna maɓallin mu kawai, kuma bayanan da aka shigar za a ƙara su ta atomatik zuwa teburin. Tallace-tallace, sannan an share fam ɗin don shigar da sabuwar yarjejeniya.
Mataki na 4 Haɗa Tables
Kafin gina rahoton, bari mu haɗa teburin mu tare domin daga baya mu iya lissafin tallace-tallace da sauri ta yanki, abokin ciniki, ko rukuni. A cikin tsofaffin nau'ikan Excel, wannan yana buƙatar amfani da ayyuka da yawa. VPR (VLOOKUP) don maye gurbin farashin, nau'ikan, abokan ciniki, birane, da sauransu zuwa teburin Tallace-tallace. Wannan yana buƙatar lokaci da ƙoƙari daga gare mu, kuma yana "ci" albarkatun Excel da yawa. Fara tare da Excel 2013, ana iya aiwatar da komai da yawa cikin sauƙi ta hanyar kafa alaƙa tsakanin tebur.
Don yin wannan, a kan tab data (Kwanan wata) click dangantaka (Dangantaka). A cikin taga da ya bayyana, danna maɓallin Create (sabon) kuma zaɓi daga cikin abubuwan da aka saukar da lissafin tebur da sunayen ginshiƙan waɗanda yakamata su kasance masu alaƙa da su:
Muhimmiyar batu: Dole ne a ƙayyade allunan a cikin wannan tsari, watau tebur mai haɗin gwiwa (price) dole ne kada ya ƙunshi cikin maɓalli na maɓalli (sunan) samfuran kwafi, kamar yadda yake faruwa a cikin tebur Tallace-tallace. A wasu kalmomi, teburin da ke da alaƙa dole ne ya kasance wanda za ku nemo bayanai ta amfani da shi VPRidan aka yi amfani da shi.
Tabbas, an haɗa tebur a irin wannan hanya Tallace-tallace tare da tebur Abokin ciniki ta hanyar gama gari Abokin ciniki:
Bayan kafa hanyoyin haɗin gwiwa, taga don sarrafa hanyoyin haɗin za a iya rufe; ba lallai ne ku sake maimaita wannan hanya ba.
Mataki na 5. Muna gina rahotanni ta amfani da taƙaitaccen bayani
Yanzu, don nazarin tallace-tallace da kuma bin diddigin yanayin aikin, bari mu ƙirƙira, alal misali, wani nau'in rahoto ta amfani da tebur mai mahimmanci. Saita tantanin halitta mai aiki zuwa tebur Tallace-tallace kuma zaɓi shafin akan ribbon Saka - PivotTable (Saka - Tebur Pivot). A cikin taga da ke buɗewa, Excel zai tambaye mu game da tushen bayanai (watau tebur Tallace-tallace) da wurin loda rahoton (zai fi dacewa akan sabon takarda):
Muhimmin batu shi ne cewa wajibi ne don kunna akwati Ƙara wannan bayanan zuwa samfurin bayanai (Ƙara bayanai zuwa Model Data) a kasan taga don Excel ya fahimci cewa muna so mu gina rahoto ba kawai akan tebur na yanzu ba, amma kuma amfani da duk dangantaka.
Bayan danna kan OK panel zai bayyana a hannun dama na taga Filayen teburiinda za a danna mahaɗin Dukdon ganin ba kawai na yanzu ba, amma duk "tebur masu wayo" waɗanda ke cikin littafin a lokaci ɗaya. Sannan, kamar yadda yake a cikin tebirin pivot na gargajiya, zaku iya jawo filayen da muke buƙata kawai daga kowane tebur masu alaƙa zuwa yankin. Tace, Rows, Stolbtsov or dabi'u - kuma Excel nan take zai gina kowane rahoton da muke buƙata akan takardar:
Kar a manta cewa tebur pivot yana buƙatar sabuntawa lokaci-lokaci (lokacin da bayanan tushen ya canza) ta danna-dama akansa kuma zaɓi umarnin Sabunta & Ajiye (Sake sabuntawa), saboda ba zai iya yin ta ta atomatik ba.
Hakanan, ta zaɓar kowane tantanin halitta a cikin taƙaitawa kuma danna maɓallin Ginshiƙi mai mahimmanci (Tsarin Pivot) tab analysis (Nazari) or Siga (Zaɓuɓɓuka) zaka iya saurin hango sakamakon da aka lissafta a ciki.
Mataki 6. Cika abubuwan bugawa
Wani aiki na yau da kullun na kowane ma'ajin bayanai shine cikawa ta atomatik na nau'ikan bugu da nau'ikan nau'ikan nau'ikan nau'ikan nau'ikan nau'ikan nau'ikan nau'ikan nau'ikan nau'ikan nau'ikan nau'ikan nau'ikan bayanai (rasitoci, daftari, ayyuka, da sauransu). Na riga na rubuta game da ɗayan hanyoyin yin wannan. Anan muna aiwatar da, misali, cike fom ta lambar asusun:
Ana ɗauka cewa a cikin cell C2 mai amfani zai shigar da lamba (lambar layi a cikin tebur Tallace-tallace, a zahiri), sannan ana ciro bayanan da muke buƙata ta amfani da aikin da aka sani VPR (VLOOKUP) da fasali INDEX (INDEX).
- Yadda ake amfani da aikin VLOOKUP don duba sama da duba ƙimar
- Yadda ake maye gurbin VLOOKUP tare da ayyukan INDEX da MATCH
- Cika fom da fom ta atomatik tare da bayanai daga tebur
- Ƙirƙirar Rahotanni tare da PivotTables