Ang pag-uuri ng data ay isang kapaki-pakinabang at medyo karaniwang ginagamit na operasyon. Pagkatapos ng lahat, kapag nagtatrabaho ka malalaking volume impormasyon sa isang talahanayan, pagkatapos ay ang pag-aayos ng data sa loob nito ay nagiging isang napakahalagang gawain. Ang pag-uuri ng data sa isang Google Sheet ay nakakatulong na gawing maayos ang data at matukoy ang mga pattern dito. meron iba't ibang pamamaraan pagbubukod-bukod sa Google sheet mga talahanayan o sa isang napiling hanay.
Titingnan natin kung paano naiiba ang pag-uuri ng talahanayan sa pag-uuri ng hanay, kung paano ilarawan nang tama ang mga pamantayan sa pag-uuri, kung paano ka makakapag-uri-uri ayon sa ilan nang sabay-sabay Mga nagsasalita ng Google mga mesa. Tutuon din tayo sa mga paraan awtomatikong pag-uuri data kung magbabago sila.
Sa artikulong ito, matututunan mo kung paano pagbukud-bukurin ang data upang gawing mas madaling tingnan at ayusin ang iyong mga nilalaman ng talahanayan.
- Paano pagbukud-bukurin ang data sa Google Sheets
- ayusin ang buong sheet
- pag-uuri ng isang hanay
- pagbukud-bukurin ayon sa maraming column nang sabay-sabay
- awtomatikong pag-uuri
Paano pagbukud-bukurin ang data sa Google Sheets
Gumagamit ang Google Sheets ng dalawang uri ng pag-uuri:
- Pag-uuri ayon sa mga column
- Pag-uuri ng saklaw.
Ngayon ay pag-uusapan natin ang bawat isa sa kanila nang mas detalyado.
Kaya't ayusin natin ang ating talahanayan ayon sa hanay.
Pag-uuri ng isang sheet
Sa ganitong paraan ng pag-uuri, ang lahat ng data sa sheet ay iuutos. Mag-ingat kung marami kang mga talahanayan sa iyong worksheet!
Pansin! Bago pagbukud-bukurin ang buong talahanayan ayon sa column, tiyaking i-freeze ang mga heading ng column - kung hindi, pag-uuri-uriin din ang mga ito at mapupunta sa gitna ng iyong data!
Kaya, para maging kumportable para sa atin na tingnan ang pinagsunod-sunod na data, ayusin muna natin ang mga header ng column upang laging makita at hindi nakatago kapag tumitingin sa talahanayan.
Upang gawin ito, ilagay ang cursor sa anumang cell ng header ng talahanayan at piliin ang View - I-freeze mula sa menu at pagkatapos ay tukuyin ang kinakailangang bilang ng mga hilera.
Pagkatapos ay piliin kung aling column ang pag-uuri-uriin namin sa aming talahanayan.
Sa pamamagitan ng pag-click sa tatsulok sa tabi ng titik ng column, maaari kang pumili ng 2 pagpipilian sa pag-uuri - pataas o pababa. Pag-uuri-uriin nito ang buong talahanayan.
O maaari kang pumili mula sa menu ng Data - Pagbukud-bukurin ang sheet ayon sa hanay... at pagkatapos - kinakailangang opsyon pagbubukod-bukod.
Mahalagang tandaan dito na kung pinili mo ang buong column, magkakaroon ka ng opsyong pag-uri-uriin ang hanay ng data (na pag-uusapan natin sa ibaba).
Ngunit kung pipiliin mo ang "Pagbukud-bukurin ayon sa hanay...", pagkatapos ay ang napiling hanay lamang ang pagbubukud-bukod at ang natitirang bahagi ng data ay mananatiling hindi magbabago. Masisira nito ang istruktura ng iyong data, at walang saysay na pag-uri-uriin ito sa ganitong paraan. Samakatuwid, mag-ingat!
Pag-uuri ng saklaw
Tingnan natin ang pangalawang paraan - pag-uuri ng saklaw.
Magiging kapaki-pakinabang ito kung mayroon kang ilang maliliit na talahanayan ng data sa iyong Google Sheets, ngunit kailangan mo lang ayusin ang isa sa mga ito.
Ang pag-uuri ng isang hanay ay hindi makakaapekto sa data ng iba pang mga talahanayan na matatagpuan sa worksheet.
Gamitin ang iyong mouse upang piliin ang hanay ng mga cell na gusto mong muling ayusin.
Pagkatapos ay mayroon kang 2 pagpipilian:
- I-right-click at piliin ang “Range sorting” mula sa lalabas na menu.
- Sa menu, piliin ang Data - Pag-uuri ng Saklaw.
Magbubukas karagdagang window na may mga setting ng pag-uuri.
Dito maaari mong tukuyin na ang hanay ng data na iyong pinili ay naglalaman ng mga header ng column. Sa aming halimbawa, hindi namin pinili ang mga header ng column, kaya iniwan naming walang laman ang checkbox. Ibig sabihin, pag-uuri-uriin namin ang buong napiling hanay. Kung nilagyan mo ng check ang kahong ito, ang unang hilera ng napiling hanay ay hindi pagbubukod-bukod.
Maaari mo ring piliin kung aling column ang pag-uuri-uriin sa pamamagitan ng pag-click sa drop-down na icon. Pinili namin ang column B.
Dito, bilang default, ang pagkakasunud-sunod ng pag-uuri ay "pataas" mula A hanggang Z. Kung kinakailangan, maaari mo itong baguhin sa kabaligtaran - mula Z hanggang A.
Pagbukud-bukurin ang data sa isang Google Sheet ayon sa maraming column
Kung kailangan mong pag-uri-uriin ayon sa ilang mga parameter, pagkatapos ay sa mga setting ng pag-uuri maaari kang magdagdag ng isa pa o kahit na ilang mga haligi.
Upang gawin ito, gamitin ang item na "Magdagdag ng isa pang column para sa pag-uuri" sa mga setting ng pag-uuri. Pagkatapos ay piliin ang gustong column mula sa drop-down na listahan.
Para sa bawat column, maaari mong piliin ang pagkakasunod-sunod ng pag-uuri - pataas o pababa.
Bilang resulta, ang pag-uuri ay isasagawa nang sunud-sunod - una sa unang hanay, pagkatapos ay sa pangalawa, at iba pa (sa aming kaso, una sa pamamagitan ng B, pagkatapos ay sa pamamagitan ng D).
Dito sa aming halimbawa, inayos muna namin ang mga order ayon sa customer at pagkatapos ay ayon sa produkto. Bilang resulta, nakikita namin ang mga order ng bawat customer na nakaayos ayon sa produkto.
Awtomatikong pag-uuri ng Google Sheets
Sabihin nating mayroon kaming isang talahanayan na may pinagsunod-sunod at nakaayos na impormasyon. Ngunit paano kung ang bagong data ay idinagdag sa talahanayan? Pagkatapos ng lahat, hindi na sila maaayos, at kailangan nating ulitin ang pag-uuri?
- Gamit ang SORT function.
- Paggamit ng PivotTable.
- Gamit ang Google Scripts (hindi namin ito isasaalang-alang dito, dahil available lang ito sa mga developer).
Para sa mga walang kasanayan sa programming, ang paggamit ng SORT function ay ang pinakamadaling solusyon.
Upang gawin ito, gagawa kami ng isa pang sheet, na matatawag naming "Pag-uuri". Ilalagay namin ang aming pinagsunod-sunod na data sa sheet na ito.
Ang aming data ay matatagpuan sa "Data" sheet.
Kopyahin ang mga heading ng column mula sa sheet na "Data" papunta sa row A ng sheet na "Pag-uuri".
Sa cell A2 ng sheet na "Pag-uuri", isulat ang formula ng function:
SORT ("Data"!A2:G , 2 , 2 )
Narito ang "Data"!A2:G ay ang mga coordinate ng hanay kung saan kukunin ang data para sa pag-uuri.
Pakitandaan na tinutukoy lang namin ang kaliwang itaas na cell at ang pinakakanang column. Nagbibigay ito sa amin ng kakayahang magdagdag ng bagong data sa aming orihinal na talahanayan nang hindi binabago ang formula ng pag-uuri.
2 - serial number hanay sa tinukoy na saklaw, kung saan pag-uuri-uriin natin. Sa aming kaso, pag-uuri-uriin namin ang mga benta ayon sa pangalan ng customer.
2 - pagkakasunud-sunod ng pagkakasunud-sunod. 2 ay nangangahulugan ng pag-uuri sa pataas na pagkakasunud-sunod, mula sa pinakamaliit hanggang sa pinakamalaki. Ang ibig sabihin ng 1 ay pag-uuri sa pababang pagkakasunud-sunod, mula sa pinakamalaki hanggang sa pinakamaliit.
Bilang resulta, sa sheet na "Pag-uuri" nakatanggap kami ng data ng mga benta na pinagsunod-sunod ng mga customer.
Upang subukan ang mga kakayahan ng awtomatikong pag-uuri sa Google Sheets, magdagdag ng isa pang row na may data ng mga benta sa orihinal na talahanayan sa sheet na "Data".
Ang entry na ito ay awtomatikong lalabas sa talahanayan sa sheet na "Pag-uuri".
Maaari ka ring gumamit ng pivot table para sa pag-uuri. Hindi rin ito nakakaapekto sa umiiral na data at inilalagay sa isang hiwalay na sheet.
Gamitin ang iyong mouse upang i-highlight ang mga column ng talahanayan na gusto mong isama sa PivotTable, at pagkatapos ay i-click ang Data - PivotTable mula sa menu. Mga detalye tungkol sa pagtatrabaho sa mga pivot table at ang kanilang mga kakayahan ay mababasa mo sa aming materyal.
Pinalabas namin bagong libro"Pagmemerkado ng nilalaman sa mga social network: Paano mapunta sa ulo ng iyong mga subscriber at mahalin sila sa iyong brand."
Nagpasya akong magsulat ng isang artikulo na makakatulong sa pag-master ng mga pinakasimpleng function ng Google Sheets. Dati napuno ako ng galit sa lahat ng may kinalaman mga spreadsheet, at ngayon ay nagtatrabaho sa kanila ay tila napaka-maginhawa at gumagana. Hayaan akong magpareserba kaagad: ang halimbawa ay naglalaman ng isang maliit na listahan ng mga query at ganap na random na mga halaga.Upang magsimula, dahil ang mga talahanayan ay madalas na naglalaman ng maraming data sa anyo ng mga column at row, para hindi malito, sasabihin ko sa iyo kung paano magtalaga ng column o row sa Google Sheets na magiging navigational.
Piliin ang linya:
- I-click ang "View"
- Itinuro namin ang "pin"
- I-click ang “1 row” (o column, depende sa kung ano ang kailangan mo)
Bilang resulta, nakakakuha kami ng isang linya na kasama namin, na tumutulong sa amin na hindi malito sa isang malaking mesa.
Pag-uuri sa Google Sheets
Hindi mo sorpresahin ang sinuman sa regular na pag-uuri, kaya sasabihin ko sa iyo ang tungkol sa nakakalito na pag-uuri. Sabihin nating mayroon tayong column mga pangunahing katanungan. Ngunit ang isa sa mga serbisyo ay nawala ang kaugnayan nito at kailangan nating alisin ang mga kaukulang kahilingan. Pero paano kung magkaiba ang mga hiling, marami sila at pinaghalo-halo?
Sa Google Sheets, at sa Excel din, maaari mong baguhin ang kulay ng isang cell na naglalaman ng isang partikular na stop word. Susunod, pag-uuri-uriin namin ang talahanayan ayon sa kulay at ang lahat ng mga cell na tatanggalin ay malilinya sa isang hilera sa harap mo. Sabihin nating kailangan kong tanggalin ang lahat ng query na may salitang "court" mula sa column ng query.
Mga Tagubilin:
- Piliin ang buong hanay sa pamamagitan ng pag-click sa cell na may titik, sa aming kaso "A".
- I-click i-right click mouse sa napiling column at piliin ang “Conditional formatting.
May lalabas na maliit na menu sa kanan kung saan makakagawa tayo ng panuntunan.
Sa menu:
- Mag-click sa field sa ilalim ng mga salitang "Pag-format ng mga cell" at hanapin ang linyang "Naglalaman ang teksto" doon.
- Ipasok ang ligtas na salita sa lalabas na butas. Mas mainam na walang pagtatapos, dahil anumang bagay ay maaaring mangyari sa Russian.
- Sa pamamagitan ng paggamit karaniwang menu piliin ang kulay ng cell at kung ano pa ang gusto mo.
- I-click ang "Tapos na".
Ngayon ay i-install natin ang karagdagan para sa aming talahanayan. Para sa layuning ito sa tuktok na menu Nahanap namin ang pindutan ng parehong pangalan na "Mga Add-on"↣ "Mag-install ng add-on." Nag drive kami papasok search bar"Pagbukud-bukurin ayon sa kulay" at i-install ang extension, tulad ng sa figure:
Hindi ko iginigiit ang partikular na karagdagan na ito, nasubok lang ito at gumagana. Ngayon ang aming koleksyon ay may extension na nag-uuri ng mga halaga ayon sa kulay ng cell.
I-click ang “Mga Add-on” ↣ “Pagbukud-bukurin ayon sa kulay” at pumili ng anuman mula sa listahan. Voila. Maaaring tanggalin.
Maghanap at mag-alis ng mga duplicate sa Google Sheets
Kadalasan, ang mga dobleng halaga ay maaaring nakakainis. Mayroong dalawang paraan upang makahanap ng mga duplicate. Ang una ay napaka-simple.
Sa isang walang laman na cell, ilagay ang function =(UNIQUE (A2:A1000)at kumuha ng listahan ng mga cell na may natatanging nilalaman:
Ang pamamaraang ito sobrang sensitibo sa case at space!
Ang ikalawang paraan ay nagsasangkot ng pag-install ng isa pang add-on na tinatawag.
* Sa halip na i-customize ay magkakaroon ka asul na pindutan+Libre
Ang application ay medyo malakas. Maaari itong maghanap ng mga duplicate sa tiyak na saklaw o ihambing ang mga column at agad na magsagawa ng anumang mga aksyon sa kanila:
- Kopyahin/ilipat ang mga duplicate sa isang hiwalay na column
- Kopyahin/ilipat ang mga duplicate sa isang hiwalay na talahanayan
- Walang awa na alisin ang mga duplicate
- I-highlight ang mga ito ng kulay
Magtutuon kami sa pag-highlight na may kulay. Programa para sa Ingles, ilong gamit ang Google Tiyak na malalaman ito ng isang tagasalin at ilang pagsubok.
Kaya, piliin ang column, pumunta sa Mga add-on ↣ Alisin ang Mga Duplicate ↣ Maghanap ng mga duplicate o natatangi.
nakikita natin:
Dito maaari naming tukuyin ang hanay ng interes nang manu-mano o sa pamamagitan ng unang pagpili gustong lugar, awtomatikong itatakda ang hanay. Sa pamamagitan ng paglalagay ng check sa kahon sa tabi ng Lumikha ng backup, gagawa kami ng kopya ng talahanayang binago. I-click ang “Next”.
Isang pagpipilian ang lilitaw sa harap natin. Makakahanap tayo ng alinman sa mga duplicate o, sa kabaligtaran, natatanging mga cell. Pinipili namin ang mga duplicate at nahaharap kami sa isa pang pagpipilian. Kung pipiliin natin kasamaMga unang pangyayari, pagkatapos ay makakahanap tayo ng mga duplicate, KASAMA ang kanilang unang pagbanggit, sa halos pagsasalita, ang orihinal. Pipiliin naming maghanap lang ng mga duplicate, hindi kasama ang unang pagbanggit, dahil kailangan naming umalis sa isang cell. I-click ang susunod.
Kung pipili kami ng hanay na "may interes", habang nakakaapekto rin sa mga bakanteng column, tiyaking lagyan ng check ang checkbox na Laktawan ang mga walang laman na cell. sa lahat, hakbang na ito nilikha upang alisin ang mga cell ng header. Sa aming halimbawa mayroon kaming pamagat, kaya iniiwan namin ang mga checkbox. Susunod!
Panghuling slide. Hindi na tayo magtatagal dito, ngunit pipiliin kaagad ang Punan ng kulay. I-click ang Tapusin at tingnan ang bilang ng mga duplicate na makikita sa napiling hanay.
Space sensitive ang padding!
Ngayon ay maaari na nating ayusin ang ating mga duplicate ayon sa kulay gamit ang unang paraan at suriin ang pangangailangan para sa pag-alis.
Conditional Formatting sa Google Sheets
Sa aming halimbawa, mayroong ilang mga column na maaaring magamit upang masuri ang pagiging epektibo ng query. Halimbawa, hindi namin gustong gumamit ng mga query na ang dalas ay mas mababa sa 100. Maaari naming i-highlight ang lahat ng hindi angkop na mga cell na may kulay.
- Piliin ang column na may mga value
- I-right click ↣ conditional formatting
- Format ng cell: "Mas mababa sa"
- Ipasok ang kinakailangang halaga sa butas
- Piliin ang kulay/timbang/background at i-click ang “Tapos na”
Ngayon sa tulong na katutubong paraan pag-uuri ayon sa kulay, sinusuri namin ang mga kahilingan nang hindi sapat ang dalas at, kung kinakailangan, tanggalin ang mga ito.
Gamit ang parehong prinsipyo, maaari kang gumawa ng maraming bagay sa mga resultang listahan. Halimbawa, sa pamamagitan ng pag-format ng mga cell gamit ang panuntunang "naglalaman ng teksto," maaari mong i-highlight at pag-uri-uriin ang lahat ng geo-independent na query at alisin ang mga ito. At iba pa. Ang natitira ay iyong imahinasyon lamang, at kung ito ay nagiging masyadong ligaw, piliin ang "Iyong formula" sa "Pag-format ng Cell" at maging malikhain!
Mga chart sa Google Sheets
Para sa dessert. Upang ipakita ang lahat nang maganda, gamitin ang intuitive Pag-andar ng Google Mga talahanayan - pagpasok ng isang diagram. Walang paliwanag na kailangan dito, subukan lang!
Konklusyon
Sa pangkalahatan, wala at wala akong masyadong pagmamahal sa mga produkto ng Google. Ngunit lubos akong nagpapasalamat sa kanila para sa serye ng docs.google.com. Sa sandaling umupo ako at hinalungkat ang mga produktong ito, dumating ako sa konklusyon na hindi na ako mabubuhay nang walang mga talahanayan at mga dokumento, kailangan ko lang malaman ito. Isang makabuluhang plus ay isang add-on para sa Google Sheets, na ina-update at makabuluhang pinapataas ang mga kakayahan ng serbisyo. Sayang naman at medyo malinaw kapaki-pakinabang na mga artikulo sa paksang ito. Susubukan kong magsulat ng pangalawang bahagi sa nakikinita na hinaharap, kung saan susuriin ang mas kumplikadong mga circuit. Sa isip, makakapaglunsad ka ng isang linya ng mga kapaki-pakinabang na artikulo sa paksa kung paano magtrabaho sa Google Sheets, mga add-on, mga formula at handa na mga solusyon para sa pang-araw-araw na gawain.
(file-> lumikha ng isang kopya upang kopyahin ang file sa iyong Google Drive at makapag-edit).
Kung ang resulta ng formula ay sumasakop ng higit sa isang cell
Una tungkol sa mahalagang katangian pagpapakita ng mga resulta ng formula sa Google Sheets. Kung ang iyong formula ay nagbabalik ng higit sa isang cell, ang buong array ay ipapakita nang sabay-sabay at sasakupin ang kasing dami ng mga cell at column na kinakailangan nito (sa Excel, ito ay mangangailangan sa iyo na maglagay ng array formula sa lahat ng mga cell na ito). Tingnan natin kung paano ito gumagana sa sumusunod na halimbawa.
PAG-URI
Makakatulong ito sa iyong pag-uri-uriin ang isang hanay ng data sa pamamagitan ng isa o higit pang mga column at agad na ipakita ang resulta.Syntax ng function:
=SORT(data na pagbubukud-bukod; column_to_sort; ascending; [column_to_sort_2, ascending_2; ...])
Ang halimbawa sa screenshot sa ibaba, inilagay namin ang formula sa cell D2 lamang at pag-uri-uriin ang data ayon sa unang column (sa halip na TRUE/FALSE, maaari mong ilagay ang TRUE/FALSE).
(pagkatapos nito - mga halimbawa para sa mga setting ng talahanayan ng rehiyon ng Russia, maaaring baguhin ang mga setting ng pagpaparehistro sa menu ng file > mga setting ng talahanayan)
Paano magdagdag ng mga header ng talahanayan sa SORT?
Gamit ang mga kulot na braces (), gumawa kami ng array ng dalawang elemento, ang table header A1:B1 at ang SORT function, na naghihiwalay sa mga elemento sa isa't isa gamit ang semicolon.
Paano pagsamahin ang ilang hanay ng data at pag-uri-uriin (at higit pa)?
Tingnan natin kung paano natin pagsasamahin ang mga hanay para magamit sa mga function. Nalalapat ito hindi lamang sa SORT, maaaring gamitin ang diskarteng ito sa anumang mga function kung saan posible ito, halimbawa sa VLOOKUP o SEARCH.
Nahulaan na ng sinumang nagbabasa ng nakaraang halimbawa kung ano ang gagawin: buksan kulot na brace at kolektahin ang mga arrays na pagsasama-samahin, na pinaghihiwalay ang mga ito sa isa't isa gamit ang isang semicolon at isinasara ang curly brace.
Maaari mong pagsamahin ang mga array at huwag gamitin ang mga ito sa isang formula, ngunit ipakita lamang ang mga ito sa isang sheet, halimbawa, sa pamamagitan ng pagkolekta ng data mula sa ilang mga sheet ng iyong workbook. Para sa patayong pagsasama, kailangan mo lang mapanatili ang parehong bilang ng mga column sa lahat ng fragment (mayroon kaming dalawang column sa lahat ng dako).
At sa screenshot sa ibaba ay isang halimbawa ng isang pahalang na unyon, gumagamit ito ng backslash sa halip na isang semicolon at ang bilang ng mga hilera sa mga fragment ay dapat tumugma, kung hindi, ang formula ay magbabalik ng error sa halip na ang pinagsamang hanay.
(Ang mga semicolon at backslashes ay mga array element separator sa Russian regional settings, kung ang mga halimbawa ay hindi gumagana para sa iyo, pagkatapos ay sa pamamagitan ng table settings file, siguraduhing ito ang mayroon ka)
Well, ngayon bumalik tayo sa pahalang na hanay at ipasok ito sa SORT function. Pag-uuri-uriin namin ang data ayon sa unang column, sa pababang pagkakasunud-sunod.
Maaaring gamitin ang isang pagsali sa anumang function, ang pangunahing bagay ay upang mapanatili ang parehong bilang ng mga haligi para sa isang patayong pagsali o mga hilera para sa isang pahalang na pagsali.
FILTER
Sa FILTER maaari naming i-filter ang data sa pamamagitan ng isa o higit pang mga kundisyon at ipakita ang resulta sa isang worksheet o gamitin ang resulta sa isa pang function bilang isang hanay ng data.Syntax ng function:
FILTER(saklaw; kundisyon_1; [kondisyon_2; ...])
Isang kondisyon
Halimbawa, mayroon kaming isang talahanayan na may mga benta ng aming mga empleyado, ipakita natin ang data para sa isang empleyado mula dito.
Ipasok natin ang sumusunod na formula sa cell E3:
=FILTER(A3:C7;B3:B7=“Natalia Chistyakova”)
Pakitandaan na ang syntax ay bahagyang naiiba mula sa karaniwang mga formula, tulad ng SUMMESLIN, kung saan ang hanay ng kundisyon at ang kundisyon mismo ay ihihiwalay gamit ang semicolon.
Ang isang formula na ipinasok sa isang cell ay nagbabalik sa amin ng isang hanay ng 9 na mga cell na may data, ngunit pagkatapos ng mga halimbawa na may function na SORT, hindi na kami nagulat dito.
Bilang karagdagan sa equal sign (=), maaari mo ring gamitin ang >, >=,<>(hindi pantay)<, <=. Для текстовых условий подходят только = и <>, at para sa mga numero o petsa maaari mong gamitin ang lahat ng mga palatandaang ito.
Dalawang kundisyon at nagtatrabaho sa isang petsa
Gawin natin ang formula at magdagdag ng isa pang kundisyon dito, batay sa petsa ng mga benta, na iniiwan ang lahat ng mga benta simula 02/01/17
Ito ang magiging hitsura ng formula kung direktang ilalagay mo dito ang mga argumento ng kundisyon, bigyang-pansin ang conversion ng entry ng text ng petsa gamit ang DATEVALUE:
=FILTER(A3:C7;B3:B7=“Natalia Chistyakova”,A3:A7>=DATEVALUE(“01.02.17”))
O tulad nito, kung tumutukoy ka sa mga cell na may mga argumento:
=FILTER(A3:C7;B3:B7=I6;A3:A7>=J6)
Interactive na graph gamit ang FILTER at SPARKLINE
Alam mo ba kung paano mo ito magagamit? FILTER function? Sa halip na i-output ang resulta ng function sa isang worksheet, magagamit natin ito bilang data para sa isa pang function, gaya ng sparkline. Ang Sparkline ay isang function na bumubuo ng isang graph sa isang cell batay sa aming data ay may maraming mga setting, tulad ng uri ng graph, ang kulay ng mga elemento, ngunit ngayon ay hindi na kami magtatagal sa mga ito at gagamitin ang function nang walang karagdagang mga setting. . Lumipat tayo sa isang halimbawa.
Listahan ng dropdown. Magbabago ang aming iskedyul depende sa napiling empleyado sa drop-down list na ganito ang gagawin namin:
- piliin ang cell E2;
- menu Data > Data Check;
- mga panuntunan: Halaga mula sa hanay at sa hanay ay piliin ang hanay na may mga empleyado mula sa pinagmumulan ng data, huwag mag-alala na ang mga pangalan ay paulit-ulit, tanging mga natatanging halaga ang mananatili sa drop-down na listahan;
Ang cell na may drop-down list ay magiging kondisyon para sa FILTER formula, isulat natin ito.
At ilalagay namin ang formula na ito sa function ng SPARKLINE, na, batay sa natanggap na data, ay gumuhit ng graph sa cell.
=sparkline(FILTER(C3:C7,B3:B7=E2))
Ito ang hitsura nito sa dynamics:
Ngunit kung gaano ka eleganteng tingnan ang SPARKLINE karagdagang mga setting, V totoong trabaho, ipinapakita ng chart ang mga resulta ng aktibidad para sa isang araw, ang mga berdeng bar ay mga positibong halaga, ang mga pink na bar ay negatibo.
IMPORTRANGE
Para maglipat ng data mula sa isang file papunta sa isa pa, ginagamit ng Google Sheets ang IMPORTRANGE function.Sa anong mga kaso maaari itong maging kapaki-pakinabang?
- Kailangan mo ng up-to-date na data mula sa file ng iyong mga kasamahan.
- Gusto mong iproseso ang data mula sa isang file kung saan mayroon kang access na "View Only".
- Gusto mong mangolekta ng mga talahanayan mula sa ilang mga dokumento sa isang lugar upang maproseso o matingnan mo ang mga ito.
Ang syntax ng formula ay ang mga sumusunod:
IMPORTRANGE(spreadsheet key; range string)
IMPORTRANGE(key; range)
spreadsheet_key (key) - pagkakasunud-sunod ng mga character ng "key=" attribute (key) sa link sa talahanayan (pagkatapos ng "spreadsheets/…/").
Halimbawa ng formula na may susi:
=IMPORTRANGE("abcd123abcd123"; "sheet1!A1:C10")
Sa halip na table key ang maaari mong gamitin buong link upang idokumento:
Ipapakita ng iyong file ang hanay na A1:CM500 mula sa Sheet1 mula sa file na matatagpuan sa kaukulang link.
Kung nasa source file maaaring magbago ang bilang ng mga column o row, maglagay ng bukas na hanay sa pangalawang argumento ng function (tingnan din ang subsection na "Mga Saklaw ng form A2:A"), halimbawa:
Sheet1!A1:CM (kung idadagdag ang mga row)
Sheet1!A1:1000 (kung idadagdag ang mga column)
! Tandaan na kung naglo-load ka ng bukas na hanay (halimbawa, A1:D), hindi mo magagawang manu-manong magpasok ng anumang data sa mga column A:D sa file kung saan matatagpuan ang IMPORTRANGE formula (iyon ay, ang huling lokasyon kung saan na-load ang data). Ang mga ito ay, parang, "nakareserba" para sa buong bukas na hanay - pagkatapos ng lahat, ang sukat nito ay hindi alam nang maaga.
Mag-import ng pag-format mula sa isang source table
Gaya ng nabanggit na namin, nilo-load lang ng IMPORTRANGE ang data, ngunit hindi ang pag-format ng source table. Paano haharapin ito? Ihanda nang maaga ang lupa sa pamamagitan ng pagkopya ng pag-format mula sa orihinal na worksheet. Upang gawin ito, pumunta sa orihinal na sheet at kopyahin ito sa iyong workbook:
Matapos pindutin ang pindutan Kopyahin sa… piliin ang workbook kung saan ka mag-i-import ng data. Karaniwan kinakailangang talahanayan ay nasa tab Kamakailan(kung talagang nakatrabaho mo siya kamakailan).
Pagkatapos kopyahin ang sheet, piliin ang lahat ng data (sa pamamagitan ng pag-click sa kaliwang sulok sa itaas):
At i-click Tanggalin. Mawawala ang lahat ng data, ngunit mananatili ang pag-format. Ngayon ay maaari ka nang pumasok sa IMPORTRANGE function at makakuha ng buong tugma ng source sheet - parehong sa mga tuntunin ng data at sa mga tuntunin ng format:
IMPORTRANGE bilang argumento sa isa pang function
Ang IMPORTRANGE ay maaaring maging argumento sa isa pang function kung ang hanay na iyong ini-import ay umaangkop sa tungkuling iyon.
Isaalang-alang natin ang isang simpleng halimbawa - ang average na halaga ng mga benta mula sa isang hanay na matatagpuan sa isa pang dokumento.
Ito ang orihinal na dokumento. Hayaang idagdag ang data at kailangan namin ang average para sa mga benta sa 2016 (iyon ay, mula sa cell D2 hanggang sa ibaba).
Una naming ini-import ang hanay na ito:
At pagkatapos ay ginagamit namin ito bilang isang argumento sa AVERAGE function:
Nakukuha namin ang resulta, na ia-update kapag nagdagdag ng mga bagong row sa source file sa column D.
LARAWAN: pagdaragdag ng mga larawan sa mga cell
Binibigyang-daan ka ng function na IMAGE na magdagdag ng mga larawan sa mga cell ng Google Sheets.Ang function ay may sumusunod na syntax:
LARAWAN(URL, , , )
Ang URL ay ang tanging kinakailangang argumento. Ito ay isang link sa larawan. Ang link ay maaaring direktang tukuyin sa formula, gamit ang mga quote:
=IMAGE(“http://shagabutdinov.ru/wp-content/uploads/2015/12/Run-or-Die.jpg”)
Ang huling pagpipilian ay mas maginhawa sa karamihan ng mga kaso. Kaya, kung mayroon kang listahan ng mga aklat at mga link sa mga pabalat, sapat na ang isang formula upang ipakita ang lahat ng ito:
Ang argument ng mode ay maaaring tumagal ng apat na halaga (kung aalisin, ang default ang mauuna):
- ang imahe ay nakaunat sa laki ng cell habang pinapanatili ang aspect ratio;
- ang imahe ay nakaunat nang hindi pinapanatili ang aspect ratio, ganap na pinupunan
- ang imahe ay ipinasok sa orihinal na laki nito;
- tinukoy mo ang mga sukat ng imahe sa ikatlo at ikaapat na argumento ng at . , ayon sa pagkakabanggit, ay kailangan lamang kapag ang argument mode = 4. Ang mga ito ay tinukoy sa mga pixel.
Ang ikaapat na mode ay maaaring maging maginhawa kung kailangan mong piliin ang eksaktong sukat ng imahe sa mga pixel sa pamamagitan ng pagbabago mga parameter ng taas(taas) at lapad (lapad). Maa-update kaagad ang larawan.
Pakitandaan na sa lahat ng mga mode maliban sa pangalawa, maaaring may mga hindi napunong lugar sa cell, at maaari silang punan ng kulay:
GOOGLETRANSLATE at DETECTLANGUAGE: pagsasalin ng text sa mga cell
Ang Google Sheets ay may isang kawili-wiling GOOGLETRANSLATE function na nagbibigay-daan sa iyong magsalin ng teksto nang direkta sa mga cell:
Ang function syntax ay ang mga sumusunod:
GOOGLETRANSLATE (teksto,, )
ang teksto ay ang tekstong kailangang isalin. Maaari mong ilagay ang teksto sa mga panipi at isulat ito nang direkta sa formula, ngunit mas maginhawang sumangguni sa cell kung saan nakasulat ang teksto.
– ang wikang ating isinasalin;
– ang wikang ating isinasalin.
Ang pangalawa at pangatlong argumento ay tinukoy ng isang dalawang-digit na code: es, fr, en, ru. Maaari din silang tukuyin sa mismong function, ngunit maaari silang kunin mula sa cell, at sa wika pinagmulang teksto at maaari pa nga itong awtomatikong matukoy.
Ngunit paano kung nais nating isalin sa iba't ibang wika? At sa parehong oras, hindi namin nais na tukuyin nang manu-mano ang pinagmulang wika sa bawat oras?
Dito magagamit ang function na DETECTLANGUAGE. Mayroon lamang itong isang argumento - teksto, ang wika kung saan kailangang matukoy:
Tulad ng anumang tampok, ang kagandahan dito ay nasa automation. Mabilis mong mababago ang teksto o wika; mabilis na isalin ang isang parirala sa 10 wika at iba pa. Siyempre, naiintindihan namin na ito ay isang teksto mula sa isang online na tagasalin - ang kalidad ay magiging angkop.
Evgeniy Namokonov at Renat Shagabutdinov, at nagpapatakbo din kami ng isang telegram channel kung saan sinusuri namin ang iba't ibang mga kaso gamit ang Google Sheets, kung interesado ka, bumisita ka, ang link ay makikita sa aking profile.
Ang pag-uuri at pag-filter ng data ay kapaki-pakinabang at madalas na ginagamit na mga operasyon kapag nagtatrabaho sa anumang editor ng spreadsheet.
Pag-uuri nagbibigay-daan sa iyo na ayusin ang kaguluhan ng data at tukuyin ang mga pattern. Sa pamamagitan ng paggamit pagsala Maaari mo lamang piliin ang impormasyong interesado ka sa ngayon.
Pag-uuri ng data sa isang talahanayan
Sa Google Sheets, ang pag-uuri ay maaaring gawin ng lahat ng data sa sheet, o sa pamamagitan lamang ng mga halaga ng isang itinalagang hanay.
Ang "Global" na pag-uuri ay may katuturan lamang kung wala impormasyon sa teksto, (halimbawa, mga header ng column), mga chart at iba pang mga bagay. Kung hindi, magkakaroon ka ng gulo kung saan ito ay magiging mahirap na malaman ito. Ngunit kung kailangan mo pa rin ang feature na ito, gamitin ang mga command na “Pagbukud-bukurin ayon sa column...” mula sa menu na “Data”.
Tingnan natin ang pag-uuri ng saklaw.
Sabihin nating mayroon kaming talahanayan ng mga benta ayon sa buwan, at gusto naming ayusin ang data para sa Enero sa pataas na pagkakasunud-sunod.
- Piliin ang lahat ng mga cell na may impormasyon sa pagbebenta, kabilang ang mga pangalan ng produkto at data mula sa iba pang mga panahon. Kung pipiliin mo lang ang mga cell sa column ng Enero, ang column na iyon lang ang maaapektuhan ng mga pagbabago. Na nawawalan ng lahat ng kahulugan, dahil hindi natin maiuugnay ang dami ng naibenta sa mismong produkto.
- Susunod, pumunta sa menu na “Data” at pumunta sa item na “Pag-uuri-uriin...”.
- Magbubukas ang isang window ng mga setting, kung saan ipinapahiwatig namin ang column kung saan pag-uuri-uriin at ang direksyon nito (pataas o pababa).
Maaari kang magdagdag ng iba pang mga column kung kinakailangan. Pagkatapos ang pagproseso ay isasagawa nang sunud-sunod. Una sa unang kolum, pagkatapos ay sa pangalawa, atbp.
I-click ang button na “Pagbukud-bukurin”.
Ang resulta ay isang talahanayan na inayos ayon sa napiling column. Sa larawan sa itaas, ito ang column na "B". At mayroon kaming malinaw na larawan ng mga benta ng mga kalakal noong Enero.
Pag-filter ng data
Binibigyang-daan ka ng mga filter na itago ang hindi kinakailangang data at sa gayon ay ginagawang mas madali ang pag-navigate sa talahanayan. Isinasaad mo kung anong impormasyon ang gusto mong makita at inaalis ng system ang lahat ng hindi kinakailangang bagay. Tingnan natin ang pagtatrabaho sa mga filter gamit ang isang halimbawa.
Gamitin natin ang parehong talahanayan tungkol sa mga benta ayon sa buwan, pati na rin ang diagram nito.
Mag-iiwan lamang kami ng impormasyon sa mga vacuum cleaner at plantsa. Upang gawin ito:
- Pumunta kami sa column kung saan gusto naming pumili ng data.
- Sa menu na "Data," piliin ang command na "Filter" at may lalabas na maliit na arrow sa kasalukuyang cell, na nagpapahiwatig ng availability ng pag-filter.
- I-click ang arrow na ito at i-set up ang pagpili: mag-iwan ng checkmark sa tabi ng mga value na gusto mong iwan, ipahiwatig ang nais na opsyon sa pag-uuri.
- Mag-set up ng filter ayon sa iba pang mga field ng talahanayan. Sa kasong ito, ang mga filter ay magkakapatong sa isa't isa.
Ang resulta ay ang pagpapakita ng impormasyon na nakakatugon sa kondisyon. Ang mga field kung saan pinagana ang filter ay mamarkahan ng icon ng funnel.
Pakitandaan na awtomatikong itinayong muli ng system ang tsart ayon sa data sa talahanayan. Ito ay napaka-maginhawa kapag ang isang diagram ay nagpapakita ng isang malaking halaga ng impormasyon.
Upang i-disable ang filter, gamitin ang utos na I-disable ang Filter sa menu ng Data.
Nagse-save ng mga filter
Kung madalas kang gumagamit ng ilang partikular na filter, maaari mong i-save ang mga ito at maalala ang mga ito tamang sandali. Upang gawin ito:
- I-set up ang pagpili ng data.
- Pumunta sa menu na "Data" - "Filter" - "I-save bilang filter...".
- Magiging dark grey ang mga heading ng row at column, at lalabas ang mga tool sa pag-filter sa itaas ng talahanayan. Sa tulong nila, maaari mong tukuyin ang isang malinaw na pangalan para sa filter, baguhin ang hanay ng data, at tanggalin din ang isang naunang na-save na filter.
- Pagkatapos ng lahat ng mga setting, i-click ang krus sa kanan sa panel ng pag-edit ng filter.
Ngayon, upang ma-access ang naka-save na filter, mag-click sa pangalan nito sa menu ng Data. Maaari kang bumalik sa nakaraang view gamit ang "Orihinal" na command sa parehong menu sa seksyong "Mga Filter...".
