Helaian Penipuan Formula Excel: Panduan Lanjutan

Oleh itu, anda telah melalui perkara asas, menangani perkara perantaraan, dan kini anda bersedia untuk bergelut dengan senjata besar — ​​formula termaju Excel! Excel mempunyai banyak fungsi dan formula lanjutan untuk pengiraan yang canggih, jadi adalah berguna untuk mempunyai panduan yang menentukan dengan tepat yang anda perlukan.

Lembaran tipu formula Excel ini merangkumi formula ramalan lanjutan, analisis statistik, fungsi manipulasi data, pengendalian ralat dan banyak lagi.

Rujukan ini akan melengkapkan anda dengan pengetahuan tentang cara menggunakan fungsi lanjutan ini. Setiap formula disertakan dengan penerangan yang jelas, sintaks dan contoh praktikal untuk membantu pengguna Excel pertengahan menjadi pengguna kuasa lanjutan.

Sila muat turun dan cetak helaian cheat dan pastikan ia mudah digunakan.

Ok, mari kita mulakan.

Excel-formulas-cheat-sheet-advanced-1 Muat turun

Pertama, mari kita masuk ke formula Array.

Isi kandungan

• Formula Tatasusunan
  • 1. Fungsi UNIK
  • 2. Fungsi SORT
  • 3. Fungsi PENAPIS
• Merawak Fungsi Excel
  • 1. Fungsi RANDBETWEEN
  • 2. Fungsi RANDARRAY
• Formula Ramalan Lanjutan dalam Microsoft Excel
  • 1. Fungsi RAMALAN.ETS
  • 2. Fungsi TREND
• Formula Statistik Lanjutan
  • 1. Fungsi PERSENTILE
  • 2. Fungsi KUARTIL
• Formula Analisis Dan Manipulasi Data Lanjutan
  • 1. Fungsi TRANSPOSE
  • 2. FREKUENSI Fungsi
  • 3. Fungsi GETPIVOTDATA
• Pengendalian Ralat Lanjutan
• Formula Carian Lanjutan
  • 1. Fungsi XLOOKUP
  • 2. Fungsi XMATCH
• Fikiran Akhir

Formula Tatasusunan

Helaian panduan pemula kami menunjukkan kepada anda cara mengisih dan menapis data anda secara manual. Pengguna lanjutan melakukan ini secara pengaturcaraan dengan formula tatasusunan.

Formula tatasusunan membolehkan anda melakukan pengiraan pada berbilang sel secara serentak. Ini adalah tiga fungsi utama:

1. UNIK

2. SUSUN

3. PENAPIS

Sesetengah fungsi ini hanya tersedia dalam versi terkini Microsoft Excel.

1. Fungsi UNIK

Fungsi UNIQUE menerima julat dan mengembalikan senarai nilai unik.

Katakan anda mempunyai data jualan untuk barangan pakaian. Untuk mencari item unik dalam lajur A, gunakan formula ini:

=UNIK(A2:A6)

2. Fungsi SORT

Fungsi SORT mengisih kandungan julat. Sintaksnya ialah:

SORT(array, [isih_indeks], [urutan_isih], [by_col])

• array : julat nilai untuk diisih.

• sort_index : lajur untuk diisih (1 secara lalai)

• sort_order : 1 untuk menaik (lalai) atau 2 untuk menurun).

• by_col : BENAR untuk mengisih mengikut lajur (lalai) atau FALSE untuk mengisih mengikut baris.

Tiga hujah terakhir adalah pilihan, dan lalai biasanya adalah apa yang anda mahukan.

Untuk mengisih data sampel mengikut lajur pertama, gunakan formula ini:

=SORT(A2:C6)

3. Fungsi PENAPIS

Fungsi FILTER membolehkan anda menapis julat pada keadaan tertentu. Ini adalah sintaksnya:

=FILTER(tatasusunan, sertakan, [jika_kosong])

• array : julat untuk ditapis.

• include : keadaan yang menentukan nilai yang hendak ditapis.

• if_empty : menentukan perkara yang perlu dikembalikan jika tiada nilai memenuhi kriteria penapisan (lalai ialah “”).

Katakan anda ingin menapis baris dalam data sampel untuk hanya menunjukkan di mana nilai jualan lebih besar daripada $15. Gunakan formula ini:

=PENAPIS(A2:C6, C2:C6>15)

Merawak Fungsi Excel

Cheat perantaraan kami menunjukkan cara menggunakan fungsi RAND yang menghasilkan nombor rawak antara 0 dan 1.

Pengguna Excel lanjutan tahu cara menggunakan fungsi rawak untuk menjana data sampel dengan cepat.

1. Fungsi RANDBETWEEN

Fungsi RANDBETWEEN adalah lebih fleksibel daripada RAND kerana anda boleh menentukan nombor bawah dan atas sebagai sesuatu selain daripada 0 dan 1.

Untuk menjana data dengan nombor antara 1 dan 100, masukkan formula ini ke dalam sel A1:

=RANDBETWEEN(1, 100)

Kemudian, salin sel ke seberapa banyak baris dan lajur yang anda mahu. Ia mengambil masa beberapa saat untuk menghasilkan grid nombor rawak:

2. Fungsi RANDARRAY

Anda mungkin berfikir adalah baik untuk mengelakkan salinan manual fungsi RANDBETWEEN. Untuk menjadi sangat maju, anda boleh menggunakan fungsi RANDARRAY baharu dalam versi terkini Microsoft Excel.

Sintaksnya ialah:

RANDARRAY([baris], [lajur], [min], [maks], [nombor bulat])

• baris : bilangan baris

• lajur : bilangan lajur

• min : nombor terendah

• maks : nombor tertinggi

• nombor bulat : lalai kepada TRUE, jika tidak menggunakan nombor perpuluhan.

Untuk menjana nombor rawak antara 1 dan 100 merentas enam baris dan sembilan lajur, gunakan formula ini:

=RANDARRAY(6, 9, 1, 100, BENAR)

Formula Ramalan Lanjutan dalam Microsoft Excel

Fungsi ramalan Excel digunakan untuk meramalkan nilai masa hadapan berdasarkan aliran data sedia ada. Fungsi ini membantu mengenal pasti corak dan aliran projek berdasarkan data anda.

1. Fungsi RAMALAN.ETS

Fungsi FORECAST yang lebih lama telah digantikan dengan satu set fungsi yang lebih baharu dalam Excel 2016.

Anda memilih fungsi berdasarkan model ramalan khusus yang anda inginkan. Contohnya, fungsi FORECAST.ETS menggunakan algoritma Exponential Smoothing .

Sintaksnya ialah:

FORECAST.ETS(target_tarikh, nilai, garis masa)

• target_date : tarikh yang anda inginkan nilai yang dikira.

• nilai : data sejarah.

• garis masa : julat tarikh

Katakan anda mempunyai tarikh dari 1 hingga 5 Januari dalam lajur A dan jumlah jualan dalam lajur B. Formula ini akan meramalkan jumlah jualan seterusnya:

=FORECAST.ETS(“6 Januari 2023”, B2:B6, A2:A6)

2. Fungsi TREND

Fungsi TREND menayangkan satu set nilai berdasarkan kaedah kuasa dua terkecil. Ia mengembalikan tatasusunan. Sintaksnya ialah:

TREND(known_y, [known_x], [new_x], [const])

• known_y : julat nilai y

• known_x : julat nilai x

• new_x : julat nilai yang dikira

Selalunya, known_y ialah titik data, manakala known_x ialah tarikh.

Menggunakan data yang sama seperti dalam contoh sebelumnya, anda boleh memasukkan formula di bawah ke dalam sel C2. Satu set nilai akan dijana.

=TREND(B2:B6, A2:A6)

Formula Statistik Lanjutan

Fungsi statistik lanjutan termasuk mengira persentil dan kuartil. Sesetengah fungsi matematik tersedia untuk keserasian ke belakang, tetapi disyorkan untuk menggunakan versi yang paling terkini.

1. Fungsi PERSENTILE

Fungsi ini mengira peratusan titik data yang jatuh di bawah nilai tertentu. Sintaksnya ialah:

PERCENTILE.INC(tatasusunan, k)

• tatasusunan : julat sel

• k : persentil dari 0 hingga 1

Katakan anda ingin mengira persentil ke-70 data dalam lajur B. Gunakan formula ini:

=PERCENTILE.INC(B2:B6, 0.7)

2. Fungsi KUARTIL

Fungsi ini ialah variasi fungsi persentil tetapi menggunakan sukuan untuk membahagikan data. Ini adalah sintaksnya:

QUARTILE.INC(array, quart)

• tatasusunan : julat data

• sukuan : 1 untuk persentil ke-25, 2 untuk persentil ke-50, 3 untuk persentil ke-75, dan 4 untuk maksimum.

Formula di bawah akan mengira kuartil pertama data dalam lajur A.

=QUARTILE.INC(A2:A5, 1)

Formula Analisis Dan Manipulasi Data Lanjutan

Beberapa fungsi lanjutan membolehkan anda menukar format data, menganalisis taburan kekerapan dan mengekstrak data daripada jadual pangsi.

1. TRANSPOSE

2. KEKERAPAN

3. GETPIVOTDATA

1. Fungsi TRANSPOSE

Kadangkala anda ingin mengalihkan data dalam baris anda ke dalam lajur dan sebaliknya. Anda boleh melakukan ini secara manual atau gunakan fungsi TRANSPOSE sebaliknya.

Katakan anda mempunyai item "T-Shirt", "Hoodie" dan "Jeans" dalam sel A2, A3 dan A4. Anda mahu menjadikannya tajuk lajur. Fungsi ini mengembalikan nilai dalam satu baris:

=TRANSPOSE(A2:A4)

2. FREKUENSI Fungsi

Fungsi ini mengira taburan kekerapan nilai dalam set data. Ini adalah sintaksnya:

KEKERAPAN(susun_data, tatasusunan_tong)

• data_array : julat nilai.

• bins_array : selang untuk digunakan.

Katakan anda mempunyai data jualan dalam lajur B dan anda ingin menganalisis taburan kekerapan nilai berdasarkan bilangan amaun:

• bawah 20.

• dari 20 hingga 80.

• atas 80.

Itu mewakili tiga tong sampah dan boleh dikira dengan formula ini:

=KEKERAPAN(A2:A6, {20,80})

Untuk lebih lanjut mengenai pengagihan kekerapan dalam Excel, lihat video ini:

3. Fungsi GETPIVOTDATA

Fungsi ini membolehkan anda mengekstrak maklumat diringkaskan daripada jadual pangsi. Ini adalah sintaksnya:

GETPIVOTDATA(medan_data, jadual_pangsi, [medan1, item1], [medan2, item2], …)

• data_field : medan data atau nilai yang anda ingin dapatkan daripada jadual pangsi.

• pivot_table : rujukan kepada jadual pangsi.

• medan1, item1, dsb. : pasangan medan/item untuk ditapis.

Katakan anda mempunyai jadual pangsi berdasarkan warna barang yang dijual. Untuk mengekstrak jualan untuk item merah, gunakan formula ini:

=GETPIVOTDATA(“Jualan”, A1, “Warna”, “Merah”)

Pengendalian Ralat Lanjutan

Malah formula Excel yang paling asas boleh menghasilkan ralat. Pengguna pertengahan harus tahu cara menggunakan ISERROR untuk mengendalikan ralat. Pengguna lanjutan juga harus biasa dengan fungsi ERROR.TYPE untuk pengecaman ralat.

Fungsi ERROR.TYPE membantu mengenal pasti jenis ralat tertentu dalam sel atau formula.

Ia mengembalikan nilai angka yang sepadan dengan jenis ralat yang berbeza, seperti #N/A, #VALUE!, #REF!, dan banyak lagi.

Katakan anda mempunyai ralat dalam sel A1 dan anda ingin mengenal pasti jenis ralatnya. Formula berikut akan mengembalikan nombor yang sepadan dengan ralat tertentu:

=ERROR.TYPE(A1)

Anda boleh menggabungkan ini dengan berbilang fungsi untuk bertindak balas secara berbeza bergantung pada jenis ralat. Ini adalah ralat yang paling biasa dan nilainya:

1. #NULL! (tiada sel biasa ditemui Dalam julat)

2. #DIV/0! ( pembahagian dengan sifar atau sel kosong)

3. #NILAI! (jenis data atau hujah yang tidak sesuai dalam formula)

4. #REF! (sel yang dirujuk telah dipadamkan atau terdapat rujukan pekeliling )

5. #NAMA? (Excel tidak mengenali fungsi atau julat)

6. #NUM! (nilai angka tidak sah)

7. #N/A (nilai tidak dapat ditemui)

Katakan anda ingin mengendalikan tiga jenis ralat tertentu. Gunakan formula ini untuk memaparkan mesej ralat tertentu berdasarkan jenis:

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, “Bahagi dengan ralat sifar”, IF(ERROR.TYPE(C2)=3, “Ralat nilai tidak sah”, IF(ERROR.TYPE(C2) )=7, “Ralat nilai tidak ditemui”, “Ralat lain”))), C2)

Formula Carian Lanjutan

Helaian cheat pemula dan pertengahan kami merangkumi pilihan fungsi carian . Berikut ialah beberapa pilihan lanjutan:

1. XLOOKUP

2. XMATCH

1. Fungsi XLOOKUP

Fungsi carian ini membolehkan anda mencari nilai dalam julat dan mengembalikan nilai yang sepadan daripada lajur atau julat lain.

Ia menawarkan lebih fleksibiliti daripada fungsi carian yang lebih mudah seperti VLOOKUP . Ini adalah sintaksnya:

XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])

• lookup_value : nilai yang anda ingin cari.

• lookup_array : julat untuk carian.

• return_array : julat yang akan menunjukkan nilai yang sepadan.

• match_mode : padanan tepat (0), seterusnya lebih kecil (1), seterusnya lebih besar (-1), atau padanan kad bebas (2).

• mod_carian : -1 untuk atas ke bawah, 1 untuk bawah ke atas atau 2 untuk carian binari.

• if_not_found : menetapkan nilai untuk dikembalikan jika tiada padanan ditemui.

Katakan anda ingin mencari julat data untuk kejadian pertama item pakaian dan mengembalikan jumlah jualan. Formula ini akan mencari teks "Hoodie" dan mengembalikan nilai dalam sel bersebelahan jika ditemui:

=XLOOKUP(“Hoodie”, A2:A6, B2:B6)

2. Fungsi XMATCH

Fungsi ini membolehkan anda mencari kedudukan nilai yang ditentukan dalam julat atau tatasusunan. Ini adalah sintaksnya:

XMATCH(nilai_carian, tatasusunan_carian, [jenis_padanan], [mod_carian])

• lookup_value : nilai yang anda ingin cari.

• lookup_array : Julat yang anda ingin cari.

• match_type : padanan tepat (0), terkecil seterusnya (-1), terbesar seterusnya (1).

• search_mode : carian binari (1) atau carian linear (2).

Katakan anda ingin mencari kejadian pertama item kuning dalam julat dalam lajur B. Gunakan formula ini:

=XMATCH(“Kuning”, B2:B6, 0)

Fikiran Akhir

Helaian tipu ini telah merangkumi pelbagai fungsi, daripada analisis statistik, formula carian, teknik manipulasi data dan strategi pengendalian ralat.

Contoh dan penjelasan yang diberikan membantu menjelaskan formula lanjutan ini, menjadikannya boleh diakses walaupun kepada mereka yang mempunyai pengalaman terhad.

Semasa anda mula memasukkannya ke dalam tugas Excel anda, anda sedang dalam perjalanan untuk meningkatkan kemahiran Excel anda ke tahap lanjutan.

Tetapi ingat, helaian penipuan ini hanyalah puncak gunung es. Perkara yang sangat menakjubkan berlaku apabila anda menjadi kreatif, mencampur dan memadankan formula ini dan menyesuaikannya untuk menyelesaikan cabaran unik anda. Excel adalah seperti kanvas, dan formula ini adalah palet anda — jadi teruskan, cat karya agung anda!