Konten
Jika Anda setidaknya akrab dengan fungsinya VPR (VLOOKUP) (jika tidak, jalankan dulu di sini), maka Anda harus memahami bahwa ini dan fungsi lain yang mirip dengannya (LIHAT, INDEX dan SEARCH, SELECT, dll.) selalu memberikan hasilnya nilai – nomor, teks atau tanggal yang kita cari di tabel yang diberikan.
Tetapi bagaimana jika, alih-alih sebuah nilai, kita ingin mendapatkan hyperlink langsung, dengan mengklik di mana kita bisa langsung melompat ke kecocokan yang ditemukan di tabel lain untuk melihatnya dalam konteks umum?
Katakanlah kita memiliki meja pesanan besar untuk pelanggan kita sebagai input. Untuk kenyamanan (meskipun ini tidak perlu), saya mengonversi tabel menjadi pintasan keyboard "pintar" yang dinamis Ctrl+T dan berikan pada tab Pembina (Rancangan) namanya tabPesanan:
Di lembar terpisah Konsolidasi Saya membuat tabel pivot (meskipun tidak harus persis tabel pivot – tabel apa pun pada prinsipnya cocok), di mana, menurut data awal, dinamika penjualan per bulan untuk setiap klien dihitung:
Mari tambahkan kolom ke tabel pesanan dengan rumus yang mencari nama pelanggan untuk pesanan saat ini di lembar Konsolidasi. Untuk ini kami menggunakan sekelompok fungsi klasik INDEKS (INDEKS) и LEBIH TERBUKTI (COCOK):
Sekarang mari kita bungkus rumus kita menjadi sebuah fungsi SEL (SEL), yang akan kami minta untuk menampilkan alamat sel yang ditemukan:
Dan akhirnya, kami menempatkan semua yang telah berubah menjadi fungsi HIPERLINK (HIPERLINK), yang di Microsoft Excel dapat membuat hyperlink langsung ke jalur (alamat) yang diberikan. Satu-satunya hal yang tidak jelas adalah Anda harus merekatkan tanda hash (#) di awal ke alamat yang diterima sehingga tautan tersebut dianggap benar oleh Excel sebagai internal (dari lembar ke lembar):
Sekarang, ketika Anda mengklik salah satu tautan, kami akan langsung melompat ke sel dengan nama perusahaan di lembar dengan tabel pivot.
Untuk membuatnya benar-benar bagus, mari kita sedikit meningkatkan formula kita sehingga transisi tidak terjadi pada nama klien, tetapi ke nilai numerik tertentu persis di kolom bulan saat pesanan yang sesuai selesai. Untuk melakukan ini, kita harus ingat bahwa fungsi INDEKS (INDEKS) di Excel sangat serbaguna dan dapat digunakan, antara lain, dalam format:
=INDEKS( XNUMXD_rentang; Nomor_baris; Nomor_kolom )
Artinya, sebagai argumen pertama, kita tidak dapat menentukan kolom dengan nama perusahaan di pivot, tetapi seluruh area data tabel pivot, dan sebagai argumen ketiga, tambahkan jumlah kolom yang kita butuhkan. Itu dapat dengan mudah dihitung dengan fungsi BULAN (BULAN), yang mengembalikan nomor bulan untuk tanggal transaksi:
Peningkatan 2. Simbol tautan yang indah
Argumen fungsi kedua HIPERLINK – teks yang ditampilkan dalam sel dengan tautan – dapat dibuat lebih cantik jika Anda menggunakan karakter non-standar dari Windings, font Webdings, dan sejenisnya alih-alih tanda dangkal ">>". Untuk ini, Anda dapat menggunakan fungsi SYMBOL (ARANG), yang dapat menampilkan karakter dengan kodenya.
Jadi, misalnya, kode karakter 56 di font Webdings akan memberi kita panah ganda yang bagus untuk hyperlink:
Peningkatan 3. Sorot baris saat ini dan sel aktif
Nah, untuk kemenangan akhir kecantikan atas akal sehat, Anda juga dapat melampirkan ke file kami versi sederhana dari penyorotan baris saat ini dan sel yang kami ikuti tautannya. Ini akan membutuhkan makro sederhana, yang akan kita gantung untuk menangani acara perubahan pemilihan pada lembar Konsolidasi.
Untuk melakukan ini, klik kanan pada tab lembar Ringkasan dan pilih perintah View kode (Melihat kode). Tempelkan kode berikut ke jendela editor Visual Basic yang terbuka:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = -4142 Sel(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44 End Sub
Seperti yang dapat Anda lihat dengan mudah, di sini pertama-tama kita hapus isian dari seluruh lembar, lalu isi seluruh baris dalam ringkasan dengan kuning (kode warna 6), lalu oranye (kode 44) dengan sel saat ini.
Sekarang, ketika sel mana pun di dalam sel ringkasan dipilih (tidak masalah – secara manual atau sebagai hasil dari mengklik hyperlink kami), seluruh baris dan sel dengan bulan yang kami butuhkan akan disorot:
Kecantikan
PS Ingatlah untuk menyimpan file dalam format yang mendukung makro (xlsm atau xlsb).
- Membuat tautan eksternal dan internal dengan fungsi HYPERLINK
- Membuat email dengan fungsi HYPERLINK