Peretasan kehidupan Excel untuk mereka yang terlibat dalam pelaporan dan pemrosesan data

2024 Pengarang: Malcolm Clapton | [email protected]. Terakhir diubah: 2024-01-13 01:58

Dalam posting ini, Renat Shagabutdinov, Asisten Direktur Jenderal Mann, Ivanov dan Ferber Publishing House, membagikan beberapa peretasan kehidupan Excel yang keren. Kiat-kiat ini akan berguna bagi siapa saja yang terlibat dalam berbagai pelaporan, pemrosesan data, dan pembuatan presentasi.

Artikel ini berisi teknik sederhana untuk menyederhanakan pekerjaan Anda di Excel. Mereka sangat berguna bagi mereka yang terlibat dalam pelaporan manajemen, menyiapkan berbagai laporan analitis berdasarkan unduhan dari 1C dan laporan lainnya, membentuk presentasi dan diagram dari mereka untuk manajemen. Saya tidak berpura-pura menjadi kebaruan mutlak - dalam satu atau lain bentuk, teknik ini mungkin dibahas di forum atau disebutkan dalam artikel.

Alternatif sederhana untuk VLOOKUP dan HLOOKUP, jika nilai yang diinginkan tidak ada di kolom pertama tabel: LOOKUP, INDEX + SEARCH

Fungsi VLOOKUP dan HLOOKUP hanya berfungsi jika nilai yang diinginkan ada di kolom atau baris pertama tabel yang Anda rencanakan untuk mendapatkan data.

Jika tidak, ada dua opsi:

1. Gunakan fungsi LOOKUP.

   Ini memiliki sintaks berikut: LOOKUP (lookup_value; lookup_vector; result_vector). Tetapi agar berfungsi dengan benar, nilai rentang view_vector harus diurutkan dalam urutan menaik:

   

2. Gunakan kombinasi fungsi MATCH dan INDEX.

   Fungsi MATCH mengembalikan nomor urut elemen dalam array (dengan bantuannya Anda dapat menemukan di baris tabel mana elemen yang dicari berada), dan fungsi INDEX mengembalikan elemen array dengan nomor tertentu (yang akan kita temukan menggunakan fungsi MATCH).

   

   Sintaks fungsi:

   • SEARCH (search_value; search_array; match_type) - untuk kasus kami, kami membutuhkan jenis pencocokan "pencocokan persis", yang sesuai dengan angka 0.

   • INDEX (array; nomor_baris; [nomor_kolom]). Dalam hal ini, Anda tidak perlu menentukan nomor kolom, karena array terdiri dari satu baris.

Cara cepat mengisi sel kosong dalam daftar

Tugasnya adalah mengisi sel-sel di kolom dengan nilai di atas (sehingga topik ada di setiap baris tabel, dan tidak hanya di baris pertama blok buku tentang topik):

Pilih kolom "Subjek", klik pita di grup "Beranda", tombol "Temukan dan Pilih" → "Pilih grup sel" → "Sel kosong" dan mulai masukkan rumus (yaitu, beri nilai yang sama sign) dan merujuk ke sel di atas, cukup dengan mengklik panah ke atas pada keyboard Anda. Setelah itu tekan Ctrl + Enter. Setelah itu, Anda dapat menyimpan data yang diterima sebagai nilai, karena rumus tidak lagi diperlukan:

Cara menemukan kesalahan dalam rumus

Perhitungan bagian terpisah dari formula

Untuk memahami rumus kompleks (di mana fungsi lain digunakan sebagai argumen fungsi, yaitu, beberapa fungsi bersarang di yang lain) atau untuk menemukan sumber kesalahan di dalamnya, Anda sering perlu menghitung sebagiannya. Ada dua cara mudah:

1. Untuk menghitung bagian dari rumus tepat di bilah rumus, pilih bagian itu dan tekan F9:

   

   Dalam contoh ini, ada masalah dengan fungsi SEARCH - argumen ditukar di dalamnya. Penting untuk diingat bahwa jika Anda tidak membatalkan perhitungan bagian fungsi dan menekan Enter, maka bagian yang dihitung akan tetap berupa angka.

2. Klik tombol Hitung Rumus di grup Rumus pada pita:

   

   Di jendela yang muncul, Anda dapat menghitung rumus langkah demi langkah dan menentukan pada tahap apa dan di fungsi mana kesalahan terjadi (jika ada):

   

Bagaimana menentukan apa yang bergantung pada atau mengacu pada formula

Untuk menentukan sel mana yang bergantung pada rumus, dalam grup Rumus pada pita, klik tombol Mempengaruhi Sel:

Panah muncul untuk menunjukkan apa yang bergantung pada hasil perhitungan.

Jika simbol yang disorot dalam gambar berwarna merah ditampilkan, maka rumusnya tergantung pada sel di lembar lain atau di buku lain:

Dengan mengkliknya, kita dapat melihat dengan tepat di mana sel atau rentang yang mempengaruhi berada:

Di sebelah tombol "Mempengaruhi Sel" adalah tombol "Sel Tergantung", yang bekerja dengan cara yang sama: ini menampilkan panah dari sel aktif dengan rumus ke sel yang bergantung padanya.

Tombol "Hapus panah", yang terletak di blok yang sama, memungkinkan Anda untuk menghapus panah ke sel yang memengaruhi, panah ke sel yang bergantung, atau kedua jenis panah sekaligus:

Cara menemukan jumlah (angka, rata-rata) nilai sel dari beberapa lembar

Katakanlah Anda memiliki beberapa lembar dengan tipe yang sama dengan data yang ingin Anda tambahkan, hitung, atau proses dengan cara lain:

Untuk melakukan ini, di sel tempat Anda ingin melihat hasilnya, masukkan rumus standar, misalnya SUM (SUM), dan tentukan nama lembar pertama dan terakhir dari daftar lembar yang perlu Anda proses argumen, dipisahkan oleh titik dua:

Anda akan menerima jumlah sel dengan alamat B3 dari lembar "Data1", "Data2", "Data3":

Pengalamatan ini berfungsi untuk lembar yang terletak secara konsisten … Sintaksnya adalah sebagai berikut: = FUNCTION (daftar_pertama: daftar_akhir! Referensi rentang).

Cara membuat frase template secara otomatis

Menggunakan prinsip dasar bekerja dengan teks di Excel dan beberapa fungsi sederhana, Anda dapat menyiapkan frasa template untuk laporan. Beberapa prinsip bekerja dengan teks:

• Kami menggabungkan teks menggunakan tanda & (Anda dapat menggantinya dengan fungsi CONCATENATE, tetapi itu tidak masuk akal).
• Teks selalu ditulis dalam tanda kutip, referensi ke sel dengan teks selalu tanpa.
• Untuk mendapatkan "tanda kutip" karakter layanan, gunakan fungsi CHAR dengan argumen 32.

Contoh membuat frase template menggunakan rumus:

Hasil:

Dalam hal ini, selain fungsi CHAR (untuk menampilkan kutipan), fungsi IF digunakan, yang memungkinkan Anda untuk mengubah teks tergantung pada apakah ada tren penjualan yang positif, dan fungsi TEXT, yang memungkinkan Anda untuk menampilkan nomor dalam format apapun. Sintaksnya dijelaskan di bawah ini:

TEKS (nilai; format)

Format ditentukan dalam tanda kutip, sama seperti jika Anda memasukkan format kustom di jendela Format Sel.

Teks yang lebih kompleks juga dapat diotomatisasi. Dalam praktik saya, ada otomatisasi panjang, tetapi komentar rutin untuk pelaporan manajemen dalam format “INDIKATOR turun / naik XX relatif terhadap rencana, terutama karena pertumbuhan / penurunan FAKTOR1 sebesar XX, pertumbuhan / penurunan FAKTOR2 sebesar YY … dengan daftar faktor yang berubah. Jika Anda sering menulis komentar seperti itu dan proses penulisannya dapat dialgoritmakan, ada baiknya jika Anda bingung untuk membuat rumus atau makro yang akan menghemat setidaknya sebagian pekerjaan Anda.

Cara menyimpan data di setiap sel setelah penggabungan

Saat Anda menggabungkan sel, hanya satu nilai yang dipertahankan. Excel memperingatkan tentang ini saat mencoba menggabungkan sel:

Oleh karena itu, jika Anda memiliki rumus yang bergantung pada setiap sel, rumus itu akan berhenti bekerja setelah menggabungkannya (# N / A kesalahan pada baris 3-4 dari contoh):

Untuk menggabungkan sel dan tetap mempertahankan data di masing-masing sel (mungkin Anda memiliki rumus seperti dalam contoh abstrak ini; mungkin Anda ingin menggabungkan sel, tetapi menyimpan semua data untuk masa mendatang atau menyembunyikannya dengan sengaja), gabungkan sel apa pun di lembar, pilih mereka, lalu gunakan perintah Format Painter untuk mentransfer pemformatan ke sel yang perlu Anda gabungkan:

Cara membuat pivot dari berbagai sumber data

Jika Anda perlu membuat pivot dari beberapa sumber data sekaligus, Anda harus menambahkan "PivotTable dan Chart Wizard" ke pita atau panel akses cepat, yang memiliki opsi seperti itu.

Anda dapat melakukannya sebagai berikut: "File" → "Options" → "Quick Access Toolbar" → "All Commands" → "PivotTable and Chart Wizard" → "Add":

Setelah itu, ikon yang sesuai akan muncul di pita, mengklik yang memanggil wizard yang sama:

Ketika Anda mengkliknya, kotak dialog muncul:

Di dalamnya, Anda harus memilih item "Dalam beberapa rentang konsolidasi" dan klik "Berikutnya". Pada langkah selanjutnya, Anda dapat memilih "Buat bidang satu halaman" atau "Buat bidang halaman". Jika Anda ingin membuat nama untuk setiap sumber data secara mandiri, pilih item kedua:

Di jendela berikutnya, tambahkan semua rentang yang menjadi dasar pembuatan pivot, dan beri nama:

Setelah itu, di kotak dialog terakhir, tentukan di mana laporan tabel pivot akan ditempatkan - pada lembar yang ada atau baru:

Laporan tabel pivot sudah siap. Di filter "Halaman 1", Anda hanya dapat memilih salah satu sumber data, jika perlu:

Cara menghitung jumlah kemunculan teks A dalam teks B ("Tarif MTS SuperMTS" - dua kemunculan singkatan MTS)

Dalam contoh ini, kolom A berisi beberapa baris teks, dan tugas kita adalah mencari tahu berapa kali masing-masing baris berisi teks pencarian yang terletak di sel E1:

Untuk mengatasi masalah ini, Anda dapat menggunakan rumus kompleks yang terdiri dari fungsi-fungsi berikut:

1. DLSTR (LEN) - menghitung panjang teks, satu-satunya argumen adalah teks. Contoh: DLSTR ("mesin") = 6.
2. PENGGANTI - mengganti teks tertentu dalam string teks dengan yang lain. Sintaks: SUBSTITUTE (teks; teks_lama; teks_baru). Contoh: PENGGANTI (“mobil”; “otomatis”; “”) = “ponsel”.
3. UPPER - mengganti semua karakter dalam string dengan huruf besar. Satu-satunya argumen adalah teks. Contoh: UPPER (“mesin”) = “MOBIL”. Kami membutuhkan fungsi ini untuk melakukan pencarian case-insensitive. Bagaimanapun, UPPER ("mobil") = UPPER ("Mesin")

Untuk menemukan kemunculan string teks tertentu di teks lain, Anda perlu menghapus semua kemunculannya di teks asli dan membandingkan panjang string yang dihasilkan dengan yang asli:

DLSTR (“Tarif MTS Super MTS”) - DLSTR (“Tarif Super”) = 6

Dan kemudian bagi perbedaan ini dengan panjang string yang kita cari:

6 / DLSTR (“MTS”) = 2

Tepat dua kali baris "MTS" disertakan dalam yang asli.

Tetap menulis algoritme ini dalam bahasa rumus (mari kita tunjukkan dengan "teks" teks di mana kita mencari kemunculan, dan dengan "mencari" - teks yang jumlah kemunculannya kita minati):

= (DLSTR (teks) -LSTR (SUBSTITUTE (UPPER (teks); UPPER (pencarian), ""))) / DLSTR (pencarian)

Dalam contoh kita, rumusnya terlihat seperti ini:

= (DLSTR (A2) -LSTR (SUBSTITUTE (UPPER (A2), UPPER ($ E $ 1), “ ))) / DLSTR ($ E $ 1)