Sangat bagus untuk menguruskan data besar dengan hanya beberapa klik tetikus dan Excel semestinya satu-satunya alat yang membolehkan anda melakukan ini. Sekiranya anda masih tidak mengetahui trik ajaib Excel, berikut adalah tutorial Advanced Excel untuk membantu anda mempelajari Excel dengan mendalam.
Lihat semua topik yang dibincangkan dalam artikel ini:
- Keselamatan
- Tema MS Excel
- Templat
- Grafik
- Pilihan Percetakan
- Jadual Data
- Carta
- Jadual Pangsi
- Carta Pangsi
- Pengesahan Data
- Menyaring Data
- Menyusun Data
- Rujukan Silang dalam Excel
- Terjemahan Bahasa
Jadi, inilah aspek pertama dan terpenting yang perlu anda ketahui dalam Tutorial Excel Lanjutan ini.
Keselamatan
Excel memberikan keselamatan pada 3 tahap:
- Tahap fail
- Tahap lembaran kerja
- Tahap buku kerja
Keselamatan tahap fail:
Keselamatan tahap fail merujuk kepada mengamankan fail Excel anda dengan menggunakan kata laluan untuk mengelakkan orang lain membukanya dan mengubahnya. Untuk melindungi fail Excel, ikuti langkah-langkah yang diberikan:
1: Klik pada Fail tab
2: Pilih Maklumat pilihan
3: Pilih Lindungi Buku Kerja pilihan
4: Dari senarai, pilih Enkripsi dengan Kata Laluan pilihan
5: Masukkan a kata laluan dalam kotak dialog yang muncul
6: Masukkan semula kata laluan dan kemudian, klik pada okey
Ingatlah perkara berikut semasa memasukkan kata laluan:
- Jangan lupa kata laluan anda kerana tidak ada pemulihan kata laluan yang tersedia di Excel
- Tiada sekatan yang dikenakan tetapi, kata laluan Excel peka huruf besar kecil
- Elakkan menyebarkan fail yang dilindungi kata laluan dengan maklumat sensitif seperti butiran bank
- Melindungi fail dengan kata laluan tidak semestinya melindungi aktiviti berbahaya
- Elakkan berkongsi kata laluan anda
Keselamatan tahap Lembaran Kerja:
Untuk melindungi data yang ada dalam lembaran kerja daripada diubah, anda boleh mengunci sel dan kemudian melindungi lembaran kerja anda. Bukan hanya ini, anda juga boleh secara selektif membenarkan atau melarang akses ke sel-sel tertentu helaian anda kepada pelbagai pengguna. Sebagai contoh, jika anda mempunyai helaian yang mengandungi perincian penjualan untuk produk yang berbeza, dan setiap produk dikendalikan oleh individu yang berbeza. anda boleh membenarkan setiap kakitangan penjualan mengubah perincian hanya produk yang menjadi tanggungjawabnya dan bukan yang lain.
Untuk melindungi lembaran kerja anda, anda mesti mengikuti 2 langkah:
1: Buka kunci sel yang boleh diedit oleh pengguna
- Dalam helaian yang ingin anda lindungi, pilih semua sel yang dapat diedit oleh pengguna
- Buka tetingkap Font yang ada di tab Laman Utama
- Pilih Perlindungan
- Nyahtanda pilihan Terkunci
2: Melindungi lembaran kerja
- Untuk melindungi helaian, klik pada Kaji semula tab dan kemudian pilih Lindungi Lembaran pilihan
- Anda akan melihat kotak dialog berikut
- Daripada ' Benarkan semua pengguna lembaran kerja ini Pilihan, pilih salah satu elemen yang anda mahu
- Berikan beberapa kata laluan pilihan anda dan klik OK (Penetapan kata laluan adalah pilihan)
Tidak mengusahakan Lembaran Kerja:
Sekiranya anda ingin melindung helaian, anda boleh melakukannya dengan memilih Lembaran kerja yang tidak dilindungi pilihan dari Kaji semula tab. Sekiranya anda telah menentukan kata laluan semasa melindungi helaian, Excel akan meminta anda memasukkannya untuk melindungi helaian tersebut.
Keselamatan tahap buku kerja:
Keselamatan tahap buku kerja akan membantu anda mengelakkan pengguna lain menambah, menghapus, menyembunyikan, atau menamakan semula helaian anda. Inilah cara anda melindungi buku kerja anda di Excel:
satu: Daripada Kaji semula tab, pilih Lindungi Buku Kerja pilihan, anda akan melihat kotak dialog berikut:
2: Masukkan sebilangan kata laluan pilihan anda dan klik pada okey (Ini adalah pilihan, jika anda tidak memasukkan kata laluan, sesiapa sahaja boleh melindung buku kerja anda)
3: Masukkan semula kata laluan dan klik okey
Apabila buku kerja anda dilindungi, anda akan melihat bahawa pilihan Buku Kerja Lindungi akan diserlahkan seperti yang ditunjukkan di bawah:
Juga, jika anda mengklik kanan pada mana-mana helaian, anda akan melihat bahawa semua pilihan seperti Sembunyikan, Masukkan, Namakan semula, dan lain-lain tidak akan tersedia lagi. Lihat gambar yang ditunjukkan di bawah:
Tema MS Excel
MS Excel menyediakan sejumlah tema dokumen untuk membantu anda membuat dokumen rasmi. Dengan menggunakan tema ini, sangat mudah bagi anda untuk menyelaraskan fon, warna atau grafik yang berbeza. Anda juga mempunyai pilihan untuk menukar tema lengkap atau hanya warna atau fon, dll sesuai dengan pilihan anda. Di Excel, anda boleh:
- Gunakan tema warna standard
- Buat tema anda
- Ubah suai fon tema
- Tukar kesan
- Simpan tema tersuai anda
Gunakan tema warna standard:
Untuk memilih tema standard, anda boleh melakukan seperti berikut:
- Pilih Susun atur Halaman tab dari Riben
- Daripada Tema kumpulan, klik pada Warna
- Pilih mana-mana warna pilihan anda
Kumpulan warna pertama yang anda lihat. adalah warna MS Excel lalai.
Buat tema anda:
Sekiranya anda ingin menyesuaikan warna anda sendiri, klik pada pilihan Customize Colors yang terdapat di akhir senarai dropdown yang ditunjukkan dalam gambar di atas, dan anda akan melihat kotak dialog seperti yang ditunjukkan pada gambar di bawah:
Dari kotak dialog di atas, pilih warna pilihan anda untuk Accents, Hyperlinks, dll. Anda juga boleh membuat warna anda sendiri dengan mengklik lebih banyak warna pilihan. Anda akan dapat melihat semua perubahan yang anda buat di Contohnya panel hadir di sebelah kanan kotak dialog yang ditunjukkan dalam gambar di atas. Bukan hanya ini, anda juga dapat memberikan nama pada tema yang anda buat di Nama kotak dan Jimat ia. Sekiranya anda tidak mahu menyimpan perubahan yang anda buat, klik R kes dan kemudian klik pada Jimat .
Ubah suai fon tema:
Sama seperti bagaimana anda dapat mengubah warna tema, Excel membolehkan anda mengubah fon tema. Ini boleh dilakukan seperti berikut:
- Klik pada Susun atur Halaman daripada Riben tab
- Buka senarai juntai bawah Fon
- Pilih gaya fon yang anda suka
Anda juga dapat menyesuaikan gaya fon anda sendiri dengan mengklik pilihan Customize Fonts. anda akan membuka kotak dialog berikut apabila anda mengkliknya:
Beri apa sahaja Tajuk dan Fon badan pilihan anda dan kemudian, beri nama. Setelah ini selesai, klik pada Jimat .
Tukar Kesan:
Excel menyediakan sekumpulan besar kesan tema seperti garis, bayangan, pantulan, dan lain-lain yang boleh anda tambahkan. Untuk menambah kesan, klik pada Susun atur Halaman dan buka Kesan senarai juntai bawah dari Tema kumpulan kemudian pilih kesan yang anda mahu.
Simpan tema tersuai anda:
Anda boleh menyimpan semua perubahan yang anda buat dengan Menyimpan tema semasa seperti berikut:
1: Klik pada Susun atur Halaman , pilih Tema
2: Pilih Simpan Tema Semasa pilihan
3: Berikan nama untuk tema anda di Nama kotak
4: Klik Jimat
Catatan: Tema yang anda Simpan akan disimpan dalam folder Tema Dokumen pada pemacu tempatan anda dalam format .thmx.
Templat
Templat, secara umum, adalah corak atau model yang membentuk asas sesuatu. Templat Excel membantu anda meningkatkan kadar pengeluaran anda kerana ia membantu anda menjimatkan masa dan usaha untuk membuat dokumen anda. Untuk menggunakan templat Excel, anda harus mengklik Fail, kemudian s memilih Baru. Di sini, anda akan dapat melihat sebilangan templat Excel yang boleh anda pilih untuk semua jenis dokumen seperti Kalender, laporan kehadiran mingguan, invois ringkas, dll. Anda juga boleh mencari templat dalam talian. Sebagai contoh, jika anda memilih templat Belanjawan Bulanan Peribadi, templat anda akan kelihatan seperti yang ditunjukkan dalam gambar di bawah:
Grafik
Tidak seperti yang difikirkan oleh banyak orang, Excel tidak hanya membenarkan anda bermain-main dengan data, tetapi juga membolehkan anda menambahkan grafik ke dalamnya. Untuk menambah grafik, klik pada Masukkan tab dan anda akan dapat melihat sejumlah pilihan seperti menambahkan gambar, bentuk, PivotTables, Carta Pangsi , Peta, dll.
Memasukkan Imej:
Dalam tutorial Advanced Excel ini, saya akan menunjukkan kepada anda semua cara menambahkan gambar ke dokumen Excel anda. Pertama, klik pada Masukkan dan kemudian buka Ilustrasi senarai, pilih Gambar .
Pilih gambar yang ingin anda tambahkan ke dokumen anda. Dalam gambar yang ditunjukkan di bawah ini, saya telah menambahkan logo Excel:
Begitu juga, anda juga boleh menambahkan bentuk, ikon, SmartArts, dan lain-lain pada dokumen anda.
Pilihan Percetakan:
Untuk mencetak Lembaran Kerja MS Excel anda, klik Fail dan kemudian pilih Cetak pilihan. Anda akan melihat sejumlah pilihan sebelum mencetak dokumen yang membolehkan anda mencetak dokumen anda dalam corak dan susun atur yang berbeza. Anda boleh mengubah orientasi halaman, menambah margin, menukar pencetak, dll. Untuk mengetahui lebih lanjut mengenai perkara ini, tekan di sini .
Jadual Data
Jadual data di Excel dibuat untuk bereksperimen dengan nilai yang berbeza untuk a formula . Anda boleh membuat satu atau dua jadual Data pemboleh ubah di Excel. Jadual data adalah salah satu daripada tiga jenis alat analisis What-if yang terdapat di Excel.
Dalam Tutorial Excel Lanjutan ini, saya akan menunjukkan kepada anda semua cara membuat jadual Data satu-pemboleh ubah dan dua pemboleh ubah.
Membuat Jadual Data Satu Pemboleh Ubah:
Katakan misalnya anda membeli 16 gelas dengan harga $ 20 setiap satu. Dengan cara ini, anda perlu membayar sejumlah $ 320 untuk 16 gelas masing-masing. Sekarang, sekiranya anda ingin membuat jadual data yang akan menunjukkan harga yang berlainan untuk item yang sama, anda boleh melakukan seperti berikut:
1: Siapkan data seperti berikut:
2: Kemudian, salin hasil yang terdapat di B3 ke sel lain
3: Tuliskan jumlah item yang berbeza seperti di bawah:
cara menjalankan gerhana di tingkap
4: Pilih julat yang baru dibuat, klik pada Data tab, pilih Analisis Apa-Sekiranya daripada Ramalan kumpulan. Kemudian pilih Jadual Data pilihan.
5: Dari kotak dialog yang ditunjukkan di bawah, tentukan sel input lajur. (Ini kerana kuantiti baru ditentukan dalam lajur)
6: Setelah ini selesai, anda akan melihat semua nilai yang dihasilkan. Pilih semua sel dengan nilai output dan tentukan simbol $ kepadanya:
Jadual Data dua pemboleh ubah:
Untuk membuat Jadual Data dua pemboleh ubah untuk data yang sama yang diambil dalam contoh sebelumnya, ikuti langkah-langkah yang diberikan:
1: Salin hasil yang ada di B3 ke beberapa sel dan tentukan nilai baris dan lajur ujian seperti yang ditunjukkan di bawah:
1: Pilih julat, klik Data tab
2: Pilih Analisis What-If dari kumpulan Ramalan
3: Di tetingkap yang muncul, masukkan sel input Row dan Column seperti gambar di bawah:
4: Sebaik sahaja anda klik OK, anda akan melihat hasilnya untuk jadual yang lengkap
5: Pilih semua sel output, dan kemudian tentukan simbol $
Carta
Carta memberikan gambaran grafik untuk data anda. Carta ini menggambarkan nilai berangka dengan cara yang sangat bermakna dan mudah difahami. Carta adalah bahagian yang sangat penting dari Excel dan mereka bertambah baik dengan setiap versi baru MS Excel. Terdapat banyak jenis Carta yang boleh anda gunakan seperti Bar, Line, Pie, Area, dll.
Tutorial Excel Lanjutan ini akan membantu anda belajar bagaimana membuat carta di Excel.
Membuat Carta:
Untuk memasukkan carta, ikuti langkah-langkah yang diberikan:
1: Sediakan data carta anda
2: Pilih data yang telah disediakan, klik pada Insert present di Ribbon tab
3: Dari kumpulan Carta, pilih mana-mana carta pilihan anda
Jadual Pangsi:
Excel Jadual Pangsi adalah jadual statistik yang memadatkan data jadual yang mempunyai maklumat yang luas. Jadual ini membantu anda memvisualisasikan data anda berdasarkan bidang yang terdapat dalam jadual data anda. Dengan menggunakan Jadual Pivot, anda dapat memvisualisasikan data anda dengan mengubah baris dan lajur medan, menambahkan penapis, menyusun data anda, dll.
Membuat jadual Pangsi: Untuk membuat jadual pangsi, ikuti langkah-langkah yang diberikan:
1: Pilih kemarahan yang anda mahu buat jadual pangsi
2: Klik pada Masukkan
3: Pilih Jadual Pangsi dari kumpulan Jadual
4: Periksa sama ada julat yang diberikan betul
5: Pilih tempat di mana anda ingin membuat jadual iaitu Lembaran Kerja Baru atau yang sama
6: Excel akan membuat Jadual Pangsi kosong
7: Seret dan lepas medan yang ingin anda tambahkan untuk menyesuaikan jadual pangsi anda
Anda akan melihat jadual berikut dibuat:
Untuk mengetahui lebih banyak daripada yang dibahas dalam Tutorial Excel Lanjutan ini, tekan di sini .
Carta Pangsi
Excel Pivot Charts adalah alat visualisasi terbina dalam untuk jadual Pivot. Pivot Charts boleh dibuat seperti berikut
1: Buat Jadual Pangsi
2: Klik pada Masukkan tab
3: Pilih Carta Pangsi daripada Carta kumpulan
4: Ini akan membuka tetingkap yang akan menunjukkan kepada anda semua Pivot Charts
5: Pilih mana-mana graf jenis dan klik okey
Seperti yang anda lihat, Carta Pivot telah dibuat untuk Jadual Pangsi saya.
Pengesahan Data
Salah satu topik terpenting dari Tutorial Excel Lanjutan ini adalah Pengesahan Data. Ciri ini, seperti namanya, membolehkan anda mengkonfigurasi sel Lembaran Kerja Excel anda untuk menerima beberapa jenis data tertentu. Sebagai contoh, jika anda menginginkan sebilangan sel dalam helaian anda dan anda mahu mereka hanya menerima tarikh, anda boleh melakukannya dengan mudah menggunakan ciri Pengesahan Data Excel. Untuk melakukan ini, ikuti langkah-langkah yang diberikan:
1: Pilih semua sel yang ingin anda tetapkan jenis data tertentu kepada:
2: Klik pada Data tab hadir di Riben
3: Dari Alat Data kumpulan, pilih Pengesahan Data
4: Anda akan melihat tetingkap pop timbul dengan tiga pilihan iaitu Tetapan, Input Message, dan Error Alert
- Tetapan akan membolehkan anda memilih semua jenis data yang anda mahu julat yang dipilih diterima
- Bahagian Mesej Input akan membolehkan anda memasukkan mesej untuk pengguna yang memberikannya beberapa butiran mengenai data yang boleh diterima
- Bahagian Mesej Ralat akan memberitahu pengguna bahawa dia telah melakukan beberapa kesalahan dalam memberikan input yang diinginkan
Sekarang, jika anda memilih mana-mana sel dalam julat yang dipilih, pertama-tama anda akan melihat mesej yang meminta pengguna memasukkan nombor bulat lebih besar daripada 1.
Sekiranya pengguna gagal melakukannya, dia akan melihat mesej ralat yang sesuai seperti yang ditunjukkan di bawah:
Penapisan Data
Menyaring data merujuk pada pengambilan beberapa data tertentu yang memenuhi beberapa kriteria yang diberikan. Berikut adalah jadual yang akan saya gunakan untuk menyaring data:
Sekarang, sekiranya anda ingin menyaring data hanya untuk New York, yang perlu anda lakukan ialah memilih lajur City, klik pada Data hadir di tab Riben. Kemudian, dari Susun & Tapis kumpulan, pilih Tapis .
Setelah ini selesai, lajur Bandar menunjukkan senarai juntai bawah yang menyimpan nama semua bandar. Untuk menapis data untuk New York, buka senarai dropdown, hapus pilihan Dipilih Semua pilihan dan semak New York dan kemudian klik pada okey . Anda akan melihat jadual yang ditapis berikut:
Begitu juga, anda juga dapat menerapkan beberapa penapis dengan hanya memilih julat yang ingin anda gunakan penapisnya, dan kemudian memilih perintah Penapis.
Menyusun
Penyortiran data di Excel merujuk kepada mengatur baris data berdasarkan data yang ada di lajur. Sebagai contoh, anda boleh menyusun semula nama dari A-Z atau menyusun nombor masing-masing dari pesanan menaik atau menurun.
Sebagai contoh, pertimbangkan jadual yang ditunjukkan dalam contoh sebelumnya. Sekiranya anda ingin menyusun semula nama vendor bermula dari A, anda boleh melakukan seperti berikut:
- Pilih semua sel yang anda mahu isih
- Klik pada Susun hadir di Data tab dan anda akan melihat kotak dialog seperti gambar di bawah:
- Di sini, anda mempunyai dua pilihan berdasarkan keinginan anda untuk Memperluas pilihan anda untuk data yang lengkap atau hanya untuk pilihan semasa (saya memilih pilihan ke-2)
- Setelah selesai, anda akan melihat kotak dialog berikut:
- Di sini, anda boleh menambah lebih banyak lajur, menghapus lajur, mengubah urutan, dan lain-lain. Oleh kerana saya mahu menyusun lajur dari A-Z, saya akan mengklik okey .
Inilah topi meja seperti:
Begitu juga, anda boleh menyusun jadual anda menggunakan pelbagai peringkat dan pesanan.
Rujukan Silang dalam MS Excel
Sekiranya anda ingin mencari data di beberapa helaian dalam buku kerja anda, anda dapat memanfaatkannya Fungsi VLOOKUP . VLOOKUP fungsi dalam Excel digunakan untuk mencaridan mengemukakan data yang diperlukan dari hamparan. V dalam VLOOKUP merujuk kepada Vertical dan jika anda ingin menggunakan fungsi ini, data anda mesti disusun secara menegak. Untuk penjelasan terperinci mengenai VLOOKUP, tekan di sini .
Menggunakan VLOOKUP untuk mengambil data dari pelbagai helaian:
Untuk menggunakan fungsi VLOOKUP untuk mengambil nilai yang terdapat dalam helaian yang berbeza, anda boleh melakukan seperti berikut:
Siapkan data lembaran seperti yang ditunjukkan:
Helaian3:
Lembaran4:
Sekarang, untuk mendapatkan gaji pekerja ini dari lembaran4 hingga lembaran3, anda boleh menggunakan VLOOKUP seperti berikut:
Anda dapat melihat bahawa kedua-dua helaian3 dan lembaran4 dipilih. Apabila anda melaksanakan perintah ini, anda akan mendapat hasil berikut:
Sekarang, untuk mendapatkan gaji semua pekerja, salin formula seperti di bawah:
Makro
Makro mesti dipelajari di Excel. Dengan menggunakan Makro ini, anda dapat mengotomatisasi tugas yang anda laksanakan secara berkala dengan hanya merekodkannya sebagai makro. Makro dalam Excel pada dasarnya adalah tindakan atau menetapkan tindakan yang dapat dilakukan berulang kali secara automatik.
Dalam Tutorial MS Excel Lanjutan ini, anda akan belajar bagaimana anda boleh membuat dan menggunakan makro.
Membuat Makro:
Dalam contoh berikut, saya mempunyai beberapa maklumat mengenai kedai dan saya akan membuat makro untuk membuat grafik data untuk penjualan barang-barang beserta jumlah dan kuantiti mereka.
- Pertama, buat jadual seperti di bawah:
- Sekarang, klik pada Pandangan tab
- Klik pada Makro dan pilih Rakam Makro pilihan
- Masukkan beberapa nama untuk makro yang akan anda buat di kotak dialog yang muncul dan jika anda mahu, anda juga boleh membuat jalan pintas untuk makro ini
- Seterusnya, klik pada OKEY ( Setelah ini selesai, Excel mula merakam tindakan anda)
- Pilih sel pertama di bawah lajur Jumlah
- Ketik '= PRODUK (B2, B3)' dan tekan enter
- Masukkan tanda $ dari kumpulan Nombor tab Utama
- Kemudian, salin formula ke sel yang lain
- Sekarang, klik pada Insert dan pilih carta yang anda suka. Berikut adalah carta jadual yang ditunjukkan dalam gambar di atas:
- Setelah tindakan selesai, klik pada Lihat dan pilih pilihan Hentikan Rakaman dari Makro
Apabila anda melakukan ini, makro anda akan direkodkan. Sekarang setiap kali anda ingin melakukan semua tindakan ini, jalankan makro dan anda akan dapat melihat hasilnya dengan sewajarnya. Juga, perhatikan bahawa setiap kali anda membuat perubahan pada nilai-nilai yang ada dalam sel, makro anda akan membuat perubahan yang sesuai ketika anda menjalankannya dan akan menunjukkan hasil yang dikemas kini secara automatik.
Terjemahan Bahasa
Excel dengan cemerlang membolehkan pengguna menerjemahkan data ke dalam bahasa yang berbeza. Ia dapat mengesan bahasa yang terdapat dalam data anda secara automatik dan kemudian mengubahnya menjadi bahasa yang dikehendaki yang terdapat dalam senarai bahasa Excel. Ikuti langkah-langkah yang diberikan untuk melakukan terjemahan bahasa:
- Klik pada Kaji semula tab
- Pilih Terjemahkan dari Bahasa kumpulan
- Anda akan melihat tetingkap Penterjemah di mana anda boleh membiarkan Excel mengesan bahasa yang terdapat dalam helaian atau memberikan bahasa tertentu
- Kemudian, dari ‘ Ke 'Dropdown list, pilih bahasa yang anda mahu menukar data menjadi
Seperti yang anda lihat, teks yang saya telah ditukar menjadi bahasa Hindi.
Ini membawa kita ke akhir artikel ini mengenai Tutorial Excel Lanjutan. Saya harap anda jelas dengan semua yang telah dikongsi dengan anda. Pastikan anda berlatih sebanyak mungkin dan kembalikan pengalaman anda.
Ada soalan untuk kami? Sila sebutkan di bahagian komen blog 'Advanced Excel Tutorial' ini dan kami akan menghubungi anda secepat mungkin.
Untuk mendapatkan pengetahuan mendalam mengenai teknologi terkini dan pelbagai aplikasinya, anda boleh mendaftar secara langsung dengan sokongan 24/7 dan akses seumur hidup.