Hacker bagi mereka yang terlibat dalam pelaporan dan pengolahan data excel-hidup

Renat bukan pertama kalinya seorang penulis tamu berbicara pada Layfhakere. Sebelumnya, kami menerbitkan bahan yang sangat baik dari dia tentang cara membuat rencana pelatihan: buku utama dan sumber daya online, serta algoritma tambahan membuat rencana pelatihan.

Artikel ini berisi beberapa teknik sederhana yang membantu menyederhanakan pekerjaan di Excel. mereka sangat berguna untuk mereka yang terlibat dalam pelaporan manajemen, mempersiapkan berbagai analitis laporan berdasarkan pendaratan laporan 1C dan bentuk lain dari presentasi ini dan diagram untuk kepemimpinan. Jangan berpura-pura menjadi hal baru mutlak - dalam satu bentuk atau lain, metode ini tentu dibahas dalam forum atau sebagaimana dimaksud dalam Pasal.

alternatif sederhana untuk VLOOKUP dan HLOOKUP, jika nilai-nilai yang diperlukan tidak dalam kolom pertama dari tabel: VIEW, INDEX + MATCH

VLOOKUP (VLOOKUP) Dan PGR (HLOOKUP) hanya bekerja jika nilai target berada di kolom atau baris pertama dari tabel dari mana Anda ingin mendapatkan data.

Dalam kasus lain, ada dua pilihan:

1. Fungsi penggunaan VIEW (LOOKUP).
   Dia memiliki sintaks berikut: MENONTON (lookup_value Cari vektor; vektor_rezultata). Tapi untuk itu untuk bekerja dengan benar Anda butuhkan untuk nilai kisaran vektor pencarian Mereka diurutkan dalam urutan menaik:
2. MATCH menggunakan kombinasi fungsi (MATCH) dan indeks (INDEX).
   Fungsi MATCH mengembalikan nomor seri dari elemen dalam array (dengan bantuan yang Anda dapat menemukan, di deretan meja elemen yang diinginkan), dan fungsi INDEX mengembalikan sebuah elemen array dengan jumlah yang telah ditetapkan (yang kita tahu untuk fungsi MATCH).Pilihan sintaks:
   • MATCH (lookup_value massiv_poiska; match_type) - dalam kasus kami, kami perlu semacam perbandingan "pencocokan tepat", itu sesuai dengan angka 0.
   • INDEX (Array; jml_baris; [Jml_kolom]). Dalam hal ini, tidak perlu untuk menentukan jumlah kolom sebagai array terdiri dari satu baris.

Seberapa cepat untuk mengisi sel kosong dalam daftar

Tugas - untuk mengisi sel dengan nilai-nilai dalam kolom di atas (tunduk berdiri di setiap baris meja, tidak hanya di blok baris pertama buku pada subjek):

Pilih kolom "Subject", tekan pada pita pada kelompok tombol "Home" "Cari dan pilih» → «Sorot sekelompok sel» → «Kosong sel "dan mulai memasukkan rumus (yaitu, menempatkan tanda sama) dan mengacu pada sel dari atas, dengan hanya menekan panah Keyboard. Lalu tekan Ctrl + Enter. Setelah itu, data dapat disimpan sebagai nilai, karena formula tidak lagi diperlukan:

Bagaimana menemukan kesalahan dalam rumus

Rumus perhitungan bagian yang terpisah

Untuk memahami rumus kompleks (di mana sebagai argumen fungsi menggunakan lainnya fungsi, yaitu, beberapa fungsi yang tertanam dalam lainnya), atau untuk menemukan di dalamnya sumber kesalahan, sering perlu untuk menghitung bagian dari itu. Ada dua cara mudah:

1. Untuk menghitung bagian kanan dari rumus di formula bar, pilih bagian, dan tekan F9:
   
   Dalam contoh ini, ada masalah dengan fungsi SEARCH (CARI) - ada campuran argumen up. Penting untuk diingat bahwa jika Anda tidak membatalkan perhitungan fungsi dan tekan Enter, maka porsi dihitung akan tetap nomor.
2. Tekan tombol "Hitung rumus" dalam kelompok "formula" pada tape:
   
   
   Pada jendela yang muncul, Anda dapat menghitung langkah rumus demi langkah dan menentukan pada tahap apa dan apa kesalahan fungsi (jika ada):
   

Bagaimana menentukan apa yang menentukan formula yang direferensikan atau

Untuk menentukan mana dari sel tergantung pada rumus dalam kelompok dari "rumus" di pita, klik pada tombol "Jejak Preseden":

Ada anak panah yang menunjuk dari mana hasilnya tergantung.

Jika simbol muncul pada gambar yang dipilih dalam warna merah, formula tergantung pada sel-sel yang berada di daftar lain atau dalam buku-buku lain:

Mengkliknya, kita dapat melihat persis di mana pengaruh sel atau range:

Sebelah "Jejak Preseden" Ada tombol "Tanggungan", bekerja dengan cara yang sama: ini akan menampilkan panah dari sel yang aktif dengan rumus untuk sel-sel yang bergantung padanya.

Tombol "Hapus panah" yang terletak di blok yang sama, memungkinkan untuk menghapus Preseden, Tanggungan, atau kedua jenis sekaligus panah:

Bagaimana menemukan jumlah (jumlah, rata-rata) nilai-nilai sel dengan beberapa lembar

Katakanlah Anda memiliki beberapa lembar yang sama dengan data yang Anda ingin menambahkan, menemukan atau diolah dengan cara lain:

Untuk melakukan hal ini, sel di mana Anda ingin melihat hasilnya, masukkan formula standar, seperti SUM (SUM), dan masukkan argumen diikuti dengan titik dua dan nama terakhir dari lembar pertama dari daftar lembar Anda perlu proses:

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

karya menyikapi tersebut untuk lembar diatur berturut-turut. Sintaksnya adalah: FITUR = (pervyy_list: referensi posledniy_list ke kisaran!).

Bagaimana untuk secara otomatis membangun frase formula

Menggunakan prinsip-prinsip dasar bekerja dengan teks di Excel dan beberapa fungsi sederhana, bisa frase formula siap untuk laporan. Beberapa prinsip kerja dengan teks:

• Menggabungkan teks dengan tanda & (dapat menggantikan fungsi CONCATENATE nya (CONCATENATE), tapi ini tidak banyak digunakan).
• Teks selalu ditulis dalam tanda kutip, referensi sel dengan teks - tidak selalu.
• Untuk mendapatkan karakter "kutipan" khusus, menggunakan fungsi karakter (CHAR) argumen 32.

CONTOH menciptakan frase Template menggunakan rumus:

menghasilkan:

Dalam hal ini, selain fungsi SYMBOL (CHAR) (untuk menampilkan tanda kutip) menggunakan fungsi IF (IF), yang memungkinkan untuk mengubah teks di Tergantung pada apakah ada tren positif dalam penjualan dan fungsi teks (TEXT), yang memungkinkan untuk menampilkan nomor di setiap Format. sintaks diuraikan di bawah ini:

TEXT (nilai; format)

format yang ditentukan dalam tanda kutip dengan cara yang sama seperti jika Anda memasuki format kustom di "Format Cells."

Anda dapat mengotomatisasi teks yang lebih kompleks. Dalam praktek saya adalah untuk mengotomatisasi panjang, tapi komentar rutin pada rekening manajemen dalam format "sosok jatuh / mawar di XX pada rencana terutama disebabkan oleh kenaikan / penurunan FAKTORA1 XX, peningkatan / penurunan FAKTORA2 YY... »dengan daftar perubahan faktor. Jika Anda menulis komentar seperti sering dan proses penulisan dapat algorithmization - layak sekali bingung membuat formula atau makro yang akan menyelamatkan Anda dari setidaknya bagian dari pekerjaan.

Cara menyimpan data dalam setiap sel setelah merger

Ketika menggabungkan sel dipertahankan hanya satu nilai. Excel memperingatkan Anda ketika Anda mencoba untuk sel merge:

Dengan demikian, jika Anda memiliki formula yang tergantung pada masing-masing sel, ia akan berhenti bekerja setelah serikat mereka (bug # N / A di baris 3-4 contoh):

Untuk sel merge, sambil menjaga data di masing-masing (mungkin Anda memiliki formula, seperti dalam contoh ini, abstrak; Anda mungkin ingin menggabungkan sel, tetapi menyimpan semua data di masa depan, atau untuk menyembunyikan niat mereka), menggabungkan sel apapun di sheet, memilih mereka, dan kemudian menggunakan perintah "Format Painter" format transfer pada sel-sel yang Anda perlu dan menggabungkan:

Bagaimana membangun ringkasan beberapa sumber data

Jika Anda perlu untuk membangun konsolidasi dari berbagai sumber data, maka perlu menambah pita atau Quick Access Toolbar "PivotTable Wizard dan grafik" di mana ada opsi seperti itu.

Anda dapat melakukan ini sebagai berikut: "File» → «Pengaturan» → «Quick Access Toolbar» → «Semua tim» → «PivotTable Wizard dan diagram» → «Add»:

Setelah itu, rekaman itu akan menjadi ikon yang sesuai, klik pada yang menyebabkan penambahan master:

Ketika Anda klik di atasnya, kotak dialog:

Di dalamnya Anda akan perlu memilih "Beberapa band konsolidasi" dan klik "Next". Pada bagian berikutnya, Anda dapat memilih "Buat bidang halaman" atau "Buat bidang halaman." Jika Anda ingin diri Anda untuk datang dengan nama untuk masing-masing sumber data - untuk memilih item kedua:

Pada kotak berikutnya, menambahkan semua rentang atas dasar untuk membangun ringkasan, dan meminta mereka nama-nama:

Setelah titik itu, akan menjadi tuan rumah sebuah laporan PivotTable di kotak dialog terakhir - pada ada atau lembar kerja baru:

Laporan PivotTable siap. Filter "Page 1" Anda dapat memilih hanya salah satu sumber data, jika diperlukan:

Bagaimana menghitung jumlah kemunculan teks dalam teks A B (SuperMTS «MTS tarif" - dua kejadian dari MTS singkatan)

Dalam contoh ini, kolom A, ada beberapa baris teks, dan tugas kita - untuk mengetahui berapa kali masing-masing memenuhi teks yang diperlukan dalam sel E1:

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

1. LEN (LEN) - menghitung panjang teks, satu argumen - teks. Contoh: LEN ( "mesin") = 6.
2. PENGGANTI (PENGGANTI) - menggantikan string teks teks tertentu dengan yang lain. Sintaks: PENGGANTI (teks; star_tekst; nov_tekst). Contoh: PENGGANTI ( "mobil", "mobil", "") = "mobile".
3. UPPER (UPPER) - menggantikan semua karakter dalam string ke huruf besar. Satu-satunya argumen - teks. Contoh: UPPER ( "mesin") = "MESIN". Fitur ini akan diperlukan untuk membuat pencarian kasus-sensitif. Setelah semua, UPPER ( "Machine") = UPPER ( "Machine")

Untuk mencari entri tertentu string teks yang lain, Anda harus menghapus semua kejadian dalam asli dan membandingkan panjang string dari aslinya:

LEN ( "Tarif super MTS MTS") - LEN ( "Super Rate") = 6

Dan kemudian membagi perbedaan dengan panjang garis, kita cari:

6 / LEN ( "MTS") = 2

Itulah dua kali garis "MTS" adalah dalam bahasa aslinya.

Ini masih harus ditulis dalam bahasa rumus algoritma (dilambangkan "text" teks yang kita mencari masuk dan "diinginkan" - jumlah kejadian yang kita tertarik):

= (LEN (teks) -DLSTR (PENGGANTI (UPPER (teks); UPPER (dicari-untuk); ""))) / LEN (dicari-untuk)

Dalam contoh kita, rumusnya adalah sebagai berikut:

= (LEN (A2) -DLSTR (PENGGANTI (UPPER (A2); UPPER ($ E $ 1); ""))) / LEN ($ E $ 1)