OLAP menjadi mudah! Kerja praktek teknologi olap di excel.

Pemrosesan Analitik Online (OLAP) adalah teknologi yang digunakan untuk mengatur database bisnis besar dan mendukung intelijen bisnis. Basis data OLAP dibagi menjadi satu atau lebih kubus, dan setiap kubus diatur oleh administrator kubus agar sesuai dengan cara data diambil dan dianalisis untuk memudahkan pembuatan dan penggunaan laporan PivotTable dan laporan PivotChart yang Anda perlukan.

Dalam artikel ini

Apa itu analisis bisnis?

Seorang analis bisnis sering kali ingin mendapatkan gambaran bisnis yang lebih luas untuk melihat tren yang lebih luas berdasarkan data agregat, serta melihat tren yang dipecah menjadi sejumlah variabel. Intelijen bisnis adalah proses mengekstraksi data dari database OLAP dan menganalisis data tersebut untuk menghasilkan wawasan yang dapat digunakan untuk membuat keputusan bisnis dan mengambil tindakan. Misalnya, dengan menggunakan OLAP dan analisis bisnis, Anda dapat menjawab pertanyaan berikut tentang data bisnis.

    Bagaimana total penjualan seluruh produk pada tahun 2007 dibandingkan dengan penjualan sejak tahun 2006?

    Bagaimana perbandingannya dengan tanggal dan waktu berdasarkan masa manfaat lima tahun terakhir?

    Berapa banyak uang yang dibelanjakan pelanggan selama 35 tahun terakhir dan bagaimana perilaku ini berubah seiring waktu?

    Berapa banyak produk yang terjual di dua negara/wilayah tertentu pada bulan ini dibandingkan dengan bulan yang sama tahun lalu?

    Untuk setiap kelompok umur pelanggan, berapa rincian profitabilitas (persentase margin dan total) berdasarkan kategori produk?

    Cari penjual, distributor, pemasok, klien, mitra, dan klien teratas dan terbawah.

Apa itu Pemrosesan Analitik Online (OLAP)?

Basis data OLAP (Pemrosesan Analitik Online) menyederhanakan pertanyaan intelijen bisnis. OLAP adalah teknologi database yang dioptimalkan untuk query dan laporan daripada pemrosesan transaksi. Sumber data untuk OLAP adalah database pemrosesan transaksi online (OLTP), yang biasanya disimpan di gudang data. Data OLAP diekstraksi dari data historis ini dan digabungkan menjadi struktur yang memungkinkan analisis kompleks. Data OLAP juga disusun secara hierarki dan disimpan dalam bentuk kubus, bukan tabel. Ini adalah teknologi kompleks yang menggunakan struktur multi-dimensi untuk menyediakannya akses cepat ke data untuk dianalisis. Di Organisasi ini, laporan PivotTable atau laporan PivotChart dapat dengan mudah menampilkan data ringkasan tingkat tinggi, seperti total penjualan untuk seluruh negara atau wilayah, dan juga menampilkan informasi tentang situs di mana penjualan sangat kuat atau lemah.

Basis data OLAP dirancang untuk mempercepat pemuatan data. Sejak server OLAP, dan tidak Microsoft Office Excel menghitung nilai agregat; data yang lebih kecil harus dikirim ke Excel saat Anda membuat atau mengedit laporan. Pendekatan ini memungkinkan Anda bekerja dengan lebih banyak data mentah dibandingkan jika data disusun dalam database tradisional, di mana Excel mengambil semua catatan individual dan menghitung nilai agregat.

Basis data OLAP berisi dua tipe data utama: ukuran, yaitu data numerik, kuantitas, dan rata-rata yang digunakan untuk membuat keputusan bisnis, dan dimensi, yaitu kategori yang digunakan untuk mengatur ukuran tersebut. Basis data OLAP membantu Anda mengatur data melalui berbagai tingkat detail, menggunakan kategori yang sama yang Anda ketahui untuk menganalisis data.

Bagian berikut menjelaskan setiap komponen secara rinci di bawah ini.

Kubik Struktur data yang mengelompokkan ukuran ke dalam tingkatan dan hierarki setiap dimensi yang ingin Anda analisis. Kubus menggabungkan beberapa dimensi seperti waktu, geografi, dan lini produk dengan ringkasan data seperti penjualan dan inventaris. Kubus bukanlah "Kubus" dalam pengertian matematis yang ketat, karena mereka belum tentu memiliki sisi yang sama. Namun, mereka mewakili metafora yang tepat untuk konsep yang kompleks.

Pengukuran Sekumpulan nilai dalam Kubus yang didasarkan pada kolom dalam tabel fakta kubus dan biasanya berupa nilai numerik. Ukuran adalah nilai sentral dalam Kubus yang diproses sebelumnya, diproses, dan dianalisis. Contoh paling umum adalah penjualan, pendapatan, pendapatan, dan pengeluaran.

Anggota Elemen dalam hierarki yang mewakili satu atau lebih kemunculan data. Suatu elemen dapat bersifat unik atau tidak unik. Misalnya, tahun 2007 dan 2008 mewakili anggota unik pada tingkat tahun dalam dimensi waktu, sedangkan Januari mewakili anggota tidak unik pada tingkat bulan karena terdapat lebih dari satu bulan Januari dalam dimensi waktu karena berisi data lebih dari satu tahun.

Elemen terhitung Anggota dimensi yang nilainya dihitung pada saat run time menggunakan ekspresi. Nilai anggota yang dihitung dapat diturunkan dari nilai anggota lainnya. Misalnya, elemen terhitung, laba, dapat ditentukan dengan mengurangkan nilai elemen, ditambah biaya, dari nilai elemen, penjualan.

pengukuran Sekumpulan satu atau lebih hierarki tingkat Kubus yang dipahami dan digunakan pengguna sebagai dasar analisis data. Misalnya, dimensi geografis mungkin mencakup tingkat negara/wilayah, negara bagian/wilayah, dan kota. Selain itu, dimensi waktu dapat mencakup hierarki dengan tingkatan tahun, kuartal, bulan, dan hari. Dalam laporan PivotTable atau laporan PivotChart, setiap hierarki menjadi kumpulan bidang yang bisa Anda perluas dan ciutkan untuk memperlihatkan lebih rendah atau lebih tinggi level tinggi.

Hirarki Struktur pohon logis yang menyusun anggota suatu dimensi sehingga setiap anggota mempunyai satu anggota induk dan nol atau lebih anak. Anak adalah anggota grup sebelumnya dalam hierarki yang berhubungan langsung dengan anggota saat ini. Misalnya, dalam hierarki waktu yang berisi tingkat kuartal, bulan, dan hari, Januari adalah turunan dari Qtr1. Elemen induk adalah anggota tingkat bawah dalam hierarki yang berhubungan langsung dengan anggota saat ini. Nilai induk biasanya merupakan konsolidasi nilai semua elemen turunan. Misalnya, dalam hierarki waktu yang berisi tingkat kuartal, bulan, dan hari, Qtr1 adalah induk dari bulan Januari.

Tingkat Dalam hierarki, data dapat disusun ke dalam tingkat granularitas yang lebih rendah dan lebih tinggi, seperti tingkat tahun, kuartal, bulan, dan hari dalam hierarki waktu.

Fungsi OLAP di Excel

Mengambil Data OLAP Anda dapat menyambung ke sumber data OLAP sama seperti Anda menyambung ke sumber data eksternal lainnya. Anda dapat bekerja dengan database yang dibuat menggunakan layanan Microsoft SQLServer OLAP versi 7.0, Layanan Analisis Microsoft SQL Server versi 2000 dan Layanan Analisis Microsoft SQL Server versi 2005, produk server Microsoft OLAP. Excel juga bisa bekerja dengan produk OLAP pihak ketiga yang kompatibel dengan OLE-DB untuk OLAP.

Data OLAP hanya bisa ditampilkan sebagai laporan PivotTable atau laporan PivotChart, atau dalam fungsi lembar kerja yang dikonversi dari laporan PivotTable, namun tidak sebagai rentang data eksternal. Anda bisa menyimpan laporan OLAP PivotTable dan PivotChart di templat laporan, dan membuat file Office Data Connection (ODC) untuk menyambungkan ke database OLAP untuk kueri OLAP. Saat Anda membuka file ODC di Excel, Anda melihat laporan PivotTable kosong yang siap ditempatkan.

Membuat file kubus untuk penggunaan offline Anda dapat membuat file kubus (.cub) yang berdiri sendiri dengan subset data dari database server OLAP. File kubus offline digunakan untuk bekerja dengan data OLAP saat Anda tidak tersambung ke jaringan. Dengan kubus, Anda bisa bekerja dengan lebih banyak data dalam laporan PivotTable atau laporan PivotChart dibandingkan yang biasa Anda lakukan dan mendapatkan data lebih cepat. Anda dapat membuat file kubus hanya jika Anda menggunakan penyedia OLAP, seperti Microsoft SQL Analysis Services versi 2005, yang mendukung fitur ini.

Tindakan server Tindakan server adalah fungsi opsional yang dapat ditentukan oleh administrator kubus OLAP di server yang menggunakan elemen kubus atau ukuran sebagai parameter dalam kueri untuk mengambil informasi dalam kubus, atau untuk meluncurkan aplikasi lain, seperti browser. Excel mendukung URL, laporan, kumpulan baris, penelusuran, dan penelusuran sisi server, namun tidak mendukung pernyataan dan kumpulan data aslinya sendiri.

KPI Indikator kinerja utama adalah ukuran perhitungan khusus yang ditentukan di server yang memungkinkan Anda melacak "indikator kinerja utama" termasuk status (nilai saat ini sesuai dengan nomor tertentu). dan tren (nilai seiring waktu). Saat ditampilkan, server dapat mengirimkan ikon terkait, mirip dengan ikon Excel baru, untuk berbaris di atas atau di bawah tingkat status (misalnya, untuk ikon berhenti), serta memutar nilai ke atas atau ke bawah (misalnya, ikon panah arah).

Memformat di server Administrator kubus dapat membuat ukuran dan menghitung anggota menggunakan pemformatan warna, pemformatan font, dan aturan pemformatan bersyarat yang dapat ditetapkan sebagai aturan bisnis standar perusahaan. Misalnya, format server untuk pendapatan mungkin berupa format angka mata uang, warna sel berwarna hijau jika nilainya lebih besar dari atau sama dengan 30.000 dan merah jika nilainya kurang dari 30.000, dan gaya fontnya tebal jika nilainya adalah kurang dari atau sama dengan 30.000 dan jika nilainya positif - biasa saja. lebih besar atau sama dengan 30.000. informasi tambahan dapat ditemukan .

Bahasa antarmuka kantor Administrator kubus bisa menentukan terjemahan data dan kesalahan di server untuk pengguna yang harus melihat informasi PivotTable dalam bahasa lain. Fungsi ini didefinisikan sebagai properti koneksi file, dan lokal komputer pengguna serta negara harus sesuai dengan bahasa antarmuka.

Komponen perangkat lunak diperlukan untuk mengakses sumber data OLAP

Penyedia OLAP Untuk mengonfigurasi sumber data OLAP untuk Excel, Anda memerlukan salah satu penyedia OLAP berikut.

    Penyedia Microsoft OLAP Excel menyertakan driver sumber data dan klien perangkat lunak untuk mengakses database yang dibuat menggunakan layanan olap Microsoft SQL Server versi 7.0, olap Microsoft SQL Server versi 2000 (8.0) dan layanan Analisis Microsoft SQL Server versi 2005 (9.0).

    Penyedia OLAP Pihak Ketiga Produk OLAP lainnya memerlukan instalasi driver tambahan dan perangkat lunak klien. Untuk menggunakan kemampuan Excel dalam bekerja dengan data OLAP, produk pihak ketiga harus mematuhi standar OLE-DB untuk OLAP dan kompatibel dengan Microsoft Office. Untuk informasi tentang menginstal dan menggunakan penyedia OLAP pihak ketiga, lihat administrator sistem atau vendor produk OLAP Anda.

Database server dan file kubus Perangkat lunak klien Unggul OLAP mendukung koneksi ke dua jenis database OLAP. Jika database di server OLAP sedang online, Anda dapat mengambil data sumber langsung dari database tersebut. Jika Anda memiliki file kubus mandiri yang berisi data OLAP atau file definisi kubus, Anda dapat menyambungkan ke file tersebut dan mendapatkan data sumber darinya.

Sumber data Sumber data menyediakan akses ke semua data dalam database OLAP atau file kubus offline. Setelah Anda membuat sumber data OLAP, Anda bisa mendasarkan laporan pada sumber tersebut dan mengembalikan data OLAP ke Excel sebagai laporan PivotTable atau laporan PivotChart, atau dalam fungsi lembar kerja yang dikonversi dari laporan PivotTable.

Kueri Microsoft Menggunakan Query Anda bisa mendapatkan data darinya basis eksternal data, seperti Microsoft SQL atau Microsoft Access. Anda tidak perlu menggunakan kueri untuk mengambil data dari tabel pivot OLAP yang terkait dengan file kubus. Informasi tambahan .

Perbedaan fitur sumber data OLAP dan non-OLAP

Jika Anda bekerja dengan laporan PivotTable dan PivotChart dari data sumber OLAP dan tipe data sumber lainnya, Anda akan melihat beberapa perbedaan fitur.

Ekstraksi data Server OLAP mengembalikan data baru ke Excel setiap kali tata letak laporan berubah. Dengan tipe sumber data eksternal lainnya, Anda mengkueri semua data sumber sekaligus, atau Anda bisa menentukan parameter untuk dikueri hanya saat menampilkan elemen bidang filter laporan yang berbeda. Selain itu, Anda memiliki beberapa opsi lain untuk memperbarui laporan Anda.

Dalam laporan berdasarkan data sumber OLAP, opsi bidang filter laporan tidak tersedia, kueri latar belakang tidak tersedia, dan opsi pengoptimalan memori tidak tersedia.

Catatan: Opsi pengoptimalan memori juga tidak tersedia untuk sumber data OLEDB dan laporan PivotTable berdasarkan rentang sel.

Jenis bidang Data sumber OLAP. bidang dimensi hanya dapat digunakan sebagai baris (rows), kolom (category), atau bidang halaman. Bidang pengukuran hanya dapat digunakan sebagai bidang nilai. Untuk tipe data sumber lainnya, semua bidang dapat digunakan di bagian mana pun dalam laporan.

Akses ke data terperinci Untuk data sumber OLAP, server menentukan tingkat detail yang tersedia dan menghitung nilai ringkasan, sehingga rekaman detail yang membentuk nilai ringkasan mungkin tidak tersedia. Namun, server mungkin menyediakan bidang properti yang dapat Anda tampilkan. Tipe data sumber lainnya tidak memiliki bidang properti, namun Anda bisa menampilkan informasi dasar untuk bidang data dan nilai elemen, dan juga menampilkan elemen tanpa data.

Bidang filter laporan OLAP mungkin tidak ada Semua elemen, dan tim Tampilkan halaman filter laporan tidak tersedia.

Urutan pengurutan awal Untuk data sumber OLAP, elemen pertama kali ditampilkan sesuai urutan yang dikembalikan oleh server OLAP. Anda dapat mengurutkan atau menyusun ulang item secara manual. Untuk tipe data sumber lainnya, elemen laporan baru diurutkan terlebih dahulu berdasarkan nama elemen dalam urutan menaik.

Nimi Server OLAP memberikan nilai ringkasan langsung ke laporan, sehingga Anda tidak dapat mengubah fungsi ringkasan untuk bidang nilai. Untuk tipe data sumber lainnya, Anda bisa mengubah fungsi agregasi untuk bidang nilai dan menggunakan beberapa fungsi ringkasan untuk bidang nilai yang sama. Anda tidak dapat membuat bidang terhitung dan anggota terhitung dalam laporan dengan data sumber OLAP.

Subtotal Dalam laporan dengan data sumber OLAP, Anda tidak dapat mengubah fungsi ringkasan untuk subtotal. Dengan tipe data sumber lainnya, Anda bisa mengubah fungsi total untuk subtotal dan memperlihatkan atau menyembunyikan subtotal untuk semua bidang baris dan kolom.

Untuk data sumber OLAP, Anda dapat menyertakan atau mengecualikan anggota tersembunyi saat menghitung subtotal dan total keseluruhan. Untuk tipe data sumber lainnya, Anda bisa menyertakan elemen tersembunyi dari bidang filter laporan di subtotal, namun elemen tersembunyi di bidang lain akan dikecualikan secara default.

Masalah analitik, OLAP, dan gudang data semakin menarik perhatian spesialis TI Rusia. Sampai saat ini, banyak materi bagus tentang topik ini, termasuk materi pengantar, telah diterbitkan di media komputer kita dan di Internet. Kami menyampaikan kepada Anda sebuah artikel di mana kami sengaja mencoba menjelaskan OLAP “sekilas”, menggunakan contoh spesifik. Praktek menunjukkan bahwa penjelasan seperti itu diperlukan untuk beberapa spesialis TI dan khususnya pengguna akhir.

Jadi, OLAP *1, pada perkiraan pertama, “sekilas”, dapat didefinisikan sebagai cara khusus untuk menganalisis data dan memperoleh laporan. Esensinya adalah menyediakan tabel multidimensi kepada pengguna yang secara otomatis merangkum data di berbagai bagian dan memungkinkan pengelolaan perhitungan dan formulir laporan secara interaktif. Artikel ini akan membahas tentang teknologi dan operasi dasar OLAP menggunakan contoh analisis faktur suatu perusahaan yang bergerak dalam perdagangan grosir produk makanan.

*1. OLAP - Pemrosesan Analitik On-Line, analisis data operasional.

Sistem OLAP dari kelas paling sederhana dan paling murah akan dianggap sebagai alat - klien OLAP *1. Misalnya, kami memilih produk paling sederhana di antara klien OLAP - “Contour Standard” dari Intersoft Lab. (Untuk kejelasan, nanti di artikel, istilah OLAP yang diterima secara umum akan dicetak tebal dan disertai padanannya dalam bahasa Inggris.)

*1. Rincian lebih lanjut tentang klasifikasi sistem OLAP dijelaskan dalam artikel “OLAP, buatan Rusia” di PC Week/RE, No. 3/2001.

Jadi, mari kita mulai dengan sistemnya. Pertama, Anda perlu menjelaskan sumber data - jalur ke tabel dan bidangnya. Ini adalah tugas pengguna yang mengetahui implementasi fisik database. Untuk pengguna akhir, ini menerjemahkan nama tabel dan bidangnya ke dalam istilah domain. Di belakang "sumber data" terdapat tabel lokal, tabel atau tampilan server SQL, atau prosedur tersimpan.

Kemungkinan besar, dalam database tertentu, invoice disimpan bukan dalam satu, tetapi dalam beberapa tabel. Selain itu, beberapa bidang atau catatan mungkin tidak digunakan untuk analisis. Oleh karena itu, Seleksi (kumpulan hasil atau kueri) kemudian dibuat, di mana hal-hal berikut dikonfigurasi: algoritme untuk menggabungkan tabel berdasarkan bidang kunci, kondisi pemfilteran, dan kumpulan bidang yang dikembalikan. Sebut saja pilihan kita “Faktur” dan tempatkan semua bidang sumber data “Faktur” ke dalamnya. Jadi, spesialis TI, dengan membuat lapisan semantik, menyembunyikan implementasi fisik database dari pengguna akhir.

Kemudian laporan OLAP dikonfigurasi. Hal ini dapat dilakukan oleh ahli materi pelajaran. Pertama, bidang sampel data datar dibagi menjadi dua kelompok - fakta (fakta atau ukuran) dan dimensi (dimensi). Fakta adalah angka, dan pengukuran adalah “bagian” di mana fakta akan diringkas. Dalam contoh kita, dimensinya adalah: "Wilayah", "Kota", "Pelanggan", "Produk", "Tanggal", dan akan ada satu fakta - bidang "Jumlah" pada faktur. Faktanya, Anda perlu memilih satu atau lebih algoritma agregasi. OLAP tidak hanya mampu menyimpulkan hasil, tetapi juga melakukan perhitungan yang lebih kompleks, hingga Analisis statistik. Memilih beberapa algoritme agregasi akan menghasilkan fakta virtual yang diperhitungkan. Dalam contoh, satu algoritma agregasi dipilih - “Jumlah”.

Properti khusus sistem OLAP adalah pembuatan pengukuran dan data untuk periode waktu yang lebih lama dari suatu tanggal dan penghitungan total otomatis untuk periode tersebut. Mari kita pilih periode “Tahun”, “Kuartal” dan “Bulan”, sedangkan data untuk setiap hari tidak akan ada dalam laporan, namun dimensi “Tahun”, “Kuartal” dan “Bulan” yang dihasilkan akan muncul. Beri nama laporan tersebut “Analisis Penjualan” dan simpan. Pekerjaan pembuatan antarmuka untuk aplikasi analitik telah selesai.

Sekarang, ketika menjalankan antarmuka ini setiap hari atau bulanan, pengguna akan melihat tabel dan grafik yang merangkum faktur berdasarkan item, pelanggan, dan periode.

Untuk membuat manipulasi data menjadi intuitif, alat manajemen tabel dinamis adalah elemen tabel itu sendiri - kolom dan barisnya. Pengguna dapat memindahkannya, menghapusnya, memfilternya, dan melakukan operasi OLAP lainnya. Dalam hal ini, tabel secara otomatis menghitung total antara dan akhir yang baru.


Misalnya, dengan menyeret kolom “Produk” (“operasi pemindahan”) ke posisi pertama, kita akan menerima laporan perbandingan - “Perbandingan volume penjualan produk untuk tahun tersebut.” Untuk menggabungkan data selama satu tahun, cukup seret kolom “Kuartal” dan “Bulan” ke bagian atas tabel - “area dimensi tidak aktif”. Dimensi “Kuartal” dan “Bulan” yang ditransfer ke area ini akan ditutup (operasi “dimensi dekat”), yaitu dikecualikan dari laporan; dalam hal ini, fakta-fakta dirangkum untuk tahun tersebut. Meskipun dimensinya tertutup, Anda dapat mengatur tahun, kuartal, dan bulan tertentu agar dimensi tersebut dapat memfilter data (operasi "filter").

Untuk lebih jelasnya, mari kita ubah jenis grafik yang menggambarkan tabel OLAP dan lokasinya di layar.

Menggali lebih dalam data (“operasi penelusuran”) memungkinkan kami memperoleh informasi lebih detail tentang penjualan produk yang kami minati. Dengan mengklik tanda “+” di sebelah produk “Kopi”, kita akan melihat volume penjualannya berdasarkan wilayah. Dengan memperluas wilayah “Ural”, kita akan mendapatkan volume penjualan berdasarkan kota-kota di wilayah Ural, dengan menggali data untuk “Ekaterinburg”, kita akan dapat melihat data pembeli grosir kota ini.

Anda juga dapat menggunakan dimensi terbuka untuk menyetel filter. Untuk membandingkan dinamika penjualan permen di Moskow dan Yekaterinburg, kami akan memasang filter pada dimensi “Produk” dan “Kota”.

Mari kita tutup pengukuran yang tidak perlu dan pilih jenis grafik “Garis”. Dengan menggunakan grafik yang dihasilkan, Anda dapat melacak dinamika penjualan, mengevaluasi fluktuasi musiman, dan hubungan antara penurunan dan peningkatan penjualan produk di berbagai kota.

Oleh karena itu, kami yakin bahwa teknologi OLAP memungkinkan pengguna membuat lusinan jenis laporan berbeda dari satu antarmuka, mengelola tabel OLAP dinamis menggunakan mouse. Tugas seorang programmer yang mengetahui alat tersebut bukanlah pengkodean rutin formulir pelaporan, tetapi menyiapkan klien OLAP untuk database. Pada saat yang sama, metode pengelolaan laporan bersifat intuitif bagi pengguna akhir.

Memang, OLAP merupakan kelanjutan dan pengembangan alami dari ide spreadsheet. Intinya, antarmuka visual OLAP juga berupa spreadsheet, namun dilengkapi dengan mesin kalkulasi yang kuat dan standar khusus untuk menyajikan dan mengelola data. Selain itu, beberapa klien OLAP diimplementasikan sebagai tambahan pada MS Excel. Oleh karena itu, jutaan pekerja kerah putih yang percaya diri dalam menggunakan spreadsheet dengan cepat menguasai alat OLAP. Bagi mereka, ini adalah “revolusi beludru” yang memberikan peluang baru, namun tidak melibatkan kebutuhan untuk belajar kembali.

Jika pembaca setelah membaca artikel ini tidak kehilangan minat pada OLAP, ia dapat merujuk pada materi yang disebutkan di awal. Koleksi materi tersebut diposting di sejumlah situs Internet, termasuk situs lab Intersoft - www.iso.ru. Dari situ Anda juga dapat mengunduh versi demo sistem “Contour Standard” dengan contoh yang dijelaskan dalam artikel.

Standar tabel pivot data asli disimpan di hard drive lokal. Dengan cara ini, Anda selalu dapat mengelola dan mengaturnya kembali, bahkan tanpa akses ke jaringan. Namun ini tidak berlaku untuk tabel ringkasan OLAP. Dalam tabel pivot OLAP, cache tidak pernah disimpan di hard drive lokal. Oleh karena itu, segera setelah terputus jaringan lokal tabel pivot Anda tidak akan berfungsi lagi. Anda tidak akan dapat memindahkan satu bidang pun di dalamnya.

Jika Anda masih perlu menganalisis data OLAP setelah offline, buat kubus data offline. Kubus data otonom adalah file terpisah, yang merupakan cache tabel pivot dan menyimpan data OLAP yang dilihat setelah memutuskan sambungan dari jaringan lokal. Data OLAP yang disalin ke tabel pivot dapat dicetak; ini dijelaskan secara rinci di situs web http://everest.ua.

Untuk membuat kubus data mandiri, pertama-tama buat tabel pivot OLAP. Tempatkan kursor di dalam tabel pivot dan klik tombol Alat OLAP pada tab kontekstual Alat, yang merupakan bagian dari grup tab kontekstual Alat PivotTable. Pilih perintah Offline OLAP (Gbr. 9.8).

Beras. 9.8. Buat kubus data offline

Kotak dialog Pengaturan Kubus Data OLAP Offline muncul di layar. Klik tombol Buat File Data Offline. Anda telah meluncurkan Wizard Buat File Kubus Data. Klik tombol Berikutnya untuk melanjutkan prosedur.

Pertama, Anda perlu menentukan dimensi dan level yang akan dimasukkan dalam kubus data. Di kotak dialog, Anda harus memilih data yang akan diimpor dari database OLAP. Idenya adalah untuk menentukan hanya dimensi yang diperlukan setelah komputer terputus dari jaringan lokal. Semakin banyak dimensi yang Anda tentukan, semakin besar kubus data otonomnya.

Klik tombol Berikutnya untuk berpindah ke kotak dialog wizard berikutnya. Ini memberi Anda kemampuan untuk menentukan anggota atau elemen data yang tidak akan disertakan dalam kubus. Secara khusus, Anda tidak memerlukan ukuran Jumlah Penjualan-Perpanjangan Internet, sehingga kotak centangnya akan dikosongkan dalam daftar. Kotak centang yang dikosongkan menunjukkan bahwa item yang ditentukan tidak akan diimpor dan menghabiskan ruang yang tidak diperlukan pada hard drive lokal Anda.

Pada langkah terakhir, tentukan lokasi dan nama kubus data. Dalam kasus kami, file kubus akan diberi nama MyOfflineCube.cub dan akan ditempatkan di folder Work.

File kubus data memiliki ekstensi .anak

Setelah beberapa waktu, Excel akan menyimpan kubus data offline di folder yang ditentukan. Untuk mengujinya, klik dua kali pada file tersebut, yang secara otomatis akan menghasilkan buku kerja Excel yang berisi tabel pivot yang terkait dengan kubus data yang dipilih. Setelah dibuat, Anda dapat mendistribusikan kubus data offline ke semua pengguna yang tertarik yang bekerja dalam mode LAN offline.

Setelah terhubung ke jaringan lokal, Anda dapat membuka file kubus data offline dan memperbaruinya serta tabel data yang sesuai. Prinsip utamanya menyatakan bahwa kubus data offline hanya digunakan untuk berfungsi ketika jaringan lokal terputus, tetapi perlu diperbarui setelah koneksi dipulihkan. Mencoba memperbarui kubus data offline setelah kegagalan koneksi akan mengakibatkan kegagalan.

Bekerja dengan file kubus offline

File kubus offline (.cub) menyimpan data dalam bentuk kubus OLAP (Online Analytical Processing). Data ini mungkin mewakili bagian dari database OLAP di server OLAP atau dapat dibuat secara independen dari database OLAP. Gunakan file kubus offline untuk terus bekerja dengan laporan PivotTable dan PivotChart saat server tidak tersedia atau saat Anda offline.

Catatan Keamanan: Berhati-hatilah saat menggunakan atau mendistribusikan file kubus offline yang berisi data sensitif atau pribadi. Disarankan agar Anda menyimpan data dalam buku kerja, bukan file kubus sehingga Anda bisa mengontrol akses ke data menggunakan manajemen hak. Untuk informasi selengkapnya, lihat Mengelola hak informasi di Office.

Saat bekerja dengan laporan PivotTable atau PivotChart yang didasarkan pada data sumber server OLAP, Anda bisa menggunakan Panduan Kubus Offline untuk menyalin data sumber ke file kubus offline terpisah di komputer Anda. Untuk membuat file offline ini, Anda harus memiliki penyedia data OLAP yang mendukung kemampuan ini, seperti MSOLAP dari Microsoft SQL Server Analysis Services yang terinstal di komputer.

Catatan: Pembuatan dan penggunaan file kubus offline dari Microsoft SQL Server Analysis Services tunduk pada persyaratan dan lisensi Instalasi Microsoft SQLServer. Tinjau informasi lisensi versi SQL Server yang berlaku.

Bekerja dengan Wizard Kubus Offline

Untuk membuat file kubus offline, Anda bisa memilih subset data dalam database OLAP menggunakan Offline Cube Wizard lalu menyimpan subset tersebut. Laporan tidak perlu menyertakan semua bidang yang disertakan dalam file, juga tidak perlu memilih salah satu bidang tersebut dan bidang data yang tersedia di database OLAP. Untuk meminimalkan file, Anda hanya dapat menyertakan data yang ingin ditampilkan dalam laporan. Anda dapat menghilangkan semua dimensi dan untuk sebagian besar jenis dimensi, Anda juga dapat mengecualikan informasi dan item tingkat rendah level tertinggi, yang tidak perlu ditampilkan. Untuk semua elemen yang Anda sertakan, bidang properti yang tersedia di database untuk elemen tersebut juga disimpan dalam file offline.

Mengambil data secara offline dan menghubungkannya kembali

Untuk melakukannya, pertama-tama Anda harus membuat laporan PivotTable atau PivotChart yang didasarkan pada database server, lalu membuat file kubus offline dari laporan tersebut. Anda kemudian dapat mengalihkan laporan antara database server dan file offline kapan saja. Misalnya saja jika Anda menggunakan laptop untuk perjalanan rumah dan video, lalu sambungkan kembali komputer Anda ke jaringan.

Di bawah ini adalah langkah-langkah dasar yang perlu Anda ikuti daya tahan baterai dengan data dan kemudian mentransfer data kembali ke Internet.

Buat atau buka laporan PivotTable atau PivotChart berdasarkan data OLAP yang ingin Anda akses secara offline.

Buat file kubus offline di komputer Anda. Dalam bab Membuat file kubus offline dari database server OLAP(lebih rendah di artikel ini).

Memutuskan sambungan dari jaringan dan bekerja dengan file kubus offline.

Hubungkan ke jaringan dan sambungkan kembali file kubus secara offline. Lihat bagiannya Menghubungkan kembali file kubus offline ke database server OLAP(di bawah artikel ini).

Perbarui file kubus offline dengan data baru dan buat ulang file kubus offline. Lihat bagiannya memperbarui dan membuat ulang file kubus offline(lebih rendah di artikel ini).

BLOG

Hanya postingan berkualitas

Apa itu Tabel Pivot Excel dan Kubus OLAP

Tonton video untuk artikelnya:

OLAP- ini bahasa Inggris. pemrosesan analitis online, teknologi analitis pemrosesan data waktu nyata. Secara sederhana– gudang dengan data multidimensi (Kubus), bahkan lebih sederhana – hanya database tempat Anda bisa mendapatkan data di Excel dan menganalisisnya menggunakan alat Excel – Tabel Pivot.

Tabel pivot- Ini antarmuka pengguna untuk menampilkan data multidimensi. Dengan kata lain, ini adalah jenis tabel khusus yang dapat digunakan untuk membuat hampir semua laporan.

Agar lebih jelas, mari kita bandingkan "Tabel Reguler" dengan "Tabel Pivot"

Tabel biasa:

Tabel ringkasan:

Perbedaan utama Tabel pivot– ini adalah kehadiran jendela “ Daftar Bidang Tabel Pivot", dari mana Anda dapat memilih bidang yang diperlukan dan mendapatkan tabel apa pun secara otomatis!

Cara Penggunaan

Membuka berkas Excel, yang terhubung ke kubus OLAP, misalnya “BIWEB”:

Sekarang, apa maksudnya dan bagaimana cara menggunakannya?

Seret dan lepas bidang yang wajib diisi untuk mendapatkan, misalnya, tabel seperti ini:

« Kelebihan» memungkinkan Anda menelusuri laporan. Dalam contoh ini, “Merek” dirinci menjadi “Nama Singkatan”, dan “Kuartal” menjadi “Bulan”, yaitu Jadi:

Fungsi analitik di Excel (fungsi kubus)

Microsoft terus menambahkan kemampuan baru ke Excel dalam hal analisis dan visualisasi data. Bekerja dengan informasi di Excel dapat direpresentasikan sebagai tiga lapisan yang relatif independen:

  • data sumber yang terorganisir “dengan benar”.
  • matematika (logika) pengolahan data
  • penyajian data

Beras. 1. Analisis data di excel: a) sumber data, b) ukuran c Poros Kekuatan, c) dasbor; Untuk memperbesar gambar, klik kanan padanya dan pilih Buka gambar di tab baru

Download catatan dalam format Word atau pdf, contoh dalam format Excel

Fungsi kubus dan tabel pivot

Cara paling sederhana dan sekaligus sangat ampuh untuk menyajikan data adalah tabel pivot. Mereka bisa dibangun dari data yang terdapat dalam: a) lembar kerja Excel, b) kubus OLAP, atau c) model data Power Pivot. Dalam dua kasus terakhir, selain tabel pivot, Anda bisa menggunakan fungsi analitik (fungsi kubus) untuk menghasilkan laporan pada lembar Excel. Tabel pivot lebih sederhana. Fungsi kubus lebih kompleks, namun memberikan lebih banyak fleksibilitas, terutama dalam desain laporan, itulah sebabnya fungsi ini banyak digunakan di dasbor.

Pembahasan berikut ini berlaku untuk rumus kubus dan tabel pivot berdasarkan model Power Pivot dan, dalam beberapa kasus, berdasarkan kubus OLAP.

Cara mudah untuk mendapatkan fungsi kubus

Ketika (jika) Anda mulai mempelajari kode VBA, Anda mengetahui bahwa cara termudah untuk memahami kode tersebut adalah dengan menggunakan perekaman makro. Kemudian kode dapat diedit, loop, pemeriksaan, dll. dapat ditambahkan. Demikian pula, cara termudah untuk mendapatkan sekumpulan fungsi kubus adalah dengan mengubah tabel pivot (Gbr. 2). Berdiri di sel mana pun dari tabel pivot, buka tab Analisis, klik tombolnya Fasilitas OLAP, dan tekan Konversikan ke rumus.

Beras. 2. Ubah PivotTable menjadi Kumpulan Fungsi Kubus

Angka-angka tersebut akan disimpan, dan ini bukan berupa nilai, melainkan rumus yang mengekstrak data dari model data Power Pivot (Gambar 3). Anda dapat memformat tabel yang dihasilkan. Termasuk, Anda dapat menghapus dan menyisipkan baris dan kolom di dalam tabel. Irisannya tetap ada dan memengaruhi data dalam tabel. Ketika data sumber diperbarui, angka-angka dalam tabel juga akan diperbarui.

Beras. 3. Tabel berdasarkan rumus kubus

CUBEVALUE() fungsi

Ini mungkin fungsi utama kubus. Dia setara dengan daerah tersebut Nilai-nilai tabel pivot. CUBEVALUE mengambil data dari model kubus atau Power Pivot dan menampilkannya di luar PivotTable. Ini berarti Anda tidak dibatasi oleh PivotTable dan bisa membuat laporan dengan kemungkinan yang tak terhitung jumlahnya.

Menulis rumus dari awal

Anda tidak perlu mengonversi PivotTable yang telah dibuat sebelumnya. Anda dapat menulis rumus kubus apa pun dari awal. Misalnya, rumus berikut dimasukkan ke dalam sel C10 (Gbr. 4):

Beras. 4. Fungsi CUBEVALUE() di sel C10 mengembalikan penjualan sepeda sepanjang tahun, seperti pada tabel pivot

Sedikit trik. Untuk memudahkan membaca rumus kubus, sebaiknya hanya satu argumen yang ditempatkan pada setiap baris. Anda dapat memperkecil jendela Excel. Untuk melakukan ini, klik ikon tersebut Buka jendela, terletak di sudut kanan atas layar. Dan kemudian sesuaikan ukuran jendela secara horizontal. Opsi alternatif– memaksa teks rumus untuk ditransfer garis baru. Untuk melakukan ini, di bilah rumus, letakkan kursor di tempat Anda ingin melakukan transfer dan tekan Alt+Enter.

Beras. 5. Minimalkan jendela

Sintaks fungsi CUBEVALUE()

Bantuan Excel sepenuhnya akurat dan sama sekali tidak berguna untuk pemula:

CUBEVALUE(koneksi; [elemen_ekspresi1]; [elemen_ekspresi2]; ...)

Koneksi– argumen yang diperlukan; string teks yang mewakili nama koneksi ke kubus.

Elemen_ekspresi– argumen opsional; string teks yang mewakili MDX yang mengembalikan elemen atau tupel dalam kubus. Selain itu, "elemen_ekspresi" dapat berupa himpunan yang ditentukan menggunakan fungsi CUBESET. Gunakan "elemen_ekspresi" sebagai irisan untuk menentukan bagian kubus yang ingin Anda kembalikan nilai agregatnya. Jika tidak ada ukuran yang ditentukan dalam ekspresi_elemen, ukuran default untuk kubus tersebut akan digunakan.

Sebelum kita menjelaskan sintaks fungsi CUBEVALUE, beberapa kata tentang kubus, model data, dan misterinya. iring-iringan mobil.

Beberapa informasi tentang kubus OLAP dan model data Power Pivot

Kubus data OLAP ( HAI N aku di dalam A bersifat analitis P pengolahan - analisis data operasional) dikembangkan khusus untuk pemrosesan analitis dan ekstraksi cepat diantaranya data. Bayangkan sebuah ruang tiga dimensi di mana periode waktu, kota, dan barang diplot sepanjang sumbunya (Gbr. 5a). Node-node dari grid koordinat tersebut berisi nilai-nilai berbagai ukuran: volume penjualan, keuntungan, biaya, jumlah unit yang terjual, dll. Sekarang bayangkan ada lusinan, atau bahkan ratusan dimensi... dan ada juga a banyak tindakan. Ini akan menjadi kubus OLAP multidimensi. Membuat, mengonfigurasi, dan memelihara kubus OLAP adalah tugas spesialis TI.

Beras. 5a. Kubus OLAP 3D

Rumus analitik Excel (rumus kubus) mengekstrak nama sumbu (misalnya, Waktu), nama unsur pada sumbu tersebut (Agustus, September), nilai besaran pada perpotongan koordinat. Struktur inilah yang memungkinkan tabel pivot berbasis kubus dan rumus kubus menjadi begitu fleksibel dan beradaptasi dengan kebutuhan pengguna. Tabel pivot berdasarkan lembar Excel tidak menggunakan ukuran, sehingga tidak fleksibel untuk tujuan analisis data.

Power Pivot adalah fitur Microsoft yang relatif baru. Ini adalah lingkungan Excel bawaan dan sebagian independen dengan antarmuka yang familier. Power Pivot jauh lebih kuat dibandingkan tabel pivot standar. Pada saat yang sama, mengembangkan kubus di Power Pivot relatif sederhana, dan yang terpenting, tidak memerlukan partisipasi spesialis TI. Microsoft menerapkan slogannya: “Analisis bisnis untuk massa!” Meskipun model Power Pivot tidak 100% kubus, model tersebut juga dapat dianggap kubus (untuk informasi selengkapnya, lihat kursus pengantar Mark Moore, Power Pivot, dan kursus Rob Colley yang lebih panjang, Rumus DAX untuk Power Pivot).

Komponen utama kubus adalah dimensi, hierarki, tingkatan, elemen (atau anggota; dalam bahasa Inggris anggota) dan ukuran (measures). Pengukuran - ciri-ciri utama data yang dianalisis. Misalnya kategori produk, periode waktu, geografi penjualan. Dimensi adalah sesuatu yang dapat kita tempatkan pada salah satu sumbu tabel pivot. Setiap dimensi, selain nilai unik, menyertakan elemen yang melakukan agregasi semua elemen dimensi ini.

Pengukuran didasarkan pada hirarki. Misalnya, kategori produk dapat dibagi menjadi subkategori, lalu menjadi model, dan terakhir menjadi nama produk (Gbr. 5b). Hierarki memungkinkan Anda membuat data ringkasan dan menganalisisnya di berbagai tingkat struktur. Dalam contoh kita, hierarki Kategori termasuk 4 Tingkat.

Elemen(anggota individu) hadir di semua tingkatan. Misalnya pada level Kategori ada empat elemen: Aksesoris, Sepeda, Pakaian, Komponen. Level lain memiliki elemennya sendiri.

Pengukuran– ini adalah nilai yang dihitung, misalnya volume penjualan. Besaran dalam kubus disimpan dalam dimensinya sendiri yang disebut (lihat Gambar 9 di bawah). Tindakan tidak memiliki hierarki. Setiap ukuran menghitung dan menyimpan nilai untuk semua dimensi dan semua elemen, dan diiris tergantung pada elemen dimensi mana yang kita tempatkan pada sumbu. Mereka juga mengatakan koordinat apa yang akan kita atur, atau konteks filter apa yang akan kita atur. Misalnya, pada Gambar. 5a, di setiap kubus kecil ukuran yang sama dihitung - Untung. Dan nilai yang dikembalikan oleh ukuran tersebut bergantung pada koordinatnya. Di sebelah kanan, Gambar 5a menunjukkan bahwa Keuntungan (dalam tiga koordinat) di Moskow pada bulan Oktober dari apel = 63.000 rubel. Suatu ukuran juga dapat diartikan sebagai salah satu dimensi. Misalnya, pada Gambar. 5a bukannya sumbu Barang-barang, letakkan sumbunya Pengukuran dengan elemen Volume penjualan, Laba, Unit terjual. Maka setiap sel akan memiliki nilai tertentu, misalnya Moskow, September, volume penjualan.

iring-iringan– beberapa elemen dengan dimensi berbeda yang menentukan koordinat sepanjang sumbu kubus tempat kita menghitung ukurannya. Misalnya, pada Gambar. 5a iring-iringan= Moskow, Oktober, apel. Tupel yang juga dapat diterima adalah Perm, apel. Satu lagi – apel, Agustus. Dimensi yang tidak termasuk dalam tupel ada di dalamnya secara implisit, dan diwakili oleh anggota default. Jadi, sel ruang multidimensi selalu ditentukan oleh himpunan koordinat lengkap, meskipun beberapa di antaranya dihilangkan dari tupel. Anda tidak dapat memasukkan dua elemen dengan dimensi yang sama dalam sebuah tupel, sintaksisnya tidak mengizinkannya. Misalnya, iring-iringan Moskow dan Perm yang tidak valid, apel. Untuk mengimplementasikan ekspresi multidimensi seperti itu, Anda memerlukan satu set dua tupel: Moskow dan apel + Perm dan apel.

Kumpulan elemen– beberapa elemen satu dimensi. Misalnya apel dan pir. Seperangkat tupel– beberapa tupel, yang masing-masing terdiri dari dimensi yang sama dan urutan yang sama. Misalnya, satu set dua tupel: Moskow, apel dan Perm, pisang.

Pelengkapan otomatis untuk membantu

Mari kembali ke sintaks fungsi CUBEVALUE. Mari gunakan pelengkapan otomatis. Mulai memasukkan rumus ke dalam sel:

Excel akan menawarkan semua koneksi yang tersedia di buku kerja Excel:

Beras. 6. Koneksi ke model data Power Pivot selalu disebut ThisWorkbookDataModel

Beras. 7. Koneksi ke kubus

Mari lanjutkan memasukkan rumusnya (dalam kasus kita untuk model data):

Pelengkapan otomatis akan menyarankan semua tabel dan ukuran yang tersedia dalam model data:

Beras. 8. Elemen tingkat pertama yang tersedia - nama tabel dan serangkaian ukuran (disorot)

Pilih ikon Pengukuran. Sampaikan poin berikut:

CUBEVALUE("ThisWorkbookDataModel " ; " .

Pelengkapan otomatis akan menyarankan semua tindakan yang tersedia:

Beras. 9. Elemen tingkat kedua yang tersedia dalam kumpulan ukuran

Pilih ukuran. Tambahkan tanda kutip, tanda kurung tutup, dan tekan Enter.

CUBEVALUE("ThisWorkbookDataModel " ; " . " )

Beras. 10. Rumus NILAI KUBUS dalam sel Excel

Demikian pula, Anda dapat menambahkan argumen ketiga ke rumus:

VBA di Excel Objek Excel.PivotTable dan bekerja dengan tabel pivot dan kubus OLAP di Excel

10.8 Bekerja dengan tabel pivot (objek PivotTable)

Objek Excel.PivotTable, kerja program dengan tabel pivot dan kubus OLAP di Excel menggunakan VBA, objek PivotCache, membuat tata letak tabel pivot

Selama beroperasinya sebagian besar perusahaan, apa yang disebut data mentah tentang aktivitas terakumulasi. Misalnya, untuk perusahaan perdagangan, data penjualan barang dapat diakumulasikan - untuk setiap pembelian secara terpisah, untuk perusahaan komunikasi seluler- memuat statistik stasiun pangkalan dan seterusnya. Seringkali, manajemen perusahaan memerlukan informasi analitis yang dihasilkan berdasarkan informasi mentah - misalnya, untuk menghitung kontribusi setiap jenis produk terhadap pendapatan perusahaan atau kualitas layanan di bidang a stasiun yang diberikan. Sangat sulit untuk mengekstrak informasi tersebut dari informasi mentah: Anda perlu menjalankan kueri SQL yang sangat kompleks yang membutuhkan waktu lama untuk dieksekusi dan sering kali mengganggu pekerjaan saat ini. Oleh karena itu, data mentah kini semakin dikonsolidasikan terlebih dahulu ke dalam gudang data arsip - Gudang Data, dan kemudian ke dalam kubus OLAP, yang sangat nyaman untuk analisis interaktif. Cara termudah untuk membayangkan kubus OLAP adalah sebagai tabel multidimensi, yang mana, alih-alih dua dimensi standar (kolom dan baris, seperti pada tabel biasa), terdapat banyak dimensi. Istilah "penampang" biasanya digunakan untuk menggambarkan pengukuran dalam sebuah kubus. Misalnya, departemen pemasaran mungkin memerlukan informasi berdasarkan waktu, berdasarkan wilayah, berdasarkan jenis produk, berdasarkan saluran penjualan, dll. Dengan menggunakan kubus (berbeda dengan kueri SQL standar), sangat mudah untuk mendapatkan jawaban atas pertanyaan seperti “berapa banyak produk jenis ini yang terjual pada kuartal keempat tahun lalu di wilayah Barat Laut melalui distributor regional.

Tentu saja, kubus seperti itu tidak dapat dibuat dalam database konvensional. Bekerja dengan kubus OLAP memerlukan keahlian khusus produk perangkat lunak. SQL Server dilengkapi dengan database OLAP dari Microsoft yang disebut Analysis Services. Ada solusi OLAP dari Oracle, IBM, Sybase, dll.

Untuk bekerja dengan kubus seperti itu, Excel memiliki klien bawaan. Dalam bahasa Rusia disebut Tabel pivot(pada layar grafis itu tersedia melalui menu Data -> Tabel pivot), dan dalam bahasa Inggris - Tabel pivot. Oleh karena itu, objek yang diwakili klien ini disebut PivotTable. Perlu dicatat bahwa ini dapat bekerja tidak hanya dengan kubus OLAP, tetapi juga dengan data biasa dalam tabel atau database Excel, tetapi banyak kemampuan yang hilang.

PivotTable dan PivotTable adalah produk perangkat lunak dari Panorama Software yang diakuisisi oleh Microsoft dan diintegrasikan ke dalam Excel. Oleh karena itu, bekerja dengan objek PivotTable agak berbeda dengan bekerja dengan objek Excel lainnya. Mencari tahu apa yang perlu dilakukan seringkali sulit. Oleh karena itu, disarankan untuk secara aktif menggunakan perekam makro untuk menerima petunjuk. Pada saat yang sama, ketika bekerja dengan tabel pivot, pengguna sering kali harus melakukan operasi berulang yang sama, sehingga otomatisasi diperlukan dalam banyak situasi.

Seperti apa bekerja dengan tabel pivot secara terprogram?

Hal pertama yang perlu kita lakukan adalah membuat objek PivotCache yang akan mewakili sekumpulan record yang diambil dari sumber OLAP. Secara kasar, objek PivotCache ini dapat dibandingkan dengan QueryTable. Anda hanya bisa menggunakan satu objek PivotCache per objek PivotTable. Objek PivotCache dibuat menggunakan metode Add() dari koleksi PivotCaches:

Redupkan PC1 Sebagai PivotCache

Setel PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches adalah kumpulan standar, dan di antara metode yang patut dipertimbangkan secara mendetail, hanya metode Add() yang dapat disebutkan di dalamnya. Metode ini mengambil dua parameter:

  • Tipe Sumber- diperlukan, menentukan tipe sumber data untuk tabel pivot. Anda bisa menentukan pembuatan PivotTable berdasarkan rentang di Excel, data dari database, sumber data eksternal, PivotTable lain, dll. Dalam praktiknya, biasanya masuk akal untuk menggunakan OLAP hanya ketika ada banyak data - oleh karena itu, Anda memerlukan penyimpanan eksternal khusus (misalnya, Layanan Analisis Microsoft). Dalam situasi ini, nilai xlExternal dipilih.
  • Sumber data- diperlukan dalam semua kasus, kecuali jika nilai parameter pertama adalah xlExternal. Faktanya, ini mendefinisikan rentang data yang menjadi dasar pembuatan PivotTable. Biasanya mengambil objek Range.

Tugas selanjutnya adalah mengonfigurasi pengaturan objek PivotCache. Seperti telah disebutkan, objek ini sangat mirip dengan QueryTable, dan kumpulan properti serta metodenya sangat mirip. Beberapa properti dan metode terpenting:

  • Koneksi ADO- kemampuan untuk mengembalikan objek Koneksi ADO yang secara otomatis dibuat untuk terhubung ke sumber data eksternal. Digunakan untuk pengaturan tambahan properti koneksi.
  • Koneksi- bekerja persis sama dengan properti objek QueryTable dengan nama yang sama. Dapat menerima string koneksi, objek Recordset yang sudah jadi, berkas teks, Kueri web. berkas Microsoft Pertanyaan. Paling sering, ketika bekerja dengan OLAP, string koneksi ditulis secara langsung (karena mendapatkan objek Recordset, misalnya, untuk mengubah data, tidak masuk akal - sumber data OLAP hampir selalu hanya-baca). Misalnya, menyetel properti ini untuk menyambung ke database Foodmart (database sampel Layanan Analisis) di server LONDON mungkin terlihat seperti ini:

PC1.Connection = "OLEDB;Penyedia=MSOLAP.2;Sumber Data=LONDON1;Katalog Awal = FoodMart 2000"

  • properti Tipe Perintah Dan Teks Perintah mereka juga menjelaskan jenis perintah yang dikirim ke server database dan teks perintah itu sendiri. Misalnya, untuk mengakses kubus Penjualan dan memasukkannya seluruhnya ke dalam cache di klien, Anda dapat menggunakan kode seperti
  • Properti Koneksi Lokal memungkinkan Anda terhubung ke kubus lokal (file *.cub) yang dibuat menggunakan Excel. Tentu saja, tidak disarankan menggunakan file seperti itu untuk bekerja dengan volume data "produksi" - hanya untuk tujuan membuat tata letak, dll.
  • Properti Memori yang Digunakan mengembalikan kuantitas memori akses acak, digunakan oleh PivotCache. Jika PivotTable berdasarkan PivotCache ini belum dibuat dan dibuka, ia akan mengembalikan 0. Dapat digunakan untuk memeriksa apakah aplikasi Anda akan berjalan pada klien yang lemah.
  • Properti OLAP mengembalikan True jika PivotCache tersambung ke server OLAP.
  • OptimalkanCache- kemampuan untuk mengoptimalkan struktur cache. Pengunduhan data awal akan memakan waktu lebih lama, namun kecepatannya mungkin meningkat. Tidak berfungsi untuk sumber OLE DB.

Properti objek PivotCache lainnya sama dengan properti objek QueryTable, dan oleh karena itu tidak akan dibahas di sini.

Metode utama objek PivotCache adalah metode CreatePivotTable(). Dengan menggunakan metode ini, tahap selanjutnya dilakukan - membuat tabel pivot (objek PivotTable). Metode ini membutuhkan empat parameter:

  • TabelTujuan- satu-satunya parameter yang diperlukan. Menerima objek Range di sudut kiri atas tempat tabel pivot akan ditempatkan.
  • Nama Tabel- nama tabel pivot. Jika tidak ditentukan, nama tampilan “PivotTable1” akan dibuat secara otomatis.
  • Data Baca- jika disetel ke True, maka seluruh isi kubus akan otomatis ditempatkan di cache. Anda harus sangat berhati-hati dengan parameter ini, karena penggunaannya yang salah dapat meningkatkan beban klien secara drastis.
  • Versi Default- properti ini biasanya tidak ditentukan. Memungkinkan Anda menentukan versi tabel pivot yang sedang dibuat. Secara default, versi terbaru yang digunakan.

Membuat tabel pivot di sel pertama lembar kerja pertama mungkin terlihat seperti ini:

PC1.Buat Rentang Tabel Pivot("A1")

Kami telah membuat tabel pivot, tetapi segera setelah pembuatannya kosong. Ini menyediakan empat area di mana Anda dapat menempatkan bidang dari sumbernya (pada layar grafis, semua ini dapat dikonfigurasi menggunakan jendela Daftar Bidang Tabel Pivot- Terbuka secara otomatis atau menggunakan tombol Tata Letak di layar terakhir PivotTable Wizard):

  • luas kolom- berisi dimensi-dimensi (“bagian” di mana data akan dianalisis) yang anggotanya lebih kecil;
  • daerah garis- dimensi yang mempunyai anggota lebih banyak;
  • wilayah halaman- pengukuran yang hanya perlu Anda filter (misalnya, menampilkan data hanya untuk wilayah ini dan itu atau hanya untuk tahun ini dan itu);
  • wilayah data- sebenarnya, bagian tengah meja. Data numerik tersebut (misalnya, jumlah penjualan) yang kami analisis.

Mengandalkan pengguna untuk menempatkan elemen dengan benar di keempat area itu sulit. Selain itu, mungkin diperlukan waktu tertentu. Oleh karena itu, sering kali diperlukan penyusunan data dalam tabel pivot secara terprogram. Operasi ini dilakukan dengan menggunakan objek CubeField. Properti utama dari objek ini adalah Orientasi, yang menentukan di mana bidang ini atau itu akan ditempatkan. Misalnya, mari kita tempatkan dimensi Pelanggan di area kolom:

PT1.CubeFields("").Orientasi = xlColumnField

Kemudian - pengukuran Waktu ke dalam area garis:

PT1.CubeFields("").Orientasi = xlRowField

Kemudian - dimensi Produk ke area halaman:

PT1.CubeFields("").Orientasi = xlPageField

Dan terakhir, indikator (data numerik untuk analisis) Penjualan Unit:

PT1.CubeFields(“.”).Orientasi = xlDataField

Pilih dokumen dari arsip untuk dilihat:

18,5 KB mobil.xls

14KB negara.xls

Excel pr.r. 1.docx

Perpustakaan
bahan

Kerja Praktek 1

"Tujuan dan antarmuka MS Excel"

Setelah menyelesaikan tugas pada topik ini, Anda:

1. Belajar menjalankan spreadsheet;

2. Memperkuat konsep dasar: sel, baris, kolom, alamat sel;

3. Pelajari cara memasukkan data ke dalam sel dan mengedit bilah rumus;

5. Cara memilih seluruh baris, satu kolom, beberapa sel yang terletak bersebelahan dan seluruh tabel.

Latihan: Kenali elemen dasar jendela MS Excel.

    Jalankan programnya Microsoft Excel. Perhatikan baik-baik jendela program.

Dokumen yang dibuat menggunakanUNGGUL , disebutbuku kerja dan memiliki ekstensi. XLS. Buku kerja baru memiliki tiga lembar kerja yang disebut SHEET1, SHEET2 dan SHEET3. Nama-nama ini terdapat pada label lembar di bagian bawah layar. Untuk berpindah ke sheet lain, klik nama sheet tersebut.

Tindakan dengan lembar kerja:

    Ganti nama lembar kerja. Tempatkan penunjuk tetikus di bagian belakang lembar kerja dan klik dua kali tombol kiri atau panggil menu konteks dan pilih perintah Ganti Nama.Tetapkan nama lembar menjadi "PELATIHAN"

    Memasukkan Lembar Kerja . Pilih tab lembar "Lembar 2" yang sebelumnya ingin Anda sisipkan lembar baru, dan gunakan menu konteksmasukkan sheet baru dan beri nama "Probe" .

    Menghapus lembar kerja. Pilih pintasan lembar "Lembar 2", dan gunakan menu konteksmenghapus .

Sel dan rentang sel.

Bidang kerja terdiri dari baris dan kolom. Baris diberi nomor 1 sampai 65536. Kolom ditandai dengan huruf latin: A, B, C, ..., AA, AB, ..., IV, jumlah - 256. Pada perpotongan baris dan kolom terdapat adalah sel. Setiap sel memiliki alamatnya sendiri: nama kolom dan nomor baris di persimpangan lokasinya. Misalnya A1, SV234, P55.

Untuk bekerja dengan beberapa sel, akan lebih mudah untuk menggabungkannya ke dalam "rentang".

Rentang adalah sel yang disusun dalam persegi panjang. Misalnya A3, A4, A5, B3, B4, B5. Untuk menulis rentang, gunakan ": ": A3:B5

8:20 – semua sel di baris 8 hingga 20.

A:A – semua sel di kolom A.

H:P – semua sel di kolom H hingga R.

Anda dapat memasukkan nama lembar kerja di alamat sel: Sheet8!A3:B6.

2. Memilih sel di Excel

Apa yang kami soroti?

Tindakan

Satu sel

Klik di atasnya atau pindahkan pilihan dengan tombol panah.

Rangkaian

Klik pada nomor baris.

Kolom

Klik pada nama kolom.

Rentang sel

Seret penunjuk tetikus dari sudut kiri atas rentang ke kanan bawah.

Beberapa rentang

Pilih yang pertama, tekan SCHIFT + F 8, pilih yang berikutnya.

Seluruh meja

Klik tombol Pilih Semua (tombol kosong di sebelah kiri nama kolom)

Anda dapat mengubah lebar kolom dan tinggi baris dengan menyeret batas di antara keduanya.

Gunakan bilah gulir untuk menentukan berapa banyak baris yang dimiliki tabel dan nama kolom terakhirnya.
Perhatian!!! Untuk mencapai akhir tabel dengan cepat secara horizontal atau vertikal, Anda perlu menekan kombinasi tombol: Ctrl+→ - akhir kolom atau Ctrl+↓ - akhir baris. Kembali cepat ke awal tabel - Ctrl+Home.

Di sel A3, masukkan alamat kolom terakhir tabel.

Berapa banyak baris yang ada di tabel? Masukkan alamat baris terakhir di sel B3.

3. Jenis data berikut dapat dimasukkan ke dalam EXCEL:

    Angka.

    Teks (misalnya judul dan materi penjelasan).

    Fungsi (misalnya jumlah, sinus, akar).

    Rumus.

Data dimasukkan ke dalam sel. Untuk memasukkan data, sel yang diperlukan harus disorot. Ada dua cara untuk memasukkan data:

    Cukup klik di sel dan ketik data yang diperlukan.

    Klik di sel dan di bilah rumus dan masukkan data di bilah rumus.

Tekan enter.

Masukkan nama Anda di sel N35, letakkan di tengah sel, dan tebalkan.
Masukkan tahun berjalan di sel C5 menggunakan bilah rumus.

4. Perubahan data.

    Pilih sel dan tekan F 2 dan ubah datanya.

    Pilih sel dan klik di bilah rumus dan ubah data di sana.

Untuk mengubah rumusnya, Anda hanya bisa menggunakan cara kedua saja.

Ubah data dalam sel N35, tambahkan nama belakang Anda. menggunakan salah satu metode.

5. Memasukkan formula.

Rumus adalah ekspresi aritmatika atau logika yang digunakan untuk melakukan penghitungan dalam tabel. Rumus terdiri dari referensi sel, simbol operasi, dan fungsi. Ms EXCEL memiliki serangkaian fungsi bawaan yang sangat besar. Dengan bantuan mereka, Anda dapat menghitung jumlah atau nilai rata-rata aritmatika dari rentang sel tertentu, menghitung bunga deposito, dll.

Memasukkan rumus selalu diawali dengan tanda sama dengan. Setelah memasukkan rumus, hasil perhitungan akan muncul di sel yang sesuai, dan rumus itu sendiri dapat dilihat di bilah rumus.

Tindakan

Contoh

+

Tambahan

A1+B1

-

Pengurangan

A1 - B2

*

Perkalian

B3*C12

/

Divisi

A1/B5

Eksponensial

A4^3

=, <,>,<=,>=,<>

Tanda-tanda hubungan

A2

Anda dapat menggunakan tanda kurung dalam rumus untuk mengubah urutan operasi.

    Pelengkapan otomatis.

Alat yang sangat berguna yang hanya digunakan di MS EXCEL adalah pengisian otomatis sel yang berdekatan. Misalnya, Anda perlu memasukkan nama bulan dalam setahun di kolom atau baris. Hal ini dapat dilakukan secara manual. Namun ada cara yang jauh lebih nyaman:

    Masukkan bulan yang diinginkan pada sel pertama, misalnya Januari.

    Pilih sel ini. Di sudut kanan bawah bingkai pilihan ada kotak kecil - penanda isian.

    Gerakkan penunjuk mouse Anda ke atas penanda isian (akan berubah menjadi tanda silang) sambil menahan tombol kiri mouse, seret penanda ke arah yang diinginkan. Dalam hal ini, nilai sel saat ini akan terlihat di sebelah bingkai.

Jika Anda perlu mengisi beberapa rangkaian angka, maka Anda perlu memasukkan dua angka pertama ke dalam dua sel yang berdekatan (misalnya, masukkan 1 di A4, dan 2 di B4), pilih dua sel ini dan seret area pilihan menggunakan penanda sesuai ukuran yang diinginkan.

Dokumen dipilih untuk dilihat Excel pr.r. 2.docx

Perpustakaan
bahan

Kerja Praktek 2

“Memasukkan data dan rumus ke dalam sel spreadsheet MS Excel”

· Masukkan data ke dalam sel jenis yang berbeda: teks, numerik, rumus.

Latihan: Masukkan data yang diperlukan dan perhitungan sederhana ke dalam tabel.

Teknologi pelaksanaan tugas:

1. Jalankan programnyaMicrosoft Excel.

2. Ke selA1 Lembar 2 masukkan teks: "Tahun berdirinya sekolah". Rekam data dalam sel menggunakan metode apa pun yang Anda ketahui.

3. Ke selDALAM 1 masukkan nomor – tahun berdirinya sekolah (1971).

4. Ke selC1 masukkan nomor – tahun berjalan (2016).

Perhatian! Harap dicatat bahwa di MS teks excel data disejajarkan ke kiri, dan angka serta tanggal disejajarkan ke kanan.

5. Pilih selD1 , masukkan rumus dari keyboard untuk menghitung usia sekolah:= C1- B1

Perhatian! Memasukkan rumus selalu diawali dengan tanda sama dengan«=». Alamat sel harus diisi dengan huruf latin tanpa spasi. Alamat sel dapat dimasukkan ke dalam rumus tanpa menggunakan keyboard, tetapi cukup dengan mengklik sel yang sesuai.

6. Hapus isi selD1 dan ulangi memasukkan rumus menggunakan mouse. Di dalam selD1 menetapkan tanda«=» , lalu klik pada selC1, Harap perhatikan alamat sel ini munculD1, memasang tanda«–» dan klik pada selB1 , tekan(Memasuki).

7. Ke selA2 Masukkan teks"Usia saya".

8. Ke selB2 masukkan tahun lahir Anda.

9. Ke selC2 masukkan tahun berjalan.

10. Ketik selD2 rumus untuk menghitung usia Anda pada tahun berjalan(= C2-B2).

11. Pilih selC2. Masukkan nomor tahun depan. Harap dicatat, perhitungan ulang di dalam selD2 terjadi secara otomatis.

12. Tentukan usia Anda pada tahun 2025. Untuk melakukan ini, ganti tahun di dalam selC2 pada2025.

Pekerjaan mandiri

Latihan: Hitung, dengan menggunakan ET, apakah 130 rubel cukup bagimu untuk membeli semua produk yang dipesan ibumu untukmu, dan apakah cukup untuk membeli chip seharga 25 rubel?

Teknologi latihan:
o Di sel A1 masukkan “Tidak.”
o Di sel A2, A3 masukkan “1”, “2”, pilih sel A2, A3, arahkan ke pojok kanan bawah (akan muncul tanda silang hitam), regangkan ke sel A6
o Di sel B1 masukkan “Nama”
o Di sel C1 masukkan “Harga dalam rubel”
o Di sel D1 masukkan “Kuantitas”
o Di sel E1 masukkan “Biaya”, dll.
o Pada kolom “Biaya”, semua rumus ditulis bahasa Inggris!
o Dalam rumus, nama sel ditulis sebagai pengganti variabel.
o Setelah menekan Enter, alih-alih rumus, langsung muncul angka - hasil perhitungan

o Hitung sendiri totalnya.

Tunjukkan hasilnya kepada gurumu!!!

Dokumen dipilih untuk dilihat Excel pr.r. 3.docx

Perpustakaan
bahan

Kerja Praktek 3

"MS.Excel. Penciptaan dan pengeditan dokumen spreadsheet»

Dengan menyelesaikan tugas dalam topik ini, Anda akan belajar:

Membuat dan mengisi tabel dengan data;

Memformat dan mengedit data dalam sel;

Gunakan rumus sederhana dalam tabel;

Salin rumus.

Latihan:

1. Buat tabel yang berisi jadwal kereta api dari stasiun Saratov ke stasiun Samara. Bentuk umum tabel "Jadwal" ditunjukkan pada gambar.

2. Pilih selA3 , ganti kata "Emas" dengan "Hebat" dan tekan tombolMemasuki .

3. Pilih selA6 , klik kiri dua kali dan ganti “Ugryumovo” dengan “Veselkovo”

4. Pilih selA5 buka bilah rumus dan ganti "Sennaya" dengan "Sennaya 1".

5. Lengkapi tabel “Jadwal” dengan perhitungan waktu pemberhentian kereta api di setiap lokasi. (masukkan kolom) Hitung total waktu berhenti, total waktu tempuh, waktu yang dihabiskan kereta api untuk bergerak dari satu tempat hunian ke yang lain.

Teknologi pelaksanaan tugas:

1. Pindahkan kolom Waktu Keberangkatan dari Kolom C ke Kolom D. Untuk melakukannya, ikuti langkah-langkah berikut:

Pilih blok C1:C7; pilih timMemotong .
Tempatkan kursor di sel D1;
Jalankan perintahMenyisipkan ;
Sejajarkan lebar kolom agar sesuai dengan ukuran header.;

2. Masukkan teks "Parkir" di sel C1. Sejajarkan lebar kolom agar sesuai dengan ukuran header.

3. Buatlah rumus yang menghitung waktu parkir di suatu daerah berpenduduk.

4. Anda perlu menyalin rumus ke blok C4:C7 menggunakan gagang isian. Untuk melakukannya, ikuti langkah-langkah berikut:
Ada bingkai di sekitar sel aktif, di sudutnya ada persegi panjang kecil, ambil dan perluas rumusnya hingga ke sel C7.

5. Masukkan teks “Waktu Perjalanan” di sel E1. Sejajarkan lebar kolom agar sesuai dengan ukuran header.

6. Buatlah rumus yang menghitung waktu yang dibutuhkan kereta api untuk melakukan perjalanan dari satu kota ke kota lain.

7. Ubah format angka untuk blok C2:C9 dan E2:E9. Untuk melakukannya, ikuti langkah-langkah berikut:

Pilih blok sel C2:C9;
Beranda – Format – Format angka lainnya - Waktu dan parameter yang ditetapkan (jam:menit) .

Tekan tombolnyaOKE .

8. Hitung total waktu parkir.
Pilih sel C9;
Klik tombolnyaJumlah otomatis di bilah alat;
Konfirmasikan pemilihan blok sel C3:C8 dan tekan tombolMemasuki .

9. Masukkan teks di sel B9. Untuk melakukannya, ikuti langkah-langkah berikut:

Pilih sel B9;
Masukkan teks “Total waktu parkir”. Sejajarkan lebar kolom agar sesuai dengan ukuran header.

10. Hapus isi sel C3.

Pilih sel C3;
Jalankan perintah menu utama Sunting - Hapus atau klikMenghapus di papan ketik;
Perhatian! Komputer secara otomatis menghitung ulang jumlah di sel C9!!!

Jalankan perintah Membatalkan atau klik tombol yang sesuai pada toolbar.

11. Masukkan teks “Total Waktu Perjalanan” di sel D9.

12. Hitung total waktu perjalanan.

13. Hiasi meja dengan warna dan sorot pinggiran meja.

Pekerjaan mandiri

Hitung menggunakan spreadsheetUnggulbiaya anak sekolah yang berencana bertamasya ke kota lain.

Dokumen dipilih untuk dilihat Excel pr.r. 4.docx

Perpustakaan
bahan

Kerja Praktek 4

"Tautan. Fungsi bawaan MS Excel."

Dengan menyelesaikan tugas dalam topik ini, Anda akan belajar:

    Lakukan operasi salin, pindahkan, dan isi otomatis pada sel dan rentang individual.

    Membedakan jenis tautan (mutlak, relatif, campuran)

    Gunakan fungsi matematika dan statistik bawaan Excel dalam perhitungan.

MS Excel berisi 320 fungsi bawaan. Cara paling sederhana menerima informasi lengkap tentang salah satunya adalah dengan menggunakan menuReferensi . Untuk kenyamanan, fungsi di Excel dibagi ke dalam kategori (matematika, keuangan, statistik, dll.).
Setiap pemanggilan fungsi terdiri dari dua bagian: nama fungsi dan argumen dalam tanda kurung.

Meja. Fungsi Excel bawaan

* Ditulis tanpa argumen.

Meja . Jenis tautan

Latihan.

1. Biaya 1 kW/jam telah ditetapkan. pembacaan listrik dan meteran untuk bulan-bulan sebelumnya dan saat ini. Penting untuk menghitung konsumsi listrik selama periode yang lalu dan biaya listrik yang dikonsumsi.

Teknologi kerja:

1. Meratakan teks dalam sel. Pilih sel A3:E3. Beranda - Format - Format Sel - Perataan: horizontal - di tengah, vertikal - di tengah, tampilan - bungkus dengan kata-kata.

2. Di sel A4 masukkan: Sq. 1, di sel A5 masukkan: Sq. 2. Pilih sel A4:A5 dan gunakan penanda isi otomatis untuk mengisi penomoran apartemen, termasuk 7.

5. Isi sel B4:C10 seperti yang ditunjukkan.

6. Di sel D4, masukkan rumus untuk mencari konsumsi listrik. Dan isi baris di bawah ini menggunakan penanda pelengkapan otomatis.

7. Di sel E4, masukkan rumus untuk mencari biaya listrik=D4*$B$1. Dan isi baris di bawah ini menggunakan penanda pelengkapan otomatis.

Catatan!
Saat pengisian otomatis, alamat sel B1 tidak berubah,
Karena kumpulan tautan absolut.

8. Di sel A11, masukkan teks “Statistik”, pilih sel A11:B11, dan klik tombol “Gabung dan Pusatkan” pada toolbar.

9. Di sel A12:A15, masukkan teks yang ditunjukkan pada gambar.

10. Klik sel B12 dan masukkan fungsi matematikaJUMLAH , untuk melakukan ini, Anda perlu mengklik di bilah rumusdengan tandafx dan pilih fungsinya, serta konfirmasi rentang selnya.

11. Fungsi diatur serupa di sel B13:B15.

12. Anda melakukan perhitungan pada Lembar 1, beri nama Listrik.

Pekerjaan mandiri

Latihan 1:

Hitung usia Anda dari tahun ini hingga 2030 menggunakan penanda pelengkapan otomatis. Tahun lahir Anda adalah acuan mutlak. Lakukan perhitungan pada Sheet 2. Ubah nama Sheet 2 menjadi Age.

Latihan 2: Buatlah tabel sesuai contoh.Di dalam selSAYA5: L12 danD13: L14 harusnya ada rumusnya: AVERAGE, COUNTIF, MAX, MIN. SelB3: H12 diisi dengan informasi oleh Anda.

Dokumen dipilih untuk dilihat Excel pr.r. 5.docx

Perpustakaan
bahan

Kerja Praktek 5

Dengan menyelesaikan tugas dalam topik ini, Anda akan belajar:

Teknologi untuk membuat dokumen spreadsheet;

Tetapkan tipe pada data yang digunakan;

Pembuatan rumus dan aturan untuk mengubah link di dalamnya;

Gunakan fungsi statistik bawaan Excel untuk perhitungan.

Latihan 1. Hitung jumlah hari hidup.

Teknologi kerja:

1. Luncurkan aplikasi Excel.

2. Di sel A1, masukkan tanggal lahir Anda (hari, bulan, tahun – 20/12/97). Rekam entri data Anda.

3. Lihat format tanggal yang berbeda(Beranda - Format Sel - Format Angka Lainnya - Tanggal) . Ubah tanggal menjadi mengetikHH.MM.YYYY. Contoh, 14/03/2001

4. Pertimbangkan beberapa jenis format tanggal di sel A1.

5. Masukkan tanggal hari ini di sel A2.

6. Di sel A3, hitung jumlah hari hidup menggunakan rumus. Hasilnya dapat disajikan sebagai tanggal, dalam hal ini harus dikonversi ke tipe numerik.

Tugas 2. Usia siswa. Berdasarkan daftar siswa tertentu dan tanggal lahir mereka. Tentukan siapa yang lahir lebih awal (nanti), tentukan siapa yang tertua (bungsu).


Teknologi kerja:

1. Dapatkan file Usia. Melalui jaringan lokal: Buka folder Network Neighborhood -Bos–Dokumen umum– kelas 9, cari file Umur. Salin dengan cara apa pun yang Anda tahu atau unduh dari halaman ini di bagian bawah aplikasi.

2. Mari kita hitung usia siswa. Untuk menghitung usia Anda perlu menggunakan fungsi tersebutHARI INI pilih tanggal hari ini, tanggal lahir siswa dikurangi, lalu hanya tahun yang diekstraksi dari tanggal yang dihasilkan menggunakan fungsi YEAR. Dari angka yang dihasilkan kita kurangi 1900 abad dan dapatkan usia siswa tersebut. Tulis rumusnya di sel D3= TAHUN(HARI INI()-С3)-1900 . Hasilnya dapat disajikan sebagai tanggal, kemudian harus dikonversi menjaditipe numerik.

3. Mari kita tentukan ulang tahun paling awal. Tulis rumusnya di sel C22=MIN(C3:C21) ;

4. Mari kita tentukan siswa termuda. Tulis rumusnya di sel D22=MIN(H3:H21) ;

5. Mari kita tentukan ulang tahun terbaru. Tulis rumusnya di sel C23=maks(C3:C21) ;

6. Mari kita tentukan siswa tertua. Tulis rumusnya di sel D23=MAX(D3:D21) .

Pekerjaan mandiri:
Tugas. Buatlah perhitungan yang diperlukan untuk tinggi badan siswa dalam satuan pengukuran yang berbeda.

Dokumen dipilih untuk dilihat Excel pr.r. 6.docx

Perpustakaan
bahan

Kerja Praktek 6

"MS.Excel. Fungsi statistik" Bagian II.

Tugas 3. Menggunakan spreadsheet, proses data menggunakan fungsi statistik. Informasi tentang siswa di kelas diberikan, termasuk nilai rata-rata triwulan, usia (tahun lahir) dan jenis kelamin. Tentukan nilai rata-rata anak laki-laki, proporsi siswa berprestasi di antara anak perempuan, dan selisih nilai rata-rata siswa dari berbagai usia.

Larutan:
Mari isi tabel dengan data awal dan lakukan perhitungan yang diperlukan.Perhatikan format nilai pada sel "IPK" (numerik) dan "Tanggal Lahir" (tanggal).

Tabel ini menggunakan kolom tambahan yang diperlukan untuk menjawab pertanyaan yang diajukan dalam masalah -usia pelajar dan merupakan muridnyaseorang siswa yang luar biasa dan seorang gadis serentak.
Untuk menghitung usia, rumus berikut digunakan (menggunakan sel G4 sebagai contoh):

=INTEGER((HARI INI()-E4)/365.25)

Mari kita mengomentarinya. Tanggal lahir siswa dikurangkan dari tanggal hari ini. Jadi, kita memperoleh jumlah hari yang telah berlalu sejak kelahiran siswa tersebut. Membagi angka ini dengan 365,25 (jumlah hari sebenarnya dalam setahun, 0,25 hari untuk tahun normal diimbangi dengan tahun kabisat), kita mendapatkan jumlah tahun siswa; akhirnya, menyoroti seluruh bagian - usia siswa.

Apakah seorang gadis adalah siswa yang berprestasi ditentukan oleh rumus (menggunakan sel H4 sebagai contoh):

=JIKA(DAN(D4=5,F4="w");1,0)

Mari kita lanjutkan ke perhitungan dasar.
Pertama-tama, Anda perlu menentukan skor rata-rata anak perempuan. Menurut definisi tersebut, skor total anak perempuan perlu dibagi dengan jumlah mereka. Untuk tujuan ini, Anda dapat menggunakan fungsi yang sesuai dari pemroses tabel.

=SUMIF(F4:F15,"w";D4:D15)/COUNTIF(F4:F15,"w")

Fungsi SUMIF memungkinkan Anda menjumlahkan nilai hanya dalam sel rentang yang memenuhi kriteria tertentu (dalam kasus kami, anak tersebut adalah laki-laki). Fungsi COUNTIF menghitung jumlah nilai yang memenuhi kriteria tertentu. Jadi kita mendapatkan apa yang kita butuhkan.
Untuk menghitung persentase siswa berprestasi di antara semua anak perempuan, kita akan mengambil jumlah anak perempuan yang berprestasi dengan jumlah total anak perempuan (di sini kita akan menggunakan sekumpulan nilai dari salah satu kolom bantu):

=SUM(H4:H15)/COUNTIF(F4:F15,"w")

Terakhir, kami akan menentukan perbedaan skor rata-rata anak-anak dari berbagai usia (kami akan menggunakan kolom bantu dalam perhitunganUsia ):

=ABS(SUMIF(G4:G15,15,D4:D15)/COUNTIF(G4:G15,15)-
SUMIF(G4:G15,16,D4:D15)/COUNTIF(G4:G15,16))

Harap dicatat bahwa format data di sel G18:G20 adalah numerik, dua tempat desimal. Dengan demikian, masalahnya terselesaikan sepenuhnya. Gambar tersebut menunjukkan hasil solusi untuk kumpulan data tertentu.

Dokumen dipilih untuk dilihat Excel pr.r. 7.docx

Perpustakaan
bahan

Kerja Praktek 7

“Membuat grafik menggunakan MS Excel”

Dengan menyelesaikan tugas dalam topik ini, Anda akan belajar:

Melakukan operasi pembuatan grafik berdasarkan data yang dimasukkan ke dalam tabel;

Edit data bagan, jenis dan desainnya.

Apa itu diagram? Bagan dirancang untuk mewakili data secara grafis. Garis, batang, kolom, sektor, dan elemen visual lainnya digunakan untuk menampilkan data numerik yang dimasukkan ke dalam sel tabel. Tampilan diagram tergantung pada jenisnya. Semua diagram, kecuali diagram lingkaran, memiliki dua sumbu: sumbu horizontal – sumbu kategori dan sumbu vertikal – sumbu nilai. Saat membuat bagan 3D, sumbu ketiga ditambahkan – sumbu seri. Seringkali bagan berisi elemen seperti kisi, judul, dan legenda. Garis kisi merupakan perpanjangan dari pembagian yang terdapat pada sumbu, judul digunakan untuk menjelaskan masing-masing elemen bagan dan sifat data yang disajikan di dalamnya, dan legenda membantu mengidentifikasi rangkaian data yang disajikan dalam bagan. Ada dua cara untuk menambahkan bagan: menyematkannya di lembar kerja saat ini atau menambahkan lembar bagan terpisah. Jika diagram itu sendiri menarik, maka ditempatkan pada lembar terpisah. Jika Anda perlu melihat diagram dan data yang menjadi dasar pembuatannya secara bersamaan, maka diagram tertanam akan dibuat.

Diagram disimpan dan dicetak bersama dengan buku kerja.

Setelah diagram dibuat, perubahan dapat dilakukan. Sebelum melakukan tindakan apa pun pada elemen diagram, pilih elemen tersebut dengan mengklik kiri elemen tersebut. Setelah ini, panggil menu konteks menggunakan tombol kanan mouse atau gunakan tombol yang sesuaiBilah alat bagan .

Tugas: Gunakan spreadsheet untuk membuat grafik fungsi Y=3,5x–5. Dimana X mengambil nilai dari –6 hingga 6 dengan kelipatan 1.

Teknologi kerja:

1. Luncurkan prosesor spreadsheet Excel.

2. Di sel A1 masukkan "X", di sel B1 masukkan "Y".

3. Pilih rentang sel A1:B1 dan pusatkan teks di dalam sel.

4. Di sel A2, masukkan angka -6, dan di sel A3, masukkan -5. Gunakan penanda IsiOtomatis untuk mengisi sel di bawah hingga opsi 6.

5. Di sel B2, masukkan rumus: =3,5*A2–5. Gunakan penanda pelengkapan otomatis untuk memperluas rumus ini hingga akhir parameter data.

6. Pilih seluruh tabel yang Anda buat dan berikan batas eksternal dan internal.

7. Pilih header tabel dan isi area bagian dalam.

8. Pilih sel tabel yang tersisa dan isi area bagian dalam dengan warna berbeda.

9. Pilih seluruh tabel. Pilih Sisipkan dari bilah menu -Diagram , Tipe: titik, Tampilan: Titik dengan lekukan halus.

10. Pindahkan bagan ke bawah tabel.

Pekerjaan mandiri:

    Gambarkan fungsi y=dosa(X)/ Xpada segmen [-10;10] dengan langkah 0,5.

    Tampilkan grafik fungsi: a) y=x; b) kamu=x 3 ; c) y=-x pada ruas [-15;15] dengan langkah 1.

    Buka file "Kota" (buka folder jaringan - kelas 9 - Kota).

    Hitung biaya nelpon tanpa diskon (kolom D) dan biaya nelpon termasuk diskon (kolom F).

    Untuk gambaran yang lebih jelas, buatlah dua diagram lingkaran. (1 diagram biaya nelpon tanpa diskon; 2 diagram biaya nelpon dengan diskon).

Dokumen dipilih untuk dilihat Excel pr.r. 8.docx

Perpustakaan
bahan

Kerja Praktek 8

KONSTRUKSI GRAFIK DAN GAMBAR DENGAN SARANA MS EXCEL

1. Konstruksi gambar"PAYUNG"

Fungsi yang grafiknya disertakan dalam gambar ini diberikan:

y1= -1/18x 2 + 12, xО[-12;12]

kamu2= ​​-1/8x 2 +6, xО[-4;4]

kamu3= -1/8(X+8) 2 + 6, xО[-12; -4]

kamu4= -1/8(X-8) 2 + 6, xО

kamu5= 2(X+3) 2 – 9, xО[-4;0]

kamu6=1.5(X+3) 2 – 10, xО[-4;0]

- Luncurkan MS EXCEL

· - Di dalam selA1 masukkan penunjukan variabelX

· - Isi rentang sel A2:A26 dengan angka dari -12 hingga 12.

Kami akan memperkenalkan rumus secara berurutan untuk setiap grafik fungsi. Untuk y1= -1/8x 2 + 12, xО[-12;12], untuk
kamu2= ​​-1/8x 2 +6, xО[-4;4], dst.

Prosedur:

    Tempatkan kursor di dalam selDALAM 1 dan masukkamu1

    Ke selPADA 2 masukkan rumusnya=(-1/18)*A2^2 +12

    Klik Memasuki di papan ketik

    Nilai fungsi dihitung secara otomatis.

    Memperluas rumus ke sel A26

    Demikian pula dengan selC10 (karena kita menemukan nilai fungsi hanya pada ruas x dari [-4;4]) masukkan rumus grafik fungsikamu2= ​​-1/8x 2 +6. DLL.

Hasilnya adalah ET berikut

Setelah semua nilai fungsi dihitung, Anda bisamembangun grafik inifungsi

    Pilih rentang sel A1:G26

    Pada toolbar pilihSisipkan menu → Diagram

    Di jendela Chart Wizard, pilihTitik → Pilih tipe yang tepat→ Tekan Oke .

Hasilnya akan menjadi gambar berikut:

Tugas untuk pekerjaan individu:

Buatlah grafik fungsi dalam satu sistem koordinat.x dari -9 hingga 9 dengan kelipatan 1 . Dapatkan gambarnya.

1. "Kacamata"

2. "Kucing" Penyaringan (sampling) data dalam sebuah tabel memungkinkan Anda untuk menampilkan hanya baris-baris yang isi selnya memenuhi kondisi tertentu atau beberapa kondisi. Berbeda dengan pengurutan, pemfilteran tidak menyusun ulang data, tetapi hanya menyembunyikan catatan yang tidak memenuhi kriteria pemilihan yang ditentukan.

Pemfilteran data dapat dilakukan dengan dua cara:menggunakan Filter Otomatis atau Filter Lanjutan.

Untuk menggunakan filter otomatis, Anda memerlukan:

Hai tempatkan kursor di dalam tabel;

Hai pilih timData - Filter - Filter Otomatis;

Hai perluas daftar kolom tempat pemilihan akan dilakukan;

Hai pilih nilai atau kondisi dan atur kriteria pemilihan di kotak dialogFilter otomatis khusus.

Untuk memulihkan semua baris tabel sumber, Anda perlu memilih baris semua di daftar drop-down filter atau memilih perintahData - Filter - Tampilkan semua.

Untuk membatalkan mode pemfilteran, Anda perlu menempatkan kursor di dalam tabel dan memilih perintah menu lagiData - Filter - Filter Otomatis (hapus centang pada kotak).

Filter lanjutan memungkinkan Anda membuat beberapa kriteria pemilihan dan melakukan pemfilteran data spreadsheet yang lebih kompleks dengan menentukan serangkaian kondisi pemilihan di beberapa kolom. Memfilter rekaman menggunakan filter lanjutan dilakukan menggunakan perintah menuData - Filter - Filter lanjutan.

Latihan.

Buatlah tabel sesuai dengan contoh pada gambar. Simpan sebagai Sortir.xls.

Teknologi pelaksanaan tugas:

1. Buka dokumen Sort.xls

2.

3. Jalankan perintah menuData - Penyortiran.

4. Pilih kunci pengurutan pertama "Ascending" (Semua departemen dalam tabel akan disusun berdasarkan abjad).

Ingatlah bahwa setiap hari kita perlu mencetak daftar barang yang tersisa di toko (memiliki saldo bukan nol), tetapi untuk ini kita perlu terlebih dahulu mendapatkan daftar tersebut, yaitu. menyaring datanya.

5. Tempatkan kursor bingkai di dalam tabel data.

6. Jalankan perintah menuData - Saring

7. Batalkan pilihan tabel.

8. Setiap sel header tabel sekarang memiliki tombol "Panah Bawah"; tidak dicetak; ini memungkinkan Anda untuk menetapkan kriteria filter. Kami ingin meninggalkan semua catatan dengan sisa bukan nol.

9. Klik tombol panah yang muncul di kolomJumlah yang tersisa . Sebuah daftar akan terbuka dari mana pemilihan akan dilakukan. Pilih garisKondisi. Atur kondisinya: > 0. KlikOKE . Data dalam tabel akan disaring.

10. Alih-alih daftar lengkap produk, kami akan menerima daftar produk yang terjual hingga saat ini.

11. Filternya bisa diperkuat. Jika Anda juga memilih departemen, Anda bisa mendapatkan daftar barang yang tidak terkirim berdasarkan departemen.

12. Untuk melihat kembali daftar semua barang yang tidak terjual untuk semua departemen, Anda harus memilih kriteria “Semua” di daftar “Departemen”.

13. Untuk menghindari kebingungan dalam laporan Anda, masukkan tanggal yang secara otomatis akan berubah sesuai dengan waktu sistem komputerRumus - Sisipkan Fungsi - Tanggal dan Waktu - Hari Ini .

Pekerjaan mandiri

"MS.Excel. Fungsi statistik"

1 tugas (umum) (2 poin).

Menggunakan spreadsheet, proses data menggunakan fungsi statistik.
1. Diberikan informasi tentang siswa kelas (10 orang), termasuk nilai matematika selama satu bulan. Hitunglah banyaknya angka lima, empat, dua, dan tiga, carilah nilai rata-rata setiap siswa dan nilai rata-rata seluruh kelompok. Buatlah bagan yang menggambarkan persentase nilai dalam suatu kelompok.

2.1 tugas (2 poin).

Empat orang sahabat bepergian dengan tiga moda transportasi: kereta api, pesawat, dan kapal. Nikolai berlayar sejauh 150 km dengan perahu, menempuh jarak 140 km dengan kereta api, dan terbang sejauh 1.100 km dengan pesawat. Vasily berlayar sejauh 200 km dengan perahu, menempuh jarak 220 km dengan kereta api, dan terbang sejauh 1.160 km dengan pesawat. Anatoly terbang sejauh 1.200 km dengan pesawat, menempuh jarak 110 km dengan kereta api, dan berlayar sejauh 125 km dengan perahu. Maria menempuh perjalanan sejauh 130 km dengan kereta api, terbang sejauh 1500 km dengan pesawat, dan berlayar sejauh 160 km dengan perahu.
Buat spreadsheet berdasarkan data di atas.

    Tambahkan kolom ke tabel yang akan menampilkan jumlah kilometer yang ditempuh masing-masing orang.

    Hitung jumlah kilometer yang ditempuh anak-anak dengan kereta api, terbang dengan pesawat, dan berlayar dengan perahu (untuk setiap jenis angkutan secara terpisah).

    Hitung jumlah kilometer semua teman.

    Tentukan jumlah kilometer maksimum dan minimum yang ditempuh teman dengan menggunakan semua jenis angkutan.

    Tentukan jumlah rata-rata kilometer untuk semua jenis angkutan.

2.2 tugas (2 poin).

Buatlah tabel “Danau Eropa” menggunakan data luas (km persegi) dan kedalaman terbesar (m) berikut: Ladoga 17.700 dan 225; Onega 9510 dan 110; Laut Kaspia 371.000 dan 995; Wenern 5550 dan 100; Chudskoe dengan Pskovsky 3560 dan 14; Balaton 591 dan 11; Jenewa 581 dan 310; Basah 1900 dan 119; Konstanz 538 dan 252; Mälaren 1140 dan 64. Tentukan luas danau terbesar dan terkecil, danau terdalam dan terdangkal.

2.3 tugas (2 poin).

Buatlah tabel “Sungai Eropa” menggunakan panjang (km) dan luas cekungan berikut (ribu km persegi): Volga 3688 dan 1350; Danube 2850 dan 817; Rhine 1330 dan 224; Elbe 1150 dan 148; Wisula 1090 dan 198; Loire 1020 dan 120; Ural 2530 dan 220; Don 1870 dan 422; Sena 780 dan 79; Thames 340 dan 15. Tentukan sungai terpanjang dan terpendek, hitung luas total daerah aliran sungai, rata-rata panjang sungai di Rusia bagian Eropa.

Tugas 3 (2 poin).

Bank mencatat ketepatan waktu pembayaran pinjaman yang diberikan kepada beberapa organisasi. Jumlah pinjaman dan jumlah yang telah dibayarkan oleh organisasi diketahui. Denda ditetapkan bagi debitur: jika perusahaan telah melunasi pinjaman lebih dari 70 persen, maka dendanya adalah 10 persen dari jumlah utangnya, jika tidak, dendanya adalah 15 persen. Hitung denda untuk setiap organisasi, denda rata-rata, jumlah total uang yang akan diterima bank tambahan. Tentukan denda rata-rata organisasi anggaran.

Temukan materi untuk pelajaran apa pun,



Memuat...
Atas