VBA adalah hal yang sangat berguna. Anda dapat menggabungkan data dari banyak file dan memproses informasi dalam jumlah besar menggunakan algoritma yang menarik. Misalnya,
makro Nodupes . Ini menggunakan kesalahan yang terjadi selama eksekusi kode sebagai pemeriksaan untuk keberadaan elemen dalam koleksi yang dihasilkan. Ada makro yang menggunakan rekursi untuk membentuk semua kemungkinan kombinasi dan permutasi.
Tapi saya ingin menunjukkan beberapa contoh penggunaan fungsi lembar kerja Excel standar.
Cukup sering, Anda harus pagar kebun keluar dari formula untuk mendapatkan teks atau angka yang Anda butuhkan dari sel. Ini biasanya terjadi karena penyajian data yang salah. Angka adalah angka, teks adalah teks, jangan campur mereka. Tanggal (dan waktu) adalah angka yang memungkinkan dan perlu untuk membuat perhitungan. Jangan bingung 14 Maret 2018 dari 14 Maret 2018. Yang kedua hanya format tanggal khusus [$ -F800], apa yang kita lihat di sel, dan pada bilah rumus akan menjadi 14 Maret 2018. Dan cukup tentang itu.
1. Misalkan Anda memiliki tabel dengan waktu mulai dan berakhirnya beberapa acara, misalnya, percakapan telepon.
Pada gambar, bagian dari tabel _t2. Hal ini diperlukan untuk menghitung jumlah acara simultan maksimum. Untuk ini ada rumus:
=(((_t2[start]>=(_t2[start]))*(_t2[start]<=(_t2[end]));(_t2[start])^0)) =MAX(MMULT((_t2[start]>=TRANSPOSE(_t2[start]))*(_t2[start]<=TRANSPOSE(_t2[end])),ROW(_t2[start])^0))
Rumusnya masif, input dikonfirmasi dengan secara bersamaan menekan tombol Control, Shift, Enter (CSE). Bilah rumus akan menunjukkan bahwa rumus ada dalam kurung kurawal. MUMNOZH membuat matriks virtual ukuran jumlah baris tabel dengan jumlah baris tabel, di baris yang hasil perhitungannya dimasukkan, seolah-olah rumus berikut dimasukkan dalam baris tabel (untuk baris kedua) = SUMPRODUCT (F ((C $ 2: C2> A2))) / / = SUMPRODUCT (N ((C $ 2: C2> A2)))), dan meregangkan seluruh tabel.
2. Sama, tetapi lebih rumit. Selain awal dan akhir, ada jumlah unit atau kekuatan yang diberikan / diterima. Perlu untuk menentukan daya maksimum di beberapa titik waktu.
Bagian dari tabel _t1:
Dan formula itu sendiri:
=((((_t1[start]*1440)<=(1:1436)-1)*((_t1[stop]*1440)>=(1:1436)-1);_t1[power])) =MAX(MMULT((TRANSPOSE(_t1[start]*1440)<=ROW(1:1436)-1)*(TRANSPOSE(_t1[stop]*1440)>=ROW(1:1436)-1),_t1[power]))
Karena waktu adalah sebagian kecil dari unit, kami mengalikan nilai dengan jumlah menit dalam sehari untuk mendapatkan bilangan bulat. Dan argumen kedua adalah GANDA - kolom nilai daya. Dalam kasus pertama, kolom unit dihasilkan.
3. Di sebelah kiri tabel adalah jumlah yang sesuai dengan interval tertentu. Jumlah bulanan harus dihitung. Diasumsikan bahwa jumlah didistribusikan secara merata dalam intervalnya.
Kolom H adalah hari pertama bulan itu, kolom I berisi rumus-rumus berikut:
=(_tis[sum per day]*((((H3;0)>_tis[end];_tis[end];(H3;0))-(H3>_tis[start];H3;_tis[start])+1)^0,5;)^2) =SUM(_tis[sum per day]*IFERROR((IF(EOMONTH(H3,0)>_tis[end],_tis[end],EOMONTH(H3,0))-IF(H3>_tis[start],H3,_tis[start])+1)^0.5,)^2)
Rumusnya sangat besar. Anda dapat menggunakan SUMPRODUCT / SUMPRODUCT, tetapi JIKA KESALAHAN tidak bekerja tanpa input besar. Sayangnya, MAX dan MIN tidak dapat membentuk array nilai virtual. Oleh karena itu, IF digunakan untuk menghitung bagian dari interval per bulan. Karena bagian yang dihasilkan bisa negatif, kesalahan secara artifisial disebabkan (akar kuadrat dari angka negatif mengarah ke sana). Dalam tabel sumber, dimungkinkan untuk membuang kolom durasi interval dan jumlah harian. Formula akan menjadi sedikit lebih lama.
4. Sedikit menyenangkan. Berapa banyak hari Jumat yang bahagia bisa dalam setahun?
Jumlah hari Jumat:
=(((((A:A;B2):(A:A;C2)))=13)*((((A:A;B2):(A:A;C2));2)=5)) =SUMPRODUCT((DAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)))=13)*(WEEKDAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)),2)=5))
Tanggal Jumat:
=((15;6;(($A:$A;$B2):($A:$A;$C2))/(((($A:$A;$B2):(A:A;$C2)))=13)/(((($A:$A;$B2):($A:$A;$C2));2)=5);(A$2));"") =IFERROR(AGGREGATE(15,6,ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2))/(DAY(ROW(INDEX($A:$A,$B2):INDEX(A:A,$C2)))=13)/(WEEKDAY(ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2)),2)=5),COLUMN(A$2)),"")
Sangat bagus (di sini) bahwa baris pertama dari lembar tersebut sesuai dengan 01/01/1901. Oleh karena itu, cukup mudah untuk membentuk serangkaian tanggal tahun ini menggunakan INDEX dan STRING dan hanya menghitung tanggal tiga belas hari Jumat.
5. Ukuran tingkat bunga, tergantung pada jumlahnya, cukup umum. Jika Anda salah menggunakannya, maka jadwal jumlah yang diterima akan seperti bagan merah di bawah ini:
Untuk menghindari ini, Anda perlu menerapkan tarif hanya untuk bagian dari jumlah per interval. Dan untuk interval yang lebih rendah, ambil jumlah yang tetap.
Untuk sumber data seperti itu:
Formula
=(A3;tbl;2)*(A3-(A3;tbl;1))+(((tbl[limit]<=A3)*(tbl[limit]-(tbl[limit];-1;))*(tbl[rate];-1;);)) =VLOOKUP(A3,tbl,2)*(A3-VLOOKUP(A3,tbl,1))+SUMPRODUCT(IFERROR((tbl[limit]<=A3)*(tbl[limit]-OFFSET(tbl[limit],-1,))*OFFSET(tbl[rate],-1,),))
Formulanya masif. Dalam A3, jumlah bunga yang akan dihitung. Data awal –tbl.
Dan akhirnya, batas kredit dengan suku bunga variabel. Formula untuk menghitung jumlah bunga tergantung pada tanggal / jumlah tahapan / pengembalian.
Sejarah kredit:
Ubah tawaran:
=(B1=0;(((_d;_s[[date]:[rate]]);)*(B5:B16)*((_d>(A5:A16))*(_d<=A2);;)*1/(365+(((_d);4)=0)))-(((_d;_s[[date]:[rate]]);)*(D5:D16)*((_d>(C5:C16))*(_d<=A2);;)*1/(365+(((_d);4)=0)));) =IF(B1=0,SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(B5:B16)*INDEX((_d>TRANSPOSE(A5:A16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0)))-SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(D5:D16)*INDEX((_d>TRANSPOSE(C5:C16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0))),)
Formulanya masif. Menghitung bunga berdasarkan hari, memperhitungkan tahun kabisat.
Terima kasih atas perhatian anda!