Jenereta ya mawu kuchokera pazidutswa zoperekedwa

Posachedwapa, mnzanga wina adabwera kwa ine ndi pempho loti andithandizire kutulutsa mawu onse omwe ali ndi mawu operekedwa. Mavuto amtunduwu amatha kubwera polemba mindandanda ya mawu osakira ndi mawu otsatsa pa intaneti ndi kukwezedwa kwa SEO, mukafunika kudutsa zololeza zonse zomwe zingalole mawu posaka:

Mu masamu, ntchito imeneyi amatchedwa Cartesian mankhwala. Tanthauzo lovomerezeka ndi motere: Cartesian product of sets A ndi B ndi gulu la awiriawiri onse, chigawo choyamba chomwe chimayikidwa A, ndipo chigawo chachiwiri ndi chokhazikitsa B. Komanso, zinthu za seti zikhoza kukhala zonse. manambala ndi malemba.

Kumasuliridwa m'chinenero cha anthu, izi zikutanthauza kuti ngati "A" tili ndi, mwachitsanzo, mawu akuti "woyera" ndi "wofiira", ndi "BMW" ndi "Mercedes" monga "BMW", ndiye pambuyo pa malonda a Cartesian a seti ziwirizi. get on the output ndi gulu la mitundu yonse ya mawu, opangidwa ndi mawu amndandanda onse awiri:

• woyera bmw
• red bmw
• white Mercedes
• red mercedes
  

… ie zomwe tikusowa. Tiyeni tiwone njira zingapo zothetsera vutoli mu Excel.

Njira 1. Zolemba

Tiyeni tiyambe ndi mafomula. Tiyerekeze kuti monga deta yoyambira tili ndi mindandanda itatu ya mawu oyambira m'mizati A, B ndi C, motsatana, komanso kuchuluka kwa zinthu pamndandanda uliwonse kumatha kusiyana:

Choyamba, tiyeni tipange mizati itatu yokhala ndi ma indices, mwachitsanzo manambala okhazikika a mawu kuchokera pamndandanda uliwonse m'mitundu yonse yotheka. Mzere woyamba wa mayunitsi (E2: G2) udzalowetsedwa pamanja, ndipo kwa ena onse tidzagwiritsa ntchito njira iyi:

Lingaliro apa ndi losavuta: ngati index yomwe ili mu selo yapitayi yapita kale kumapeto kwa mndandanda, ndiye kuti ndi yofanana ndi chiwerengero cha zinthu zomwe zili pamndandanda wowerengedwa ndi ntchitoyo. COUNT (COUNTA), kenako timayambanso kuwerengera. Kupanda kutero, timawonjezera indexyo ndi 1. Samalani kwambiri kukonza mwanzeru milingo ndi zizindikiro za dola ($) kuti mutha kukopera fomula pansi ndi kumanja.

Tsopano popeza tili ndi manambala a ordinal a mawu omwe tikufuna pamndandanda uliwonse, titha kuchotsa mawuwo pogwiritsa ntchito ntchitoyi INDEX (INDEX) mu magawo atatu osiyana:

Ngati simunakumanepo ndi izi pantchito yanu m'mbuyomu, ndiye ndikukulangizani kuti muwerenge mozungulira - zimathandizira nthawi zambiri ndipo ndizothandizanso (komanso zochulukirapo!) VPR (VLOOKUP).

Pambuyo pake, zimangotsala pang'ono kumata mzere wa zidutswa zomwe zatsatiridwa ndi mzere pogwiritsa ntchito chizindikiro cha concatenation (&):

... kapena (ngati muli ndi mtundu waposachedwa wa Excel) wokhala ndi ntchito yothandiza GWANIZANI (TEXTJOIN), yomwe imatha kumata zonse zomwe zili m'maselo otchulidwawo kudzera pagulu lopatukana (malo):

Njira 2. Kupyolera mu Kufufuza Mphamvu

Power Query ndi chowonjezera champhamvu cha Microsoft Excel chomwe chimagwira ntchito zazikulu ziwiri: 1. kukweza deta mu Excel kuchokera pafupifupi gwero lililonse lakunja, ndi 2. mitundu yonse ya kusintha kwa matebulo odzaza. Power Query idamangidwa kale mu Excel 2016-2019, ndipo ya Excel 2010-2013 imayikidwa ngati chowonjezera chosiyana (mutha kutsitsa patsamba lovomerezeka la Microsoft kwaulere). Ngati simunayambe kugwiritsa ntchito Power Query mu ntchito yanu, ndiye nthawi yoti muganizirepo, chifukwa masinthidwe monga omwe tafotokozawa amachitika kumeneko mosavuta komanso mwachilengedwe, mumayendedwe angapo.

Choyamba, tiyeni titengere mindandanda yamagwero ngati mafunso osiyana mu Power Query. Kuti muchite izi, patebulo lililonse, chitani izi:

1. Tiyeni tisandutse matebulo kukhala “anzeru” ndi batani Pangani ngati tebulo tsamba Kunyumba (Kunyumba - Pangani Monga Table) kapena njira yachidule ya kiyibodi Ctrl+T. Tebulo lililonse lidzapatsidwa dzina Gawo 1,2,3, XNUMX, XNUMX…, zomwe, komabe, zitha kusinthidwa ngati zingafunike pa tabu Constructor (Kapangidwe).
   
2. Mukakhazikitsa cell yogwira patebulo, dinani batani Kuchokera patebulo (Kuchokera patebulo) tsamba Deta (Tsiku) kapena pa tabu Kufunsa Mphamvu (ngati mwayiyika ngati chowonjezera chapadera cha Excel 2010-2013).
3. Pazenera la query editor lomwe limatsegulidwa, sankhani lamulo Kunyumba - Tsekani ndikunyamula - Tsekani ndikulowetsani… (Kunyumba — Tsekani & Katundu — Tsekani & Kwezani ku..) ndiyeno mwina Ingopangani kulumikizana (Pangani kulumikizana kokha). Izi zidzasiya tebulo lodzaza kukumbukira ndikulola kuti lipezeke m'tsogolomu.

Ngati muchita zonse molondola, ndiye kuti zomwe zili mugawo loyenera ziyenera kukhala zopempha zitatu mumayendedwe Kulumikizana kokha ndi mayina athu a tebulo:

Tsopano dinani kumanja pa funso loyamba ndikusankha lamulo Lumikizani (Buku)kuti mupange kopi yosinthidwa, ndiyeno yonjezerani ndime yowonjezera ku deta kudzera mu lamulo Kuwonjezera ndime ž - Mzere wachizolowezi (Onjezani Mzere -ž Mzere Wamakonda). Pazenera lolowera fomula, lowetsani dzina lagawo latsopano (mwachitsanzo, Fragment2) ndi mawu osavuta kwambiri ngati chilinganizo:

=Tebulo2

… mwachitsanzo, mwa kuyankhula kwina, dzina la funso lachiwiri:

Pambuyo pang'anani OK tiwona gawo latsopano, m'selo iliyonse yomwe padzakhala tebulo lokhala ndi ziganizo kuchokera patebulo lachiwiri (mutha kuwona zomwe zili m'matebulowa ngati mutadina kumbuyo kwa selo pafupi ndi mawuwo. Table):

Ikupitilirabe kukulitsa zonse zomwe zili m'matebulo osungidwawa pogwiritsa ntchito batani lokhala ndi mivi iwiri pamutu wagawo lotsatira ndikuchotsa. Gwiritsani ntchito dzina lazagawo loyambirira ngati choyambirira (Gwiritsani ntchito dzina lazachigawo choyambirira ngati choyambirira):

... ndipo timapeza zophatikizira zonse zomwe zingatheke m'magulu awiri oyambirira:

Komanso, zonse ndi zofanana. Onjezani gawo lina lowerengedwa ndi fomula:

=Tebulo3

..., ndikuwonjezeranso matebulo osungidwa - ndipo tsopano tili ndi njira zonse zololeza mawu kuchokera pamaseti atatu, motsatana:

Zimatsalira kusankha mizati yonse itatu kuchokera kumanzere kupita kumanja, kugwira Ctrl, ndi kugwirizanitsa zomwe zili mkati mwake zolekanitsidwa ndi mipata pogwiritsa ntchito lamulo Phatikizani mizati (Gwirizanitsani Mizati) kuchokera ku tabu Transformation (Sinthani):

Zotsatira zake zitha kutsitsidwanso papepala ndi lamulo lodziwika kale Kunyumba - Tsekani ndikunyamula - Tsekani ndikulowetsani… (Kunyumba — Tsekani & Katundu — Tsekani & Kwezani ku..):

Ngati m'tsogolomu chinachake chidzasintha m'matebulo athu omwe ali ndi zidutswa, ndiye kuti zidzakhala zokwanira kungosintha funso lomwe lapangidwa podina kumanja pa tebulo lotsatira ndikusankha lamulo. Sinthani & Sungani (Bwezeretsani) kapena mwa kukanikiza njira yachidule ya kiyibodi Ctrl+alt+F5.

• Kodi Power Query, Power Pivot, Power Map ndi Power BI ndi chiyani ndipo chifukwa chiyani amafunikira wogwiritsa ntchito Excel
  
• Kupanga Tchati cha Gantt mu Power Query
• Njira 5 zogwiritsira ntchito INDEX