tartalom
Ha legalább ismeri a funkciót VPR (VLOOKUP) (ha nem, akkor először futtasd itt), akkor meg kell értened, hogy ez és a többi hozzá hasonló függvény (NÉZET, INDEX és KERESÉS, KIVÁLASZTÁS stb.) mindig eredményt ad. érték – az adott táblázatban keresett szám, szöveg vagy dátum.
De mi van akkor, ha egy érték helyett egy élő hiperhivatkozást szeretnénk kapni, amelyre kattintva azonnal a talált egyezésre ugorhatunk egy másik táblázatban, hogy általános összefüggésben nézzük meg?
Tegyük fel, hogy van egy nagy rendelési táblázatunk ügyfeleink számára bemenetként. A kényelem kedvéért (bár ez nem szükséges) a táblázatot dinamikus „intelligens” billentyűparancsra konvertáltam Ctrl+T és adott a lapon építész (Tervezés) a neve lapRendelések:
Külön lapon Összevont Felépítettem egy pivot táblát (bár nem kell pontosan pivot táblának lennie – elvileg bármelyik tábla megfelelő), ahol a kiindulási adatok szerint minden ügyfélnél kiszámolják a hónapok szerinti értékesítési dinamikát:
Adjunk hozzá egy oszlopot a rendelési táblázathoz egy képlettel, amely kikeresi az aktuális rendelés vevő nevét a lapon Összevont. Ehhez a klasszikus függvénycsoportot használjuk INDEX (INDEX) и KIÉPÍTETTebb (MÉRKŐZÉS):
Most csomagoljuk a képletünket függvénybe SEJT (SEJT), amelyet meg fogunk kérni, hogy megjelenítse a talált cella címét:
És végül mindent, ami kiderült, függvénybe foglalunk HIPERLINK (HYPERLINK), amely a Microsoft Excelben élő hiperhivatkozást tud létrehozni egy adott elérési útra (címre). Az egyetlen dolog, ami nem nyilvánvaló, az az, hogy a kapott címre az elején a hash jelet (#) kell ragasztani, hogy a hivatkozást az Excel megfelelően belsőként érzékelje (lapról lapra):
Most, ha rákattint valamelyik hivatkozásra, azonnal a pivot táblát tartalmazó lapon a cég nevét tartalmazó cellára ugorunk.
Hogy valóban jó legyen, kicsit javítsuk a képletünket, hogy az áttérés ne az ügyfél nevére, hanem egy adott számértékre történjen pontosan abban a hónap oszlopban, amikor a megfelelő rendelés elkészült. Ehhez emlékeznünk kell arra, hogy a függvény INDEX (INDEX) Az Excelben nagyon sokoldalú, és többek között a következő formátumban használható:
= INDEX ( XNUMXD_tartomány; Vonal_száma; Oszlop_szám )
Azaz első argumentumként nem a pivotban lévő cégek nevét tartalmazó oszlopot adhatjuk meg, hanem a pivot tábla teljes adatterületét, és harmadik argumentumként adjuk hozzá a szükséges oszlop számát. Könnyen kiszámítható a függvénnyel HÓNAP (HÓNAP), amely az ügylet dátumának hónapszámát adja vissza:
Javítás 2. Gyönyörű link szimbólum
Második függvény argumentum HIPERLINK – a hivatkozással ellátott cellában megjelenő szöveg – szebbé tehető, ha a banális „>>” jelek helyett nem szabványos karaktereket használunk Windings, Webdings betűtípusokból és hasonlókból. Ehhez használhatja a funkciót SYMBOL (CHAR), amely kódjuk alapján képes karaktereket megjeleníteni.
Így például a Webdings betűtípus 56-os karakterkódja egy szép dupla nyilat ad a hiperhivatkozáshoz:
3. javítás. Jelölje ki az aktuális sort és az aktív cellát
Nos, a szépség végső győzelméhez a józan ész felett, csatolhatja fájlunkhoz az aktuális sor és a linket követő cella kiemelésének egyszerűsített változatát is. Ehhez egy egyszerű makróra lesz szükség, amelyet felakasztunk, hogy kezeljük a kijelölés módosítási eseményét a lapon Összevont.
Ehhez kattintson a jobb gombbal a lap Összegzés lapjára, és válassza ki a parancsot Megnézem kód (Kilátás kód). Illessze be a következő kódot a megnyíló Visual Basic szerkesztő ablakba:
Privát al-munkalap_kijelölésmódosítása (Cél érték szerint tartomány) Cells.Interior.ColorIndex = -4142 Cells(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.Color SubIndex = 44 End
Amint jól látható, itt először eltávolítjuk a kitöltést a teljes lapról, majd az összesítésben a teljes sort kitöltjük sárgával (6-os színkód), majd narancssárgával (44-es kód) az aktuális cellával.
Most, ha az összesítő cellán belül bármelyik cellát kijelöli (nem számít – manuálisan vagy a hiperhivatkozásunkra kattintva), a teljes sor és cella kiemelve lesz a kívánt hónappal:
Szépség 🙂
PS Ne felejtse el menteni a fájlt makró-kompatibilis formátumban (xlsm vagy xlsb).
- Külső és belső kapcsolatok létrehozása a HYPERLINK funkcióval
- E-mailek létrehozása a HYPERLINK funkcióval