Es ist bekan­nt, dass die Funk­tion SVER­WEIS() nur nach rechts su­chen kann, also mit ei­nem pos­i­tiv­en In­dex. Ein neg­a­tiv­er In­dex ist zwar über Um­we­ge mit der VER­WEIS()-Funk­tion in der Matrix-Vari­ante mög­lich, aber nach Aus­sage Mi­cro­soft soll­te die­se Mög­lich­keit nicht mehr ver­wen­det wer­den. Und ich gehe da­von aus, dass mit jed­er neue­ren Ver­sion des Ex­cel die War­nung vor der Ver­wen­dung im­mer deut­lich­er wird.

Mit­tels Pow­er Que­ry (in Ex­cel 2016 Ab­ru­fen und trans­formieren) ist es mit weni­gen Maus­klicks mög­lich, ei­nen Er­satz für den SVER­WEIS zu find­en und als an­ge­neh­men Neben­ef­fekt auch gle­ich den neg­a­tiv­en In­dex mit­zu­neh­men.

Vor­aus­set­zun­gen

Um das zu erre­ichen, gibt es we­ni­ge Voraus­set­zun­gen. Sie müs­sen Ex­cel in der Win­dows-Ver­sion vor­liegen ha­ben, Pow­er Que­ry muss instal­liert sein (ab 2016 ist das inte­gri­ert) und die Dat­en müs­sen ohne kom­plette Leer­zei­len vor­liegen. Mehr be­darf es nicht.

▲ nach oben …

Der Ab­lauf

La­den Sie zu Be­ginn erst ein­mal die­se Da­tei her­un­ter und öff­nen Sie die­se in Ex­cel. Sie erken­nen in den Basis­dat­en eine min­i­mal­is­tis­che Rech­nung, eher eine Auf­stel­lung. Die kann so beispiel­sweise von ein­er Daten­bank ex­por­tiert wor­den sein.

Import-Dat­en für eine Rech­nung

Der entschei­dende Punkt ist, dass nur die Pro­duk­t­num­mer einge­tra­gen ist und die Beze­ich­nung als auch der Preis fehlt. Die­se bei­den Wer­te sind in in ei­nem weit­eren Arbeits­blatt aufge­führt:

Dat­en für die Verknüp­fung mit der Rech­nung

Dass es nor­maler­weise erhe­blich mehr Pro­duk­te sind, ver­ste­ht sich von al­lei­ne. Hier ist es der Über­sichtlichkeit ge­schul­det, dass es nur die­se 7 Ar­ti­kel sind. Wich­tig ist, dass die Pro­duk­t­num­mer für die Verknüp­fung ver­wen­det wird. Dass die­se nicht in der ers­ten Spal­te der Lis­te ste­ht, ist von mir ex­tra so ein­gerichtet, um den neg­a­tiv­en In­dex, den Ver­weis nach links dar­zu­stel­len . Und die Anord­nung soll natür­lich so blei­ben, wie sie ist.

Eine weit­ere wich­ti­ge Voraus­set­zung für die­ses Vor­ha­ben ist, dass alle zu nutzen­den Dat­en, also die Ba­sis- als auch die Ref­eren­z­dat­en als Intel­li­gente Ta­bel­le vor­liegen. Die Basis­dat­en lie­gen in die­ser Form vor. Sie erken­nen das dar­an, dass der Me­nü­punkt Tabel­len­tools sicht­bar ist, wenn der Cur­sor (die ak­ti­ve Zel­le) im Daten­bere­ich ste­ht. Die Ref­eren­z­dat­en im Blatt Pro­duk­te sind noch nicht als Ta­bel­le for­matiert. Das muss als ers­tes ge­sche­hen. Wie Sie das ma­chen, sei Ih­nen über­lassen. Ob über das Menü oder Tas­tenkom­bi­na­tion, das Ergeb­nis ist au­ßer der Op­tik im­mer gle­ich. Ich ver­wende stets StrgL (wie Liste) oder StrgT (wie Tabel­le).

▲ nach oben …

OK, jet­zt sind bei­de zu verknüpfend­en Daten­blöcke im Tabellen-For­mat. Das Arbeits­blatt Pro­duk­te ist das ak­tu­el­le und die ak­ti­ve Zel­le ist irgend­wo im Daten­bere­ich. Je nach Excel-Ver­sion wer­den Sie nun in die Pow­er Que­ry-Funk­tion­al­ität her­stellen. In Ex­cel ²⁰¹⁰⁄₂₀₁₃ ist Pow­er Que­ry ja ein eigen­ständi­ger Me­nü­punkt, in Ex­cel 2016 find­en Sie die entsprechen­den Aus­wah­len beim Me­nü­punkt Dat­en.

Jet­zt im Men­üband bei den Icons auf Aus Ta­bel­le (bei ²⁰¹⁰⁄₁₃ Von Ta­bel­le) Klick­en und es öff­net sich ein neu­es Fen­ster mit dem Abfrage-Edi­tor:

Der Abfrage-Edi­tor (Teil des Fen­sters)

An die­ser Stel­le soll­ten Sie die Gele­gen­heit nut­zen und der kün­fti­gen Ta­bel­le ei­nen sin­nvollen Na­men ge­ben. Rechts im Edi­tor-Fen­ster se­hen Sie den Bere­ich mit den Abfrage-Ein­stel­lun­gen:

Umbe­nen­nen des Abfrage-Ergeb­niss­es

Lö­schen Sie hier den Na­men Ta­bel­le1 und tra­gen da­für tbl_­Pro­duk­te ein. Das war erst ein­mal al­les. Schlie­ßen Sie den Edi­tor, in­dem Sie im Men­üband auf das Sym­bol Schlie­ßen und la­den Klick­en. Es wur­de ein neu­es Tabel­len­blatt an­ge­legt, wo die Ta­bel­le in for­matiert­er Form ste­ht. Außer­dem zeigt sich rechts im Anwen­dungs-Fen­ster der Bere­ich mit den Arbeitsmap­pen­abfra­gen:

Der Bere­ich der Abfra­gen

Wech­seln Sie nun zum Sheet Basis­dat­en. Auch hier acht­en Sie dar­auf, dass min­destens eine Zel­le inner­halb der Dat­en mar­kiert ist und wie zu­vor über Pow­er Que­ry Aus bzw. Von Ta­bel­le anklick­en und da­mit eine weit­ere Ab­fra­ge er­stel­len. Ein kurz­er Blick nach rechts zeigt Ih­nen, dass der Name der (kün­fti­gen) Ta­bel­le schon ein „sprechen­der” ist, der kann also blei­ben. Jet­zt aber nicht schlie­ßen son­dern inner­halb des Edi­tors in der Grup­pe Com­bine den Punkt Abfra­gen zusam­men­führen anklick­en. Es öff­net sich die­ses Fen­ster:

Der ers­te Schritt beim Zusam­men­führen von Abfra­gen

Hin­weis: Es ist sehr wich­tig, dass die Ab­fra­ge tbl_­Be­stel­lung im obe­ren Teil-Fen­ster ste­ht. Von oben nach un­ten gese­hen (was die Fen­ster bet­rifft) ist es eine n:1 Be­zie­hung, die er­stellt wer­den soll. OK, das war für Daten­bank-Kundi­ge.  😎 An­mer­kung für Gu­rus: Ja, ich weiß …

Mer­ken Sie sich bit­te: In den obe­ren Fen­sterteil ge­hört die Ta­bel­le, wo das ver­knüpf­te Feld (hier: die Pro­duk­t­num­mer) mehr­fach vorkom­men kann oder auch vor­kommt. Im Ver­gle­ich zum SVER­WEIS wäre es das ers­te Argu­ment der Funk­tion. Meh­re­re (n) Kun­den kön­nen ein (1) Pro­dukt kau­fen.

Im frei­en Feld unter­halb der an­ge­zeig­ten Ta­bel­le mit den Aufträ­gen Klick­en Sie auf den Pfeil  und wäh­len die Ta­bel­le, wo die Pro­duk­t­num­mern und die dazu gehöri­gen Dat­en ste­hen. Die­se Num­mern sind ein­ma­lig, also ohne Dublet­ten, auch wenn es nicht zwin­gend not­wen­dig ist. Und sie sind auch nicht sor­tiert. – Die Aus­wahl fällt nicht schw­er, es ist natür­lich die Ab­fra­ge tbl_­Pro­duk­te.

Jet­zt richt­en Sie die ei­gent­li­che Verknüp­fung ein. Ich ver­suche ein­mal, eine Esels­brücke zu bau­en. Beim SVER­WEIS hei­ßt es ja dem Sinn nach: WAS soll ge­sucht wer­den? – WO soll nachge­se­hen wer­den (Bere­ich)? – Wel­che SPAL­TE der Fund­zei­le soll zurück­gegeben wer­den? Das vier­te Argu­ment betra­chte ich hier nicht.

Die­ser Weg mit Pow­er Que­ry ist in etwa ver­gle­ich­bar. WAS soll ge­sucht wer­den? Klar, die Pro­duk­t­num­mer. Die ist ja der ein­zi­ge Hin­weis auf den zu suchen­den Ar­ti­kel. WO soll ge­sucht wer­den? Lo­gisch, in tbl_­Pro­duk­te. – Jet­zt ist aber ein gravieren­der Unter­schied ge­ge­ben. Sie müs­sen näm­lich an­ge­ben, in welch­er Spal­te der zu su­chen­de Be­griff ste­ht. Es ist nicht zwin­gend die ers­te Spal­te.

Um es ab­zu­kür­zen: Klick­en Sie in tbl_­Be­stel­lung irgend­wo in die Spal­te Pro­duk­t­num­mer, und in tbl_­Pro­duk­te ist es „zufäl­lig” auch Pro­duk­t­num­mer. Gle­iche Spal­ten-Namen erle­ichtern das Den­ken, sind aber nicht zwin­gend not­wen­dig. Wenn Sie bei­de Spal­ten mar­kiert ha­ben, sieht das so aus:

Die bei­den Spal­ten für den Link sind mar­kiert

Ein Klick auf OK und die­ses Fen­ster schlie­ßt sich. Der Abfrage-Edi­tor ist aber im­mer noch ge­öff­net. Und dort gibt es eine neue Spal­te mit dem wirk­lich „ein­fall­sre­ichen” Na­men New­Col­umn. Und der In­halt jed­er einzel­nen Zei­le ist auch nicht ger­ade viel­sagend. Aber rechts in der Über­schrift ist solch ein Sym­bol: . Klick­en Sie ein­mal dar­auf und es öff­net sich die­ser Dia­log:

Bes­tim­men Sie, wel­che Fel­der aus­gegeben wer­den sol­len

Es sol­len der Pro­dukt-Name und der Preis an­ge­zeigt wer­den. Ent­fer­nen Sie die an­de­ren Häk­chen, ein­schließlich der Aus­wahl ganz un­ten mit den Spal­tenna­men. Dann wer­den näm­lich nur die ei­gent­li­chen Spal­tenna­men als Über­schrift ge­schrie­ben. OK und das Ergeb­nis sieht schon sehr gut aus. Prinzip­iell ist das Ziel erre­icht. Sie kön­nen nun noch in der Grup­pe Trans­formieren den Daten­typ in der Spal­te Da­tum auf den Daten­typ Da­tum (statt Da­tum/Uhr­zeit) än­dern und bei Preis auf Wäh­rung. Zum gu­ten Schluss Schlie­ßen und la­den. Sie se­hen, dass der Ver­weis mit ei­nem pos­i­tiv­en als auch neg­a­tiv­en In­dex klappt.

Was gibt es noch zu tun? Na ja, se­hen Sie sich doch ein­mal im Blatt Pro­duk­te die Zel­le A4 an. Vielle­icht ist es Ih­nen ja schon aufge­fall­en, dass da ein „s” bei der Ta­ta­tur fehlt. Nut­zen Sie die Gele­gen­heit das zu ver­bes­sern. Und Sie erken­nen dann ei­nen weit­eren Gegen­satz zum SVER­WEIS: Es erfol­gt kei­ne automa­tis­che Anpas­sung, kei­ne automa­tis­che Än­de­rung der Dat­en in der erstell­ten Rech­nung. Dazu be­darf es ein­er klei­nen Ak­ti­on: Wäh­len Sie den Me­nü­punkt Tabel­len­tools oder (vor­zugs­wei­se) Abfrage­tools und Klick­en Sie an­schlie­ßend auf die Schalt­fläche Aktu­al­isieren. Alter­na­tiv geht das auch per Recht­sklick in die dat­en und im Kon­textmenü Aktu­al­isieren wäh­len. Das mag Ih­nen im ers­ten Mo­ment als klein­er Nach­teil er­schei­nen, aber Pow­er Que­ry wird typ­is­cher­weise so einge­set­zt, dass im­mer fri­sche, neue Dat­en automa­tisch ein­ge­le­sen und wie bei ei­nem VBA-Pro­gramm in im­mer gle­ich­er Wei­se aus­gew­ertet wer­den. Und der Anwen­der kann bes­tim­men, wann die aktu­al­isierten Dat­en über­nom­men wer­den.

Last but not least kön­nen Sie nun auch die Berech­nun­gen beispiel­sweise für den Gesamt­preis in der erstell­ten Ta­bel­le vor­neh­men. Ein­fach eine Spal­te anfü­gen, Über­schrift in G1 schrei­ben und in G2 die Berech­nung durch­führen.

▲ nach oben …

Ref­er­ence: #0522