Yn ddiweddar, daeth ffrind i mi ataf gyda chais i helpu gyda chynhyrchu pob ymadrodd posibl yn cynnwys set o eiriau penodol. Gall problemau o'r math hwn godi wrth lunio rhestrau o eiriau allweddol ac ymadroddion ar gyfer hysbysebu ar-lein a hyrwyddo SEO, pan fydd angen i chi fynd trwy'r holl newidiadau posibl o eiriau mewn ymholiad chwilio:
Mewn mathemateg, gelwir y llawdriniaeth hon Cynnyrch Cartesaidd. Mae'r diffiniad swyddogol fel a ganlyn: y cynnyrch Cartesaidd o setiau A a B yw set yr holl barau, y mae ei gydran gyntaf yn perthyn i set A, ac mae'r ail gydran yn perthyn i set B. Ar ben hynny, gall elfennau setiau fod yn ddau. rhifau a thestun.
Wedi'i chyfieithu i'r iaith ddynol, mae hyn yn golygu os oes gennym ni yn set A, er enghraifft, y geiriau “gwyn” a “coch”, ac yn set B “BMW” a “Mercedes”, yna ar ôl cynnyrch Cartesaidd y ddwy set hyn rydyn ni cael ar yr allbwn yw'r set o'r holl amrywiadau posibl o ymadroddion, sy'n cynnwys geiriau'r ddwy restr:
- bmw gwyn
- bmw coch
- Mercedes gwyn
- mercedes coch
… hy yr hyn sydd ei angen arnom. Edrychwn ar ddwy ffordd i ddatrys y dasg hon yn Excel.
Dull 1. Fformiwlâu
Gadewch i ni ddechrau gyda fformiwlâu. Gadewch i ni dybio bod gennym ni fel data cychwynnol dair rhestr o eiriau gwreiddiol yng ngholofnau A, B ac C, yn y drefn honno, a gall nifer yr elfennau ym mhob rhestr amrywio:
Yn gyntaf, gadewch i ni wneud tair colofn gyda mynegeion, hy rhifau trefnol o eiriau o bob rhestr ym mhob cyfuniad posibl. Bydd y rhes gyntaf o unedau (E2: G2) yn cael eu mewnbynnu â llaw, ac ar gyfer y gweddill byddwn yn defnyddio'r fformiwla ganlynol:
Mae'r rhesymeg yma yn syml: os yw'r mynegai yn y gell flaenorol uwchraddol eisoes wedi cyrraedd diwedd y rhestr, hy yn hafal i nifer yr elfennau yn y rhestr a gyfrifwyd gan y ffwythiant COUNT (COUNTA), yna rydym yn ailgychwyn y rhifo. Fel arall, rydym yn cynyddu'r mynegai gan 1. Rhowch sylw arbennig i osod yr ystodau yn glyfar gydag arwyddion doler ($) fel y gallwch chi gopïo'r fformiwla i lawr ac i'r dde.
Nawr bod gennym rifau trefnol y geiriau sydd eu hangen arnom o bob rhestr, gallwn dynnu'r geiriau eu hunain gan ddefnyddio'r ffwythiant MYNEGAI (MYNEGAI) yn dair colofn ar wahân:
Os nad ydych wedi dod ar draws y swyddogaeth hon yn eich gwaith o'r blaen, yna rwy'n eich cynghori'n gryf i'w hastudio'n groeslinol o leiaf - mae'n helpu mewn llawer o sefyllfaoedd ac mae'n ddefnyddiol dim llai (a hyd yn oed yn fwy!) VPR (VLOOKUP).
Wel, ar ôl hynny, dim ond i gludo'r darnau canlyniadol fesul llinell gan ddefnyddio'r symbol concatenation (&):
… neu (os oes gennych y fersiwn diweddaraf o Excel) gyda'r swyddogaeth ddefnyddiol COMBINE (TEXTJOIN), sy'n gallu gludo cynnwys cyfan y celloedd penodedig trwy nod gwahanydd penodol (gofod):
Dull 2. Trwy Grym Ymholiad
Mae Power Query yn ychwanegiad pwerus ar gyfer Microsoft Excel sy'n cyflawni dwy brif dasg: 1. llwytho data i Excel o bron unrhyw ffynhonnell allanol, a 2. pob math o drawsnewidiadau o dablau wedi'u llwytho. Mae Power Query eisoes wedi'i ymgorffori yn Excel 2016-2019, ac ar gyfer Excel 2010-2013 mae wedi'i osod fel ychwanegiad ar wahân (gallwch ei lawrlwytho o wefan swyddogol Microsoft am ddim). Os nad ydych wedi dechrau defnyddio Power Query yn eich gwaith eto, yna mae'n bryd meddwl am y peth, oherwydd mae trawsnewidiadau fel y rhai a ddisgrifir uchod yn cael eu gwneud yno yn hawdd ac yn naturiol, mewn dim ond cwpl o symudiadau.
Yn gyntaf, gadewch i ni lwytho'r rhestrau ffynhonnell fel ymholiadau ar wahân yn Power Query. I wneud hyn, ar gyfer pob tabl, dilynwch y camau canlynol:
- Gadewch i ni droi tablau yn rhai “clyfar” gyda botwm Fformat fel bwrdd tab Hafan (Cartref - Fformat fel Tabl) neu lwybr byr bysellfwrdd Ctrl+T. Bydd pob tabl yn cael enw yn awtomatig Tabl 1,2,3…, sydd, fodd bynnag, yn gallu cael ei newid os dymunir ar y tab Constructor (Dylunio).
- Ar ôl gosod y gell weithredol yn y tabl, pwyswch y botwm O'r bwrdd (O'r Tabl) tab Dyddiad (Dyddiad) neu ar y tab Ymholiad Pwer (os ydych wedi ei osod fel ychwanegiad ar wahân ar gyfer Excel 2010-2013).
- Yn y ffenestr golygydd ymholiad sy'n agor, dewiswch y gorchymyn Cartref - Cau a Llwythwch - Cau a Llwythwch i mewn… (Cartref - Cau a Llwyth - Cau a Llwythwch i..) ac yna'r opsiwn Dim ond creu cysylltiad (Creu cysylltiad yn unig). Bydd hyn yn gadael y bwrdd wedi'i lwytho yn y cof ac yn caniatáu mynediad iddo yn y dyfodol.
Os gwnewch bopeth yn gywir, yna dylai'r allbwn yn y panel cywir fod yn dri chais yn y modd Cysylltiad yn unig gyda'n henwau tablau:
Nawr de-gliciwch ar yr ymholiad cyntaf a dewiswch y gorchymyn Cyswllt (Cyfeirnod)i wneud copi y gellir ei ddiweddaru ohono, ac yna ychwanegu colofn ychwanegol at y data trwy'r gorchymyn Ychwanegu colofn ž – Colofn personol (Ychwanegu Colofn -ž Colofn Custom). Yn y ffenestr mewnbwn fformiwla, rhowch enw'r golofn newydd (er enghraifft, Fragment2) a mynegiant hynod o syml fel fformiwla:
=Tabl2
… hy, mewn geiriau eraill, enw'r ail ymholiad:
Ar ôl clicio ar OK fe welwn golofn newydd, ym mhob cell bydd tabl nythu gydag ymadroddion o'r ail dabl (gallwch weld cynnwys y tablau hyn os cliciwch yng nghefndir y gell wrth ymyl y gair Tabl):
Erys i ehangu holl gynnwys y tablau nythu hyn gan ddefnyddio'r botwm gyda saethau dwbl ym mhennyn y golofn canlyniadol a dad-dicio Defnyddiwch enw'r golofn wreiddiol fel rhagddodiad (Defnyddiwch enw gwreiddiol y golofn fel rhagddodiad):
… a chawn bob cyfuniad posibl o elfennau o’r ddwy set gyntaf:
Ymhellach, mae popeth yn debyg. Ychwanegwch golofn gyfrifedig arall gyda'r fformiwla:
=Tabl3
…, ac yna ehangu’r tablau nythu eto – a nawr mae gennym eisoes yr holl opsiynau posibl ar gyfer newid geiriau o’r tair set, yn y drefn honno:
Mae'n aros i ddewis pob un o'r tair colofn o'r chwith i'r dde, gan ddal Ctrl, a chydgadwynu eu cynnwys wedi'u gwahanu gan fylchau gan ddefnyddio'r gorchymyn Cyfuno colofnau (Uno Colofnau) o'r tab Trawsnewid (Trawsnewid):
Gellir dadlwytho'r canlyniadau canlyniadol yn ôl i'r ddalen gyda'r gorchymyn sydd eisoes yn gyfarwydd Cartref - Cau a Llwythwch - Cau a Llwythwch i mewn… (Cartref - Cau a Llwyth - Cau a Llwythwch i..):
Os bydd rhywbeth yn newid yn ein tablau ffynhonnell gyda darnau yn y dyfodol, yna bydd yn ddigon i ddiweddaru'r ymholiad a gynhyrchir trwy dde-glicio ar y tabl canlyniadol a dewis y gorchymyn Diweddaru ac Arbed (Adnewyddu) neu drwy wasgu llwybr byr y bysellfwrdd Ctrl+Alt+F5.
- Beth yw Power Query, Power Pivot, Power Map a Power BI a pham mae angen defnyddiwr Excel arnynt
- Creu Siart Gantt mewn Pŵer Ymholiad
- 5 ffordd o ddefnyddio'r swyddogaeth MYNEGAI