Mukati
Mutsetse unodimburwa mukati mesero imwe chete, inowedzerwa uchishandisa nzira yekudimbudzira kiibhodhi alt+pinda chinhu chakajairika uye chakajairika. Dzimwe nguva ivo vanogadzirwa nevashandisi pachavo kuti vawedzere runako kune refu mavara. Dzimwe nguva kuchinjirwa kwakadaro kunowedzerwa otomatiki kana uchiburitsa data kubva kune chero mapurogiramu anoshanda (hello 1C, SAP, nezvimwewo) Dambudziko nderekuti iwe haufanirwe kungoyemura matafura akadaro, asi kushanda nawo - uye ipapo aya asingaoneki mavara kutamiswa anogona kuve dambudziko. Uye vangasave - kana iwe uchiziva maitiro ekuzvibata nemazvo.
Ngatitarisei nyaya iyi zvakadzama.
Kubvisa kutyora kwemitsara nekutsiva
Kana isu tichida kubvisa ma hyphens, saka chinhu chekutanga chinouya mupfungwa ndeye classic "tsvaga uye kutsiva" maitiro. Sarudza mavara uye wobva wafonera hwindo rekutsiva nekiibhodhi yekudimbudzira Ctrl+H kana kuti vachishandisa Imba - Tsvaga uye Sarudza - Tsiva (Kumba - Tsvaga & Sarudza - Tsiva). Imwe kusawirirana - hazvina kunyatsojeka kuti ungapinda sei mundima yepamusoro Kuwana (Wana chii) yedu isingaonekwi mutsetse break character. alt+pinda pano, zvinosuruvarisa, haichashandi, kukopa chiratidzo ichi zvakananga kubva muchitokisi uye kuchinamira pano zvakare chinokundikana.
Mubatanidzwa uchabatsira Ctrl+J - ndiyo imwe nzira alt+pinda muExcel dialog box kana minda yekuisa:
Ndokumbira utarise kuti mushure mekuisa iyo inopenya cursor mumunda wepamusoro uye wodzvanya Ctrl+J - hapana chichaonekwa mumunda pachawo. Usatya - izvi zvakajairika, chiratidzo hachionekwe 🙂
Kumunda wepasi Substitute (Siva ne) kana usapinda chero chinhu, kana kupinda munzvimbo (kana isu tichida kwete chete kubvisa hyphens, asi kutsiva iwo nenzvimbo kuitira kuti mitsetse irege kunamatira pamwechete kuita imwechete). Ingobaya bhatani Tsiva zvinhu zvose (Siva Zvose) uye ma hyphens edu achanyangarika:
bvute: mushure mekuita kutsiva kwakapinda ne Ctrl+J hunhu husingaoneki hunoramba huri mumunda Kuwana uye inogona kukanganisa mune ramangwana - usakanganwa kuidzima nekuisa chitubu mundima iyi uye kakawanda (nokuvimbika) kudzvanya makiyi. Kudzima и Backspace.
Kubvisa mutsara kutyora neformula
Kana iwe uchida kugadzirisa dambudziko nemafomula, saka unogona kushandisa yakavakirwa-mukati basa PRINDA (CLEAN), iyo inogona kujekesa mavara eese asingadhindike, kusanganisira yedu ill-fated line breaks:
Iyi sarudzo, zvisinei, haisi nguva dzose yakakodzera, nokuti mitsetse mushure mekushanda uku inogona kubatanidzwa pamwe chete. Kuti izvi zvisaitike, haufanirwe kungobvisa hyphen chete, asi shandura iyo nenzvimbo (ona ndima inotevera).
Kutsiva kutyora kwemitsara neformula
Uye kana iwe uchida kwete kungodzima, asi kutsiva alt+pinda pane, semuenzaniso, nzvimbo, ipapo imwe, yakawedzera kuomarara kuvaka ichadikanwa:
Kuisa hyphen isingaoneki tinoshandisa basa racho SYMBOL (CHAR), iyo inoburitsa hunhu nekodhi yayo (10). Uye ipapo basa SUBSTITUTE (SUBSTITUTE) inotsvaga yedu hyphens mune sosi data uye inovatsiva nemamwe chero zvinyorwa, semuenzaniso, nenzvimbo.
Kamurana kuita mbiru nekutyora mutsetse
Kujairwa kune akawanda uye anobatsira kwazvo chishandiso Mavara nezvikamu kubva kune tab Data (Data - Chinyorwa kune Columns) inogona zvakare kushanda zvakanaka nekutsemuka kwemitsara uye kupatsanura zvinyorwa kubva musero rimwe kuita akati wandei, kutyora nayo alt+pinda. Kuti uite izvi, padanho rechipiri rewizard, unofanirwa kusarudza musiyano weiyo tsika delimiter hunhu mwe (Tsika) uye shandisa nzira yekudimbudzira yatinotoziva Ctrl+J seimwe nzira alt+pinda:
Kana data rako ringave riine mitsara yakati wandei mumutsara, saka unogona "kupunza" nekubatidza bhokisi rekutarisa. Bata delimiters dzinotevedzana seimwe (Bata zvinotevedzana delimiters seimwe).
Mushure mekunge mavharidza Next (Inotevera) uye nekufamba nematanho ese matatu ewizard, tinowana mhedzisiro yaunoda:
Ndokumbira utarise kuti usati waita oparesheni iyi, zvinodikanwa kuisa nhamba yakakwana yemakoramu asina chinhu kurudyi kwekakamurwa koramu kuitira kuti mhedzisiro yacho irege kupfuudza kukosha (mitengo) yaive kurudyi.
Gura mumitsara neAlt + Pinda kuburikidza nePower Query
Rimwe basa rinonakidza nderekukamura zvinyorwa zvakawanda kubva pasero rega rega kwete kuita makoramu, asi kuita mitsetse:
Zvinotora nguva yakareba kuita izvi nemaoko, zvakaoma nemafomula, kwete munhu wese anogona kunyora macro. Asi mukuita, dambudziko iri rinoitika kakawanda kupfuura zvatinoda. Mhinduro iri nyore uye yakapusa ndeye kushandisa iyo Power Query yekuwedzera-mukati yebasa iri, iro rakavakwa muExcel kubva 2016, uye kune ekare mavhezheni 2010-2013 anogona kudhawunirodha mahara kubva kuMicrosoft webhusaiti.
Kurodha iyo sosi data muPower Query, unofanira kutanga waishandura kuita "smart tafura" ine keyboard shortcut. Ctrl+T kana nebhatani Fomati setafura Tab Home (Kumba - Fomati seTafura). Kana nekuda kwechimwe chikonzero iwe usingadi kana usingakwanisi kushandisa "smart tables", saka unogona kushanda ne "mapenzi" avo. Muchiitiko ichi, ingosarudza iyo yekutanga chiyero uye upe zita pane iyo tab Mafomula - Maneja Wezita - Nyowani (Mafomula - Maneja Wezita - Nyowani).
Pashure paizvozvo, pane tab Data (kana iwe uine Excel 2016 kana gare gare) kana pane iyo tab Simba Query (kana uine Excel 2010-2013) unogona kudzvanya bhatani Kubva patafura/renji (Kubva Tafura/Range)kurodha tafura yedu muPower Query edhita:
Mushure mekurodha, sarudza koramu ine multiline mavara mumaseru uye sarudza rairo paMain tab Split Column – By Delimiter (Kumba - Split Column - By delimiter):
Zvingangoita, Power Query inozoona otomatiki musimboti wekukamura uye kutsiva chiratidzo pachacho #(lf) isingaonekwe mutsara wekudyisa hunhu (lf = mutsara wekudya = mutsara wekudya) mumunda wekuparadzanisa wekuisa. Kana zvichidikanwa, mamwe mavara anogona kusarudzwa kubva pane yekudonhedza pasi pasi pehwindo, kana ukatanga watarisa bhokisi. Kupatsanura neakakosha mavara (Yakamurwa nemabhii akakosha).
Saka kuti zvinhu zvose zvakakamurwa kuita mitsara, uye kwete makoramu - usakanganwa kushandura sarudzo Rows (Nemitsara) muboka repamusoro sarudzo.
Chasara kudzvanya OK uye tora zvaunoda:
Tafura yakapedzwa inogona kudzoserwa papepa uchishandisa murairo Vhara uye takura - Vhara uye rodha mukati... Tab Home (Kumba - Vhara & Rodha - Vhara & Rodha ku…).
Izvo zvakakosha kuti uzive kuti kana uchishandisa Power Query, iwe unofanirwa kuyeuka kuti kana sosi data rachinja, mhedzisiro haigadziriswe otomatiki, nekuti. idzi hadzisi mafomula. Kuti uvandudze, unofanirwa kudzvanya-kurudyi patafura yekupedzisira pane pepa uye wosarudza murairo Gadziridza & Chengeta (Refresh) kana kudzvanya bhatani Gadzirisa Zvese Tab Data (Data - Zorora Zvese).
Macro yekukamura kuita mitsetse neAlt + Enter
Kuti tipedze mufananidzo, ngatitaureiwo mhinduro yedambudziko rekare nerubatsiro rwe macro. Vhura iyo Visual Basic Mharidzo uchishandisa bhatani rezita rimwe chete pane tab Developer (Mugadziri) kana zvidimburiko zve keyboard alt+F11. Muhwindo rinoonekwa, isa module nyowani kuburikidza nemenyu Pinza – Module uye kopira kodhi inotevera ipapo:
Sub Split_By_Rows() Dim cell As Range, n As Integer Set cell = ActiveCell For i = 1 To Selection.Rows.Count ar = Split(cell, Chr(10))' sarudza nhamba yezvimedu cell.Offset(1, 0 ). Resize(n, 1).EntireRow.Isa 'pinza mitsara isina chinhu pazasi pesero.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar)' pinda mukati mawo data uchibva pane array Set cell = cell.Offset(n + 1, 0) 'chinja uchienda kuchitokisi chinotevera Next i End Sub
Dzokera kuExcel uye sarudza maseru ane mavara mazhinji aunoda kupatsanura. Zvadaro shandisa bhatani macro Tab yokuvaka (Mugadziri - Macros) kana nzira yekudimbudzira keyboard alt+F8kumhanyisa iyo yakagadzirwa macro, iyo ichakuitira basa rese:
Voila! Vagadziri vezvirongwa, chokwadi, vanongori vanhu vane usimbe vangada kushanda nesimba kamwe chete vobva vaita chinhu 🙂
- Kuchenesa mavara kubva kujunk uye mamwe mavara
- Kutsiva zvinyorwa uye kubvisa nzvimbo dzisina kutyora neiyo SUBSTITUTE basa
- Maitiro ekutsemura mavara anonamira kuita zvikamu muExcel