Pemanfaatan fungsi CONCATENATE dan IF untuk Analisa Tes Pilihan Ganda

koreksi digital

Pada aplikasi pengolah angka Libreoffice Calc ada fungsi CONCATENATE dan fungsi IF. Fungsi CONCATENATE dapat menggabungkan beberapa text hingga 30 text. Adapun fungsi IF dapat memunculkan text sesuai dengan syarat yang kita tentukan. Secara lebih detail dapat dibaca pada menu Help / Libreoffice Help.

CONCATENATE
Combines several text strings into one string.
Syntax
CONCATENATE(“Text1”; …; “Text30”)
Text 1; Text 2; … represent up to 30 text passages which are to be combined into one string.
Example
=CONCATENATE(“Good “;”Morning “;”Mrs. “;”Doe”) returns: Good Morning Mrs. Doe.

IF
Specifies a logical test to be performed.
Syntax
IF(Test; ThenValue; OtherwiseValue)
Test is any value or expression that can be TRUE or FALSE.
ThenValue (optional) is the value that is returned if the logical test is TRUE.
OtherwiseValue (optional) is the value that is returned if the logical test is FALSE.
In the LibreOffice Calc functions, parameters marked as “optional” can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as “optional”, you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
Examples
=IF(A1>5;100;”too small”) If the value in A1 is higher than 5, the value 100 is entered in the current cell; otherwise, the text “too small” (without quotes) is entered.

Fungsi Concatenate dan If dapat kita manfaatkan untuk mengoreksi dan membuat analisa ulangan/tes pilihan ganda.

Penggunaan fungsi IF untuk koreksi tes/ulangan pilihan ganda tentu sudah sering.
Kali ini, saya coba memanfaatkan fungsi Concatenate dan If untuk mengetahui nomor-nomor soal yang belum berhasil dijawab oleh siswa sehingga perlu dipelajari lagi materinya.

Berikut contoh penggunaan fungsi concatenate;
=CONCATENATE(IF(C5=$C$4;””;”1, “);IF(D5=$D$4;””;”2, “);IF(E5=$E$4;””;”3, “);IF(F5=FF$4;””;”4, “);IF(G5=$G$4;””;”5, “);IF(H5=$H$4;””;”6, “);IF(I5=$I$4;””;”7, “);IF(J5=$J$4;””;”8, “);IF(K5=$K$4;””;”9, “);IF(L5=$L$4;””;”10, “);IF(M5=$M$4;””;”11, “);IF(N5=$N$4;””;”12, “);IF(O5=$O$4;””;”13, “);IF(P5=$P$4;””;”14, “);IF(Q5=$Q$4;””;”15, “);IF(R5=$R$4;””;”16, “);IF(S5=$S$4;””;”17, “);IF(T5=$T$4;””;”18, “);IF(U5=$U$4;””;”19, “);IF(V5=$V$4;””;”20, “);IF(W5=$W$4;””;”21, “);IF(X5=$X$4;””;”22, “);IF(Y5=$Y$4;””;”23, “);IF(Z5=$Z$4;””;”24, “);IF(AA5=$AA$4;””;”25, “);IF(AB5=$AB$4;””;”26, “);IF(AC5=$AC$4;””;”27, “);IF(AD5=$AD$4;””;”28, “);IF(AE5=$AE$4;””;”29, “);IF(AF5=$AF$4;””;”30, “))

Keterangan:
C4:AZ4 berisi kunci jawaban
C5:AZ5 berisi jawaban siswa
fungsi IF(C5=$C$4;””;”1, ” berarti; jika isi sel C5 (jawaban siswa) sama dengan C4 (kunci jawaban) maka “” (nihil), dan jika isi sel C5 (jawaban siswa) tidak sama dengan C4 (kunci jawaban) maka akan muncul text “1, ”

Karena fungsi Concatenate hanya mampu menggabungkan 30 text maka,
>pada kolom kedua (BH) saya tulis fungsi untuk menganilisa jawaban soal no 1–30
=CONCATENATE(IF(C5=$C$4;””;”1, “);IF(D5=$D$4;””;”2, “);IF(E5=$E$4;””;”3, “);IF(F5=FF$4;””;”4, “);IF(G5=$G$4;””;”5, “);IF(H5=$H$4;””;”6, “);IF(I5=$I$4;””;”7, “);IF(J5=$J$4;””;”8, “);IF(K5=$K$4;””;”9, “);IF(L5=$L$4;””;”10, “);IF(M5=$M$4;””;”11, “);IF(N5=$N$4;””;”12, “);IF(O5=$O$4;””;”13, “);IF(P5=$P$4;””;”14, “);IF(Q5=$Q$4;””;”15, “);IF(R5=$R$4;””;”16, “);IF(S5=$S$4;””;”17, “);IF(T5=$T$4;””;”18, “);IF(U5=$U$4;””;”19, “);IF(V5=$V$4;””;”20, “);IF(W5=$W$4;””;”21, “);IF(X5=$X$4;””;”22, “);IF(Y5=$Y$4;””;”23, “);IF(Z5=$Z$4;””;”24, “);IF(AA5=$AA$4;””;”25, “);IF(AB5=$AB$4;””;”26, “);IF(AC5=$AC$4;””;”27, “);IF(AD5=$AD$4;””;”28, “);IF(AE5=$AE$4;””;”29, “);IF(AF5=$AF$4;””;”30, “))

bh

>pada kolom ketiga (BI) saya tulis fungsi untuk menganalisa jawaban soal no 31–50
=CONCATENATE(IF(AG5=$AG$4;””;”31, “);IF(AH5=$AH$4;””;”32, “);IF(AI5=$AI$4;””;”33, “);IF(AJ5=$AJ$4;””;”34, “);IF(AK5=$AK$4;””;”35, “);IF(AL5=$AL$4;””;”36, “);IF(AM5=$AM$4;””;”37, “);IF(AN5=$AN$4;””;”38, “);IF(AO5=$AO$4;””;”39, “);IF(AP5=$AP$4;””;”40, “);IF(AQ5=$AQ$4;””;”41, “);IF(AR5=$AR$4;””;”42, “);IF(AS5=$AS$4;””;”43, “);IF(AT5=$AT$4;””;”44, “);IF(AU5=$AU$4;””;”45, “);IF(AV5=$AV$4;””;”46, “);IF(AW5=$AW$4;””;”47, “);IF(AX5=$AX$4;””;”48, “);IF(AY5=$AY$4;””;”49, “);IF(AZ5=$AZ$4;””;”50″); ; ; ; ; )

bi

>pada kolom pertama (BG) saya tulis rumus penggabungan text dengan ampersand (&)
=BH5&BI5

bg

The following two tabs change content below.
Pelayan pelajar di MTs Irsyaduth Thullab masih belajar menulis, salah satunya di kebokata.blogspot.com

Leave a Reply

Your email address will not be published. Required fields are marked *