Cara membuat laporan di Excel. Cara membuat laporan yang terlihat profesional di Excel

Renat tidak lagi menjadi yang pertama kali mendukung tamu pada Lifehak. Sebelumnya, kami menerbitkan bahan yang hebat dari itu tentang cara membuat rencana latihan: dan sumber daya online, serta membuat rencana pelatihan.

Artikel ini berisi teknik yang tidak rumit yang memungkinkan. Terutama mereka akan berguna bagi mereka yang terlibat dalam pelaporan manajemen, menyiapkan berbagai laporan analitik berdasarkan bongkar muatan dari 1C dan laporan lain, membentuk presentasi dan diagram untuk manual. Saya tidak berpura-pura kebaruan mutlak - dalam satu atau yang lain, teknik-teknik ini mungkin dibahas di forum atau.

Alternatif sederhana untuk HDP dan GRP, jika nilai yang diinginkan tidak ada di kolom pertama tabel: Lihat, indeks + board pencarian

Hasil:

Dalam hal ini, selain fungsi, karakter (karakter) (untuk menampilkan kutipan) menggunakan fungsi jika (jika), yang memungkinkan Anda untuk mengubah teks tergantung pada apakah dinamika positif penjualan diamati, dan fungsi teks (teks ), yang memungkinkan Anda untuk menampilkan nomor dalam format apa pun. Sintaksnya dijelaskan di bawah ini:

Teks ( nilai; format)

Formatnya ditunjukkan dalam kutipan dengan cara yang sama seperti jika Anda memasukkan format khusus di jendela "Format Sel".

Anda dapat mengotomatiskan teks yang lebih kompleks. Dalam praktik saya ada komentar yang panjang, tetapi rutin ke laporan manajemen dalam format "Indikator turun / meningkat oleh XX relatif terhadap rencana ini terutama karena pertumbuhan / penurunan faktor1 pada XX, pertumbuhan / penurunan pada YY .. . "Dengan daftar faktor yang berubah. Jika Anda sering menulis komentar dan proses penulisan mereka dapat algoritma - biayanya sekali untuk membuat rumus atau makro yang akan menyingkirkan Anda setidaknya dari bagian dari pekerjaan.

Cara menyimpan data di setiap sel setelah serikat pekerja

Saat menggabungkan sel, hanya satu nilai yang disimpan. Excel memperingatkan tentang hal itu ketika mencoba menggabungkan sel:

Dengan demikian, jika Anda memiliki formula yang tergantung pada setiap sel, itu akan berhenti bekerja setelah merger mereka (kesalahan # n / d dalam baris 3-4 dari contoh):

Untuk menggabungkan sel dan pada saat yang sama menyimpan data di masing-masing (mungkin Anda memiliki formula, seperti dalam contoh abstrak ini; Anda mungkin ingin menggabungkan sel, tetapi menyimpan semua data untuk masa depan atau menyembunyikannya), Gabungkan sel apa pun pada lembar, sorot, dan kemudian gunakan perintah "Contoh Format", transfer pemformatan ke sel-sel yang perlu Anda gabungkan:

Cara Membangun Ringkasan Beberapa Sumber Data

Jika Anda perlu membangun kononsolidasi segera dari berbagai sumber data, Anda harus menambah pita atau panel akses cepat "Master dari tabel dan grafik konsolidasi", di mana ada opsi seperti itu.

Ini dapat dilakukan sebagai berikut: "File" → "Parameter" → "Panel Akses Cepat" → "Semua perintah" → "Master dari tabel dan grafik" → "Tambah": "

Setelah itu, ikon yang sesuai muncul pada kaset, menekan yang merupakan penyihir itu sendiri:

Ketika Anda mengkliknya, kotak dialog muncul:

Di dalamnya Anda perlu memilih item "Dalam beberapa rentang konsolidasi" dan klik "Next". Pada item berikutnya, Anda dapat memilih "Buat satu halaman halaman" atau "Buat bidang halaman". Jika Anda ingin secara mandiri muncul dengan nama untuk masing-masing sumber data - pilih item kedua:

Di jendela berikutnya, tambahkan semua rentang berdasarkan yang akan dikonsolidasikan, dan mengaturnya:

Setelah itu, di kotak dialog terakhir, tentukan di mana laporan tabel konsolidasi akan diposting - pada satu atau lembar baru:

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

Cara menghitung jumlah tepi teks A ke teks B ("supermtes treasury mts" - dua entri dari singkatan MTS)

Dalam contoh ini, di kolom A ada beberapa string teks, dan tugas kita adalah mencari tahu berapa kali di masing-masing ditemukan 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 ("Machine") \u003d 6.
  2. Pengganti - menggantikan teks tertentu dalam garis teks kepada orang lain. Sintaks: Pengganti ( teks; Star_text; New_text.). Contoh: pengganti ("mobil"; "otomatis"; "") \u003d "mobile".
  3. Terdaftar (atas) - Mengganti semua karakter di baris ke modal. Satu-satunya argumen adalah teks. Contoh: Propontif ("Mesin") \u003d "Mesin". Fitur ini akan diperlukan untuk melakukan pencarian tanpa mendaftar. Lagi pula, terdaftar ("mesin") \u003d terdaftar ("mesin")

Untuk menemukan entri string teks yang pasti ke yang lain, Anda perlu menghapus semua entri ke dalam yang asli dan membandingkan panjang garis yang dihasilkan dengan sumber:

DLSTR ("MTS Super Mts Tarif") - DLSTR ("Super Tariff) \u003d 6

Dan kemudian membagi perbedaan ini untuk panjang garis yang kami cari:

6 / DLSTR ("MTS") \u003d 2

Itu dua kali garis "MTS" termasuk dalam yang pertama.

Masih mencatat algoritma ini dalam bahasa formula (kami menunjukkan oleh teks yang kami cari, dan "Iskim" adalah jumlah entri yang menarik bagi AS):

\u003d (DLSTR ( teks) -Dlster (substitusi (terdaftar ( teks); Terdaftar ( bodoh); "")) / DLSTR ( bodoh)

Dalam contoh kami, rumus terlihat seperti ini:

\u003d (DLSTR (A2) -Dlster (substitusi (terdaftar (A2); terdaftar ($ E $ 1); "")) / DLSTR ($ E $ 1)

Ringkasan Tabel di Excel - alat yang ampuh untuk membuat laporan. Ini sangat berguna dalam kasus-kasus di mana pengguna bekerja dengan baik dengan formula dan sulit baginya untuk secara mandiri membuat analisis data. Dalam artikel ini, kita akan melihat bagaimana cara membuat tabel serupa dan peluang mana yang ada di Editor Excel. Tidak ada file unduhan untuk ini. Pelatihan tersedia secara online.

Pertama-tama, Anda perlu membuat beberapa tabel. Diinginkan ada beberapa kolom. Dalam hal ini, beberapa informasi harus diulang, karena hanya dalam hal ini akan memungkinkan untuk membuat analisis informasi yang dimasukkan.

Misalnya, pertimbangkan biaya keuangan yang sama pada bulan yang berbeda.

Membuat tabel ringkasan

Untuk membangun tabel yang sama, Anda harus menggambar tindakan berikut.

  1. Untuk memulai, harus sepenuhnya dialokasikan.

  1. Kemudian pergi ke tab "Sisipkan". Klik ikon "Tabel". Dalam menu yang muncul, pilih "Ringkasan Tabel".

  1. Akibatnya, sebuah jendela akan muncul di mana Anda perlu menentukan beberapa parameter dasar untuk membangun tabel konsolidasi. Pertama-tama, Anda harus memilih area data berdasarkan mana analisis akan dilakukan. Jika Anda sebelumnya mengidentifikasi tabel, maka tautannya akan diatur secara otomatis. Kalau tidak, perlu dialokasikan.

  1. Maka Anda akan diminta untuk menunjukkan di mana konstruksi akan terjadi. Lebih baik memilih item "pada lembar yang ada", karena akan merepotkan untuk menganalisis informasi ketika semuanya tersebar ke dalam beberapa lembar. Maka Anda harus menentukan rentang. Untuk melakukan ini, klik ikon di dekat bidang input.

  1. Segera setelah itu, Wizard Penciptaan akan berukuran kecil. Selain itu, penampilan kursor akan berubah. Anda harus membuat klik mouse kiri di tempat mana pun nyaman bagi Anda.

  1. Akibatnya, referensi ke sel yang ditentukan akan secara otomatis diganti. Maka Anda perlu mengklik ikon di sisi kanan jendela untuk memulihkannya ke ukuran sumber.

  1. Untuk menyelesaikan pengaturan, Anda perlu mengklik tombol "OK".

  1. Akibatnya, Anda akan melihat templat kosong untuk bekerja dengan tabel konsolidasi.

  1. Pada tahap ini, Anda harus menentukan bidang mana yang akan:
    1. kolom;
    2. baris;
    3. nilai analisis.

Anda dapat memilih apa pun. Itu semua tergantung pada informasi apa yang ingin Anda dapatkan.

  1. Untuk menambahkan bidang apa pun, Anda perlu membuat klik mouse kiri dan, tanpa melepaskan jari-jari Anda, seret ke area yang diinginkan. Dalam hal ini, kursor mengubah penampilannya.

  1. Anda dapat melepaskan jari hanya ketika lingkaran yang disilangkan menghilang. Demikian pula, Anda perlu menarik semua bidang yang ada di tabel Anda.

  1. Untuk melihat hasil keseluruhan, Anda dapat menutup panel samping pengaturan. Untuk melakukan ini, cukup klik pada salib.

  1. Akibatnya, Anda akan melihat yang berikut ini. Dengan alat ini, Anda dapat mengurangi jumlah pengeluaran di setiap bulan untuk setiap posisi. Selain itu, informasi tersedia pada hasil umum.

  1. Jika Anda tidak menyukai meja, Anda dapat mencoba membangunnya sedikit berbeda. Untuk melakukan ini, Anda perlu mengubah bidang di bidang konstruksi.

  1. Tutup asisten untuk membangun lagi.

  1. Kali ini kita melihat bahwa tabel konsolidasi telah menjadi lebih, karena sekarang bukan bulan-bulan kolom, tetapi kategori pengeluaran.

Jika Anda tidak dapat membangun meja sendiri, Anda selalu dapat mengandalkan bantuan editor. Di Excel, ada kemungkinan membuat objek seperti itu dalam mode otomatis.

Untuk melakukan ini, Anda perlu melakukan yang berikut, tetapi pertama-tama pilih semua informasi.

  1. Klik tab Sisipkan. Kemudian klik ikon "Tabel". Di menu yang muncul, pilih item kedua.

  1. Segera setelah itu, sebuah jendela akan muncul di mana akan ada contoh yang berbeda untuk konstruksi. Pilihan seperti itu ditawarkan berdasarkan beberapa kolom. Jumlah template langsung tergantung pada kuantitas mereka.

  1. Ketika Anda melayang untuk setiap item akan tersedia pratinjau hasilnya. Jadi pekerjaannya jauh lebih nyaman.

  1. Anda dapat memilih apa yang paling Anda sukai.

  1. Untuk memasukkan opsi yang dipilih, klik tombol "OK".

  1. Akibatnya, Anda akan mendapatkan hasil berikut.

Harap dicatat: Tabel dibuat pada lembar baru. Ini akan terjadi setiap kali menggunakan perancang.

Setelah Anda menambahkan (tidak peduli bagaimana) tabel ringkasan, Anda akan melihat tab baru "analisis" pada bilah alat. Ini berisi sejumlah besar alat dan fungsi yang berbeda.

Pertimbangkan masing-masing secara lebih rinci.

Dengan mengklik tombol yang ditandai dalam tangkapan layar, Anda dapat melakukan hal berikut:

  • ubah nama;

  • hubungi jendela Pengaturan.

Di jendela Opsi, Anda akan melihat banyak hal menarik.

Bidang aktif

Dengan alat ini, Anda dapat melakukan hal berikut:

  1. Pertama, Anda perlu menyoroti beberapa sel. Kemudian klik tombol "Bidang Aktif". Dalam menu yang muncul, klik pada bidang "parameter bidang".

  1. Segera setelah ini, Anda akan melihat jendela berikut. Di sini Anda dapat menentukan jenis operasi untuk digunakan untuk informasi di bidang yang dipilih.

  1. Selain itu, Anda dapat mengonfigurasi format numerik. Untuk melakukan ini, klik tombol yang sesuai.

  1. Akibatnya, jendela "format sel" akan muncul.

Di sini Anda dapat menentukan, dalam bentuk seperti apa yang Anda butuhkan untuk menampilkan hasil analisis informasi.

Berkat alat ini, Anda dapat mengkonfigurasi pengelompokan dengan nilai-nilai khusus.

Masukkan irisan

Microsoft Excel Editor memungkinkan Anda membuat tabel ringkasan interaktif. Pada saat yang sama, tidak ada yang sulit dilakukan.

  1. Sorot beberapa kolom. Kemudian klik tombol "Sisipkan Tidur".
  2. Di jendela yang muncul, sebagai contoh, pilih salah satu bidang yang diusulkan (di masa depan Anda dapat mengalokasikannya dalam jumlah yang tidak terbatas). Setelah sesuatu dipilih, tombol "OK" segera diaktifkan. Klik itu.

  1. Akibatnya, jendela kecil akan muncul, yang dapat dipindahkan ke mana saja. Ini akan menawarkan semua nilai unik yang mungkin ada di bidang ini. Berkat alat ini, Anda dapat menarik jumlah hanya untuk bulan-bulan tertentu (dalam hal ini). Informasi default ditampilkan untuk setiap saat.

  1. Anda dapat mengklik salah satu item. Segera setelah itu, jumlahnya akan mengubah semua nilai di bidang.

  1. Jadi ternyata memilih selang waktu.

  1. Kapan saja, semuanya dapat dikembalikan ke penampilan aslinya. Untuk melakukan ini, klik ikon di sudut kanan atas jendela ini.

Dalam hal ini, kami dapat mengurutkan laporan berdasarkan bulan, karena kami memiliki bidang yang sesuai. Tetapi untuk bekerja dengan tanggal terdapat alat yang lebih kuat.

Jika Anda mengklik tombol yang sesuai pada bilah alat, kemungkinan besar Anda akan melihat kesalahan ini. Faktanya adalah bahwa tidak ada sel di tabel kami bahwa akan ada format data "tanggal" secara eksplisit.

Sebagai contoh, buat meja kecil dengan tanggal yang berbeda.

Maka Anda perlu membangun tabel konsolidasi.

Buka tab "Sisipkan". Klik ikon "Tabel". Dalam submenu yang muncul, pilih opsi yang Anda butuhkan.

  1. Kemudian kami akan diminta untuk memilih berbagai nilai.

  1. Untuk melakukan ini, sudah cukup untuk menyoroti seluruh tabel sepenuhnya.

  1. Segera setelah itu, alamat akan secara otomatis diganti. Semuanya sangat sederhana di sini, karena dirancang untuk boneka. Untuk menyelesaikan konstruksi, klik tombol "OK".

  1. Evicel Editor akan menawarkan hanya satu opsi, karena tabelnya sangat sederhana (misalnya, tidak perlu lagi).

  1. Coba lagi untuk mengklik ikon "Sisipkan Timeline" (terletak pada tab "Analisis").

  1. Kali ini tidak akan ada kesalahan. Anda akan ditawari untuk memilih bidang penyortiran. Centang dan klik tombol "OK".

  1. Karena ini, jendela akan muncul, di mana Anda dapat memilih tanggal yang diinginkan menggunakan pelari.

  1. Kami memilih bulan lain dan tidak ada data, karena semua biaya dalam tabel diindikasikan hanya untuk bulan Maret.

Jika Anda telah membuat perubahan pada data sumber dan untuk beberapa alasan itu tidak ditampilkan dalam tabel konsolidasi, Anda selalu dapat memperbaruinya secara manual. Untuk melakukan ini, cukup klik pada tombol yang sesuai pada bilah alat.

Jika Anda memutuskan untuk mengubah bidang, berdasarkan mana konstruksi harus terjadi, jauh lebih mudah untuk melakukan ini dalam pengaturan, dan tidak menghapus tabel dan membuatnya lagi dengan preferensi baru.

Untuk melakukan ini, klik ikon sumber data. Kemudian pilih item menu yang terlihat.

Akibatnya, sebuah jendela akan muncul di mana Anda dapat mengidentifikasi kembali jumlah informasi yang diinginkan.

Tindakan

Dengan alat ini Anda dapat:

  • tabel yang jelas;
  • mengalokasikan;
  • pindahkan.

Perhitungan

Jika perhitungan dalam tabel tidak cukup atau mereka tidak memenuhi kebutuhan Anda, Anda selalu dapat melakukan perubahan. Dengan mengklik ikon alat ini, Anda akan melihat opsi-opsi berikut.

Ini termasuk:

  • bidang yang dihitung;

  • objek yang dihitung;

  • prosedur perhitungan (formula ditambahkan ditampilkan dalam daftar);

  • tarik formula (tidak ada informasi, karena tidak ada formula tambahan).

Di sini Anda dapat membuat diagram ringkasan atau mengubah jenis tabel yang direkomendasikan.

Menggunakan alat ini, Anda dapat mengonfigurasi tampilan ruang pengembangan editor.

Terima kasih untuk ini, Anda dapat:

  • konfigurasikan tampilan bilah samping dengan daftar bidang;

  • mengaktifkan atau menonaktifkan tombol Plus / Mini C;

  • konfigurasikan tampilan header bidang.

Saat bekerja dengan tabel konsolidasi, selain tab "Analisis", satu lagi - "Desainer" juga akan muncul. Di sini Anda dapat mengubah tampilan objek Anda sampai tidak dapat dikenali dibandingkan dengan opsi default.

Anda dapat mengkonfigurasi:

  • subtotal:
    • jangan tampilkan;
    • tunjukkan semua hasil di bagian bawah;
    • tampilkan semua hasil dalam judul.

  • hasil Umum:
    • nonaktifkan untuk string dan kolom;
    • aktifkan untuk baris dan kolom;
    • aktifkan hanya untuk string;
    • aktifkan hanya untuk kolom.

  • laporan Laporan:
    • tampilkan dalam bentuk terkompresi;
    • tampilkan dalam bentuk struktur;
    • tampilkan dalam bentuk tabel;
    • ulangi semua tanda tangan elemen;
    • jangan ulangi tanda tangan elemen.

  • garis kosong:
    • masukkan string kosong setelah setiap elemen;
    • hapus string kosong setelah setiap elemen.

  • posting pengaturan gaya tabel (di sini Anda dapat mengaktifkan / menonaktifkan setiap item):
    • header string;
    • tajuk utama kolom;
    • garis bolak-balik;
    • kolom bolak-balik.

  • konfigurasikan gaya elemen desain.

Untuk melihat opsi yang lebih berbeda, Anda perlu mengklik Segitiga di sudut kanan bawah alat ini.

Segera setelah itu, daftar besar akan muncul. Anda dapat memilih apa pun. Ketika Anda melayang untuk setiap templat, tabel Anda akan berubah (ini dilakukan untuk pratinjau). Perubahan tidak akan berlaku saat Anda tidak mengklik salah satu opsi yang diusulkan.

Selain itu, jika Anda mau, Anda dapat membuat gaya desain Anda sendiri.

Di sini Anda dapat mengubah urutan tampilan baris. Terkadang diperlukan untuk kenyamanan analisis pengeluaran. Apalagi jika daftarnya sangat besar, karena posisi yang diinginkan lebih mudah ditemukan secara alfabet daripada membalik daftar beberapa kali.

Untuk melakukan ini, lakukan hal berikut.

  1. Klik pada segitiga di dekat bidang yang diinginkan.
  2. Akibatnya, Anda akan melihat menu berikutnya. Di sini Anda dapat memilih opsi penyortiran yang diinginkan ("dari A hingga Z" atau "dari I hingga A").

Jika opsi standar tidak cukup, Anda dapat mengklik item "Pengaturan Sortasi Lanjutan" di menu yang sama.

Akibatnya, Anda akan melihat jendela berikut. Untuk pengaturan yang lebih rinci, Anda perlu mengklik tombol "Advanced".

Di sini semuanya dikonfigurasi dalam mode otomatis. Jika Anda menghapus centang ini, Anda dapat menentukan kunci yang Anda butuhkan.

Ringkasan Tabel di Excel 2003

Tindakan yang dijelaskan di atas cocok untuk editor modern (2007, 2010, 2013 dan 2016). Dalam versi lama, semuanya terlihat berbeda. Peluang, tentu saja, ada jauh lebih sedikit.

Untuk membuat tabel konsolidasi di Excel 2003, Anda perlu melakukan hal berikut.

  1. Buka bagian menu "Data" dan pilih item yang sesuai.

  1. Akibatnya, seorang master akan muncul untuk menciptakan objek tersebut.

  1. Setelah mengklik tombol "Next", sebuah jendela akan terbuka di mana Anda ingin menentukan rentang sel. Kemudian tekan lagi "Next".

  1. Untuk melengkapi pengaturan, klik "Siap."

  1. Akibatnya, Anda akan melihat yang berikut ini. Di sini Anda perlu menyeret bidang ke area yang sesuai.

  1. Misalnya, hasil ini mungkin berubah.

Menjadi jelas bahwa jauh lebih baik untuk menciptakan laporan seperti itu di editor modern.

Kesimpulan

Artikel ini mencakup semua detail bekerja dengan tabel konsolidasi di Editor Excel. Jika sesuatu tidak berfungsi, Anda dapat mengalokasikan bukan bidang itu atau sangat sedikit dari mereka - untuk membuat objek seperti itu ada beberapa kolom dengan nilai berulang.

Jika tutorial ini tidak cukup untuk Anda, informasi tambahan dapat ditemukan di Microsoft Online Bantuan.

Instruksi video

Bagi mereka yang masih memiliki pertanyaan tanpa menjawab, video dengan komentar untuk instruksi yang dijelaskan di atas terlampir.

Tonton video ke artikel:


Laporan khas yang termasuk dalam kami modul Bisnis Intelijen (BI) Solusi Ready Memungkinkan untuk menanggapi 99% dari permintaan pengguna paling populer.

Tetapi jika diinginkan, analis atau pengguna lain dapat membuat laporan mereka di Excel pada kubus olap.

Terimakasih untuk di jantung setiap modul bisnis - analis (BI) terletak olap cubeDengan menghubungkan ke mana pengguna dapat dengan mudah membangun laporan apa pun di Excel menggunakan tabel ringkasan.

Juga dibangun dalam laporan Excel pada kubus olap dapat disimpan, dan ketika membuka kembali, mereka akan secara otomatis diperbarui dengan data saat ini dari sistem Korporat Analytics Bisnis (BI) .

Contoh laporan yang dibangun di atas kubus olap:

Deskripsi laporan:

(Proses Building Reports Lihat di video ke artikel ini)

1. Laporan Penjualan dalam Rubel dan Unit:
  • titik: untuk tahun berjalan
  • indikator: Penjualan fakta
  • detail: Kolom / Kuartal, Bulan
  • detail: Baris / saluran penjualan, klien, merek, produk

2. Laporan struktur penjualan dalam rubles dan unit (filter):
  • titik: untuk tahun berjalan
  • indikator: Penjualan fakta, penjualan fakta dalam persen
  • detail: Kolom / Kuartal, Bulan
  • detail: Baris / Saluran Penjualan, Merek

3. Laporkan implementasi rencana penjualan dalam rubles dan unit (filter):
  • titik: untuk tahun berjalan
  • indikator: Rencana Penjualan, Fakta Penjualan, Rencana Implementasi
  • detail: Kolom / Kuartal, Bulan
  • detail: Baris / saluran penjualan, klien, kategori, produk

4. Laporan Keuntungan Penjualan dalam Rubel dan Unit (Filter):
  • titik
  • indikator
  • detail: Rows / Distrik Federal, Daerah, Kategori, Produk

5. Laporan peningkatan waktu penjualan dalam rubel dan unit (filter):
  • titik: Untuk tahun ini dibandingkan dengan sebelumnya
  • indikator: Penjualan tahun sebelumnya, rencana penjualan, fakta penjualan, pelaksanaan rencana, penjualan meningkat dalam persyaratan absolut dan relatif ke tahun sebelumnya
  • detail: Baris / seperempat, bulan, kategori, produk

6. Laporan Piutang:
  • titik: Pada tanggal saat ini
  • indikator: Jumlah dokumen, piutang keseluruhan, piutang saat ini, piutang yang kedaluwarsa untuk periode - hingga 15 hari, hingga 30 hari, hingga 60 hari, hingga 90 hari, hingga 180 hari, hingga 365 hari atau lebih , struktur piutang
  • detail: String / Sale Saluran, Counterparty, Dokumen Pembayaran Dokumen, Tanggal Dokumen, Dokumen

7. Laporan piutang:
  • titik: untuk tahun berjalan
  • indikator: Piutang umum, piutang saat ini, piutang overdue, struktur manajemen utang
  • detail: Baris / Tahun, Minggu, Counterparty

8. Laporkan Pembayaran Kedatangan dengan Tanggal Dokumen:
  • titik: untuk tahun berjalan
  • indikator: Jumlah dokumen, jumlah pembayaran dokumen, penyimpangan untuk pembayaran secara absolut dan relatif
  • detail: Baris / tahun, bulan, rekanan, jenis dokumen, tanggal dokumen, nomor dokumen

9. Pembayaran Laporkan Kedatangan dengan Tanggal Pembayaran:
  • titik: untuk tahun berjalan
  • indikator: Jumlah pembayaran, jumlah distribusi pembayaran, penyimpangan distribusi secara absolut dan relatif
  • detail: Baris / tahun, bulan, counterparty, jenis pembayaran, tanggal pembayaran, ruang pembayaran

10. Laporan Cadangan Komoditas:
  • titik: Pada tanggal saat ini
  • indikator: Saldo barang, tanggal kedaluwarsa residu dalam persen (oleh DSO), saldo barang dalam hal kesesuaian - istilah 0%, jangka waktu 10-30%, jangka waktu 40-60%, jangka waktu 70-90%, , istilah 99%
  • detail: string / kategori, produk, status, gudang, Tanggal dokumen paroki

Halo semua! Bahan hari ini bagi mereka yang terus mengembangkan pekerjaan dengan aplikasi, dan tidak tahu bagaimana membuat tabel konsolidasi di Excel.

Setelah membuat tabel umum di salah satu dokumen teks, dimungkinkan untuk melaksanakan analisisnya dengan membuat tabel konsolidasi di Excel.

Membuat tabel Excel konsolidasi membutuhkan kepatuhan dengan kondisi tertentu:

  1. Data cocok dengan tabel di mana ada kolom dan daftar dengan nama.
  2. Tidak ada bentuk kosong.
  3. Kurangnya benda tersembunyi.

Cara membuat tabel konsolidasi di Excel: petunjuk langkah demi langkah

Untuk membuat tabel pivot yang Anda butuhkan:

Lembaran kosong dibuat, di mana daftar wilayah dan bidang terlihat. Headline menjadi ladang di meja baru kami. Tabel konsolidasi akan dibentuk dengan menyeret bidang.

Mereka akan dicentang, dan untuk kenyamanan analisis Anda akan mengubahnya di tempat-tempat di area tabular.


Saya memutuskan bahwa analisis data akan dilakukan melalui filter penjual untuk dilihat oleh siapa dan berapa bulan yang dijual, dan produk mana.

Pilih penjual tertentu. Klik mouse dan transfer bidang "Penjual" ke "Filter Laporan". Bidang baru ditandai, dan tampilan tabel berubah sedikit.


Kategori "barang" kami akan mengirimkan dalam bentuk baris. Dalam "judul baris" kami mentransfer bidang yang Anda butuhkan.


Untuk menampilkan daftar drop-down, ia memiliki nilai dalam urutan kami menunjukkan nama. Jika awalnya dalam baris membuat pilihan untuk mendukung barang, dan kemudian arahkan harga, maka barang hanyalah daftar drop-down, dan sebaliknya.

Kolom "Unit", berada di tabel utama, menampilkan jumlah barang yang dijual oleh penjual tertentu dengan harga tertentu.


Untuk menampilkan penjualan, misalnya, untuk setiap bulan, Anda memerlukan bidang "Tanggal" untuk meletakkan "nama kolom". Pilih perintah "Grind" dengan mengklik Tanggal.


Menunjukkan periode tanggal dan langkah. Konfirmasikan pilihan.

Kami melihat meja seperti itu.


Kami akan mentransfer bidang "Jumlah" ke area "Nilai".


Itu menjadi terlihat oleh tampilan angka, dan kami membutuhkan format numerik


Untuk memperbaiki, pilih sel dengan memanggil jendela dengan mouse, pilih "format numerik".

Format numerik yang kami pilih untuk jendela berikutnya dan rayakan "pemisah kelompok pelepasan". Konfirmasikan tombol "OK".

Pendaftaran tabel pivot

Jika kami memberikan kutu yang mengkonfirmasi alokasi beberapa objek sekaligus, kami dapat memproses data sekaligus ke beberapa penjual.


Penggunaan filter dimungkinkan untuk kolom dan baris. Menempatkan kutu pada salah satu varietas barang, Anda dapat mengetahui berapa banyak yang diimplementasikan oleh satu atau lebih penjual.


Secara terpisah mengkonfigurasi parameter bidang. Pada contoh, kami melihat bahwa penjual tertentu Roma dalam bulan beton menjual kemeja ke jumlah tertentu. Dengan menekan mouse di baris "Jumlah di atas bidang ..." Kami memanggil menu dan pilih "Parameter bidang bidang".


Di sebelah data ini di lapangan pilih "Kuantitas". Konfirmasikan pilihan.

Lihatlah meja. Jelas terlihat bahwa dalam satu bulan penjual menjual kemeja dalam jumlah 2 buah.


Sekarang kami mengubah tabel dan membuat filter bekerja berdasarkan bulan. Kolom "date" ditransfer ke "Filter Laporan", dan di mana "nama kolom" akan menjadi "Penjual". Tabel menampilkan seluruh periode penjualan atau untuk bulan tertentu.


Pemilihan sel dalam tabel pivot akan mengarah pada tampilan tab seperti "bekerja dengan tabel konsolidasi", dan akan ada dua tab lainnya "parameter" dan "desainer".


Anda benar-benar dapat menceritakan tentang pengaturan tabel konsolidasi untuk waktu yang sangat lama. Melakukan perubahan pada selera Anda, mencapai bermanfaat bagi Anda. Jangan takut untuk menekan dan bereksperimen. Tindakan apa pun yang selalu dapat Anda ubah dengan menekan kombinasi tombol Ctrl + Z.

Saya harap Anda mempelajari semua materi, dan sekarang Anda tahu cara membuat meja konsolidasi di Excel.

Untuk analisis tabel besar dan kompleks biasanya digunakan. Dengan bantuan formula, Anda juga dapat menerapkan pengelompokan dan analisis data yang tersedia. Buat laporan yang tidak rumit dengan bantuan formula.

Sebagai awal, kami akan menggunakan ( Sisipkan / Tabel / Tabel) Mengandung informasi tentang penjualan batch produk. Dalam baris tabel, data tentang pasokan batch produk dan pemasarannya diberikan. Tabel serupa digunakan dalam artikel .

Tabel memiliki kolom:

  • Produk - Nama batch barang, misalnya, " Jeruk»;
  • Kelompok - sekelompok barang, misalnya, " Jeruk"Masukkan grup" Buah-buahan»;
  • tanggal pengiriman - Tanggal pengiriman barang oleh pemasok;
  • Wilayah perbaikan - wilayah di mana batch barang diimplementasikan;
  • Penjualan - Biaya yang dimungkinkan untuk menerapkan batch barang;
  • Penjualan - Istilah penjualan barang yang sebenarnya di wilayah tersebut (dalam hari);
  • Keuntungan - Tanda apakah laba diterima dari batch barang yang terwujud.

Melalui Nama dispatcher. Perbaiki tabel pada " Resmi_tablitsa."(Lihat file contoh).

Dengan bantuan formula, buat 5 laporan sederhana yang akan ditempatkan pada lembar terpisah.

Laporkan №1 Total penjualan barang

Kami menemukan total penjualan setiap produk.
Tugas itu diselesaikan dengan cukup menggunakan fungsi diam (), namun, konstruksi laporan membutuhkan keterampilan tertentu untuk bekerja dengan beberapa alat Excel.

Jadi, lanjutkan. Pertama, kita perlu membentuk daftar nama produk. Karena Di kolom, nama tabel sumber diulang, maka kita perlu memilih hanya nilai darinya. Ini dapat dilakukan dalam beberapa cara: formula (lihat artikel), melalui menu Data / Data / Delete Duplikat atau dengan. Jika Anda menggunakan cara pertama, maka ketika menambahkan produk baru ke tabel sumber, nama baru akan dimasukkan dalam daftar secara otomatis. Tetapi di sini untuk kesederhanaan akan menggunakan cara kedua. Untuk ini:

  • Pergi ke lembar dengan tabel sumber;
  • Panggilan ( Data / Sortir dan Filter / Tingkat Lanjut);
  • Isi bidang seperti yang ditunjukkan pada gambar di bawah ini: Beralih diatur ke posisi Salin hasilnya ke tempat lain; Di bidang rentang sumber, masukkan $ A $ 4: $ A $ 530; Periksa bendera Hanya entri unik..

  • Salin daftar yang dihasilkan pada lembar di mana laporan akan ditempatkan;
  • Urutkan daftar barang ( Data / penyortiran dan filter / sortir dari A hingga Z).

Itu harus menjadi daftar berikut.

Di sel. B6. Kami memperkenalkan formula berikut, lalu salin ke akhir daftar:

Summase (source_tablik [produk]; A6; SUMPER_TABLIK [Jual])

JADWAL (SUMBER_TABLIK [produk]; A6)

Laporkan No. 2 penjualan barang berdasarkan wilayah

Kami menemukan total penjualan setiap produk di daerah.
Kami menggunakan daftar barang yang dibuat untuk laporan No. 1. Demikian pula, kami mendapatkan daftar nama daerah (di bidang rentang sumber, masukkan $ D $ 4: $ D $ 530).
Salin kisaran vertikal yang dihasilkan di Clipboard Dan itu horizontal. Kisaran yang dihasilkan yang berisi nama-nama daerah ditempatkan di header laporan.

Di sel. Dgn B. 8 Kami memperkenalkan formula berikut:

Smerevymn (source_tablik [penjualan];
Source_tablik [Produk]; $ A8;
Source_table [Region Sale]; B $ 7)

Formula akan mengembalikan total penjualan barang yang namanya terletak di sel A8. Di wilayah dari sel PUKUL 7 . Perhatikan penggunaan (referensi $ A8 dan B $ 7), akan diperlukan saat menyalin rumus untuk sel-sel tabel kosong lainnya.

Salin formula di atas di sel di sebelah kanan tidak akan berfungsi (ini dilakukan untuk laporan No. 1), karena Dalam hal ini di sel C8. Formula akan terlihat seperti ini:

Smerevymn (source_tablik [penjualan, hari];
Source_tablik [grup]; $ A8;
Source_Table [Penjualan]; C $ 7)

Laporkan Produk Penyaringan No. 3 untuk Profitabilitas

Mari kita kembali ke tabel sumber. Setiap batch barang baik membawa keuntungan atau tidak membawa (lihat kolom laba di tabel sumber). Hitung penjualan oleh kelompok barang tergantung pada profitabilitas. Untuk melakukan ini, akan menyaring menggunakan rumus perekaman tabel sumber pada bidang laba.

Frekuensi (Sumber_tablik [penjualan, hari]; A7: A12)

Untuk memasukkan formula, sorot rentangnya C6: C12. , lalu B. Row formula. Masukkan formula di atas dan klik Ctrl.+ Bergeser.+ MEMASUKKAN.

Hasil yang sama dapat diperoleh dengan menggunakan fungsi DIMMPROT () yang biasa:
\u003d Sumpacy ((Sumber_tablik [penjualan, hari]\u003e A6) *
(Sumber_tablik [Penjualan, Hari]<=A7))

Laporkan statistik pasokan barang №5

Sekarang siapkan laporan tentang pasokan barang untuk bulan itu.
Pertama buat daftar bulan demi tahun. Di tabel sumber, tanggal pengiriman paling awal 07/11/2009. Itu dapat dihitung menggunakan rumus:
\u003d Min (sumber_tablik [tanggal pengiriman])

Buat daftar tanggal - mulai dari tanggal pengiriman paling awal. Untuk melakukan ini, kami menggunakan rumus:
\u003d CONMINES ($ C $ 5; -1) +1

Akibatnya, kami akan menerima daftar tanggal - hari pertama bulan:

Menerapkan format sel yang sesuai, ubah tampilan tanggal:

Formula untuk menghitung jumlah barang yang dipasok untuk bulan ini:

SUMMAT ((SUMBER_TABLIK [Detail]\u003e \u003d B9) *
(Sumber_tablik [Tanggal Pengiriman]

Sekarang tambahkan baris untuk menghitung jumlah total pihak untuk setiap tahun. Untuk melakukan ini, Anda akan sedikit mengubah tabel, menyoroti tahun di kolom terpisah, yang disuplai menggunakan fungsi tahun ().

Sekarang untuk output pada tahun kita akan membuat struktur melalui item menu:

  • Sorot setiap sel dari tabel yang dimodifikasi;
  • Hubungi jendela melalui item menu Hasil Data / Struktur / Sementara;
  • Isi bidang seperti yang ditunjukkan pada Gambar:

Setelah menekan OK, tabel akan diubah sebagai berikut:

Hasil perantara akan dibuat pada tahun.
Dengan menekan tombol kecil di sudut kiri atas lembar, Anda dapat mengelola tampilan data di tabel.

Ringkasan:

Laporan yang mirip dengan yang dibuat, dapat dilakukan secara alami dengan atau menggunakan Saring Ke tabel sumber atau dengan fungsi bdmm () lainnya (), kulit kayu (), bsc (), dll. Pilihan pendekatan tergantung pada situasi spesifik.

Artikel serupa.