Contents
Yawancin hanyoyin kasuwanci (har ma da kasuwancin gaba ɗaya) a cikin wannan rayuwar sun haɗa da cika umarni ta ƙayyadaddun adadin masu yin ta hanyar ƙayyadaddun ƙayyadaddun ƙayyadaddun. Shirye-shiryen a cikin irin waɗannan lokuta yana faruwa, kamar yadda suke faɗa, "daga kalandar" kuma sau da yawa akwai buƙatar canja wurin abubuwan da aka tsara a ciki (umarni, tarurruka, bayarwa) zuwa Microsoft Excel - don ƙarin bincike ta hanyar ƙididdiga, tebur pivot, charting, da dai sauransu.
Tabbas, Ina so in aiwatar da irin wannan canja wuri ba ta hanyar kwafin wawa ba (wanda ba shi da wahala), amma tare da sabunta bayanai ta atomatik ta yadda a nan gaba duk canje-canjen da aka yi a kalanda da sabbin umarni akan tashi za a nuna su a ciki. Excel. Kuna iya aiwatar da irin wannan shigo da shi cikin 'yan mintuna kaɗan ta amfani da ƙarar Query Query da aka gina a cikin Microsoft Excel, farawa daga sigar 2016 (na Excel 2010-2013, ana iya saukar da shi daga gidan yanar gizon Microsoft kuma shigar da shi daban daga mahaɗin). .
A ce mun yi amfani da Kalanda na Google kyauta don tsarawa, wanda a cikinsa, don dacewa, na ƙirƙiri kalanda daban (maɓallin tare da alamar ƙari a cikin ƙananan kusurwar dama kusa da shi). Sauran kalanda) da take Work. Anan muna shigar da duk umarni waɗanda ke buƙatar kammalawa kuma a isar da su ga abokan ciniki a adireshinsu:
Ta danna kowane oda sau biyu, zaku iya duba ko gyara bayanan sa:
Lura cewa:
- Sunan taron shine kocinwanda ya cika wannan umarni (Elena) da Lambar oda
- Nuna adireshin delivery
- Bayanan kula ya ƙunshi (a cikin layi daban-daban, amma a kowane tsari) sigogin tsari: nau'in biyan kuɗi, adadin, sunan abokin ciniki, da dai sauransu a cikin tsari. Siga=darajar.
Don tsabta, umarni na kowane manajan suna haskakawa a cikin launi nasu, kodayake wannan ba lallai ba ne.
Mataki 1. Samu hanyar haɗi zuwa Google Calendar
Da farko muna buƙatar samun hanyar haɗin yanar gizo zuwa kalandar odar mu. Don yin wannan, danna maɓallin tare da dige guda uku Zaɓuɓɓukan Kalanda Aiki kusa da sunan kalanda kuma zaɓi umarnin Saituna da Sharing:
A cikin taga da ke buɗewa, zaku iya, idan ana so, sanya kalanda a bainar jama'a ko buɗe damar yin amfani da shi ga masu amfani ɗaya. Muna kuma buƙatar hanyar haɗin kai don shiga cikin sirri zuwa kalanda a cikin tsarin iCal:
Mataki 2. Load bayanai daga kalanda zuwa Power Query
Yanzu bude Excel kuma a kan shafin data (idan kuna da Excel 2010-2013, to akan shafin Tambayar .arfi) zaɓi umarni Daga Intanet (Data - Daga Intanet). Sannan liƙa hanyar da aka kwafi zuwa kalanda kuma danna Ok.
Tambayar ICal Power ba ta gane tsarin ba, amma yana da sauƙin taimakawa. Ainihin, iCal babban fayil ne na rubutu tare da hanji a matsayin mai iyakancewa, kuma a ciki yana kama da wani abu kamar haka:
Don haka zaku iya danna dama akan gunkin fayil ɗin da aka zazzage kuma zaɓi tsarin da ya fi kusa da ma'ana CSV - kuma bayananmu game da duk umarni za a loda su cikin editan Query Query kuma a raba su zuwa ginshiƙai biyu ta hanji:
Idan ka duba da kyau, za ka iya ganin cewa:
- Bayanai game da kowane taron (oda) an haɗa su cikin katangar da ke farawa da kalmar BEGIN da ƙarewa da KARSHE.
- Ana adana lokutan farawa da ƙarshen kwanan wata a cikin igiyoyi masu lakabin DTSTART da DTEND.
- Adireshin jigilar kaya shine LOCATION.
- Bayanin oda - filin DESCRIPTION.
- Sunan taron (sunan mai gudanarwa da lambar oda) - Filin TAKAICE.
Ya rage don cire wannan bayanin mai amfani da canza shi zuwa tebur mai dacewa.
Mataki 3. Juya zuwa Al'ada View
Don yin wannan, yi jerin ayyuka masu zuwa:
- Mu share manyan layuka 7 da ba mu buƙata kafin umarnin BEGIN na farko Gida - Share Layuka - Share Manyan Layukan (Gida - Cire layuka - Cire manyan layuka).
- Tace ta shafi Column1 Layukan da ke ɗauke da filayen da muke buƙata: DTSTART, DTEND, BAYANI, WURI da TAKAITA.
- A kan Babba shafin Ƙara ginshiƙi zabi Rukunin fihirisa (Ƙara shafi - Rukunin Fihirisa)don ƙara ginshiƙi lambar layi zuwa bayanan mu.
- Dama can akan shafin. Ƙara ginshiƙi zabi tawagar Sharadi na sharadi (Ƙara shafi - Sharadi na Sharadi) kuma a farkon kowane toshe (oda) muna nuna ƙimar ma'aunin:
- Cika sel mara komai a cikin ginshiƙin da aka samu Blockta danna dama a kan take kuma zaɓi umarnin Cika - Kasa (Cika - Down).
- Cire ginshiƙin da ba dole ba index.
- Zaɓi shafi Column1 da aiwatar da jujjuyawar bayanai daga ginshiƙi Column2 ta amfani da umarnin Canji - Rukunin Pivot (Canja - Pivot shafi). Tabbatar zaɓi a cikin zaɓuɓɓukan Kada ku tara (Kada a tara)don kada a yi amfani da aikin lissafi akan bayanan:
- A cikin sakamakon tebur mai girma biyu (giciye), share ɓangarorin baya a cikin ginshiƙin adireshi (danna dama akan taken shafi - Maye gurbin dabi'u) kuma cire ginshiƙin da ba dole ba Block.
- Don kunna abubuwan da ke cikin ginshiƙan DTSTART и DTEND a cikin cikakken lokacin kwanan wata, nuna alamar su, zaɓi kan shafin Canza - Kwanan wata - Gudanar da Bincike (Canza - Kwanan wata - Fassara). Sa'an nan mu gyara code a cikin dabara bar ta maye gurbin aikin Kwanan wata.Daga on Kwanan wata.Dagadon kar a rasa ƙimar lokaci:
- Sa'an nan, ta danna dama a kan taken, mun raba ginshiƙi KWATANCIN tare da oda sigogi ta mai raba - alama n, amma a lokaci guda, a cikin sigogi, za mu zaɓi rarraba zuwa layuka, kuma ba cikin ginshiƙai:
- Har yanzu, muna raba ginshiƙin da aka samu zuwa guda biyu daban-daban - ma'auni da ƙimar, amma ta alamar daidai.
- Zaɓin shafi BAYANI.1 yi convolution, kamar yadda muka yi a baya, tare da umarni Canji - Rukunin Pivot (Canja - Pivot shafi). Ƙimar ƙimar a cikin wannan yanayin zai zama ginshiƙi tare da ƙimar sigina - BAYANI.2 Tabbatar zaɓar aiki a cikin sigogi Kada ku tara (Kada a tara):
- Ya rage don saita tsarin don duk ginshiƙai da sake suna kamar yadda ake so. Kuma zaku iya loda sakamakon zuwa Excel tare da umarnin Gida - Rufe kuma Loda - Rufe kuma Load a… (Gida - Rufe & Load - Rufe & Loda zuwa…)
Kuma ga jerin umarni da aka ɗora a cikin Excel daga Kalanda Google:
A nan gaba, lokacin canzawa ko ƙara sabbin umarni zuwa kalanda, zai isa kawai sabunta buƙatar mu tare da umarnin Bayanai - Wartsake Duk (Bayanai - Refresh Duk).
- Kalandar masana'anta a cikin Excel an sabunta ta daga intanet ta hanyar Query Query
- Canza shafi zuwa tebur
- Ƙirƙiri bayanai a cikin Excel