Bagaimana cara mendapatkan daftar nilai yang tidak bersebelahan dari sebuah baris di Excel (mengabaikan yang kosong)?

Saya sedang merestrukturisasi beberapa kumpulan data dan perlu mengambil daftar nilai yang tidak bersebelahan dalam satu baris di Excel untuk Mac. Ada sel dengan spasi kosong yang harus dibuang, sehingga hasilnya adalah daftar horizontal dengan nilai yang sama, tanpa sel kosong di antaranya.

Nilai muncul dalam blok yang terdiri dari 6 sel yang berdekatan dan diselingi dengan jumlah sel kosong yang bervariasi (kelipatan 6). Data bersifat numerik, jadi >0 berhasil. Saya telah mencoba mengadaptasi solusi yang ditemukan online, seperti yang ini di sini, namun tidak berhasil.

Saya harus melakukan hal serupa dengan mengambil nilai kemunculan 1 hingga 10 secara terpisah, dan dapat melakukannya setelah membuang hloookup, mempelajari rumus indeks+pencocokan dan array, dan menyesuaikan solusi yang ditemukan online untuk masalah serupa dengan kolom, seperti ini.

Beginilah solusi saya untuk mengambil kejadian ke-2 berturut-turut:

=IFERROR(INDEX($FR5:$GT5,SMALL(IF($FR5:$GT5>0,COLUMN($FR5:$GT5)-COLUMN($FR5)+1,FALSE),2)),"9999" )

di mana $FR5:$GT5 adalah rentang yang saya perlukan untuk mengambil nilai, dan 9999 adalah kode saya untuk nilai yang hilang. Saya pikir saya akan melemparkannya ke sana, seseorang dengan keterampilan terbatas seperti saya mungkin akan merasakan manfaatnya.

Adakah tip untuk membantu saya melanjutkan? Sebaiknya, saya ingin mengadaptasi rumus saya sebelumnya untuk mengatasi masalah ini. Saya mencoba tetapi tidak dapat menghilangkan sel kosong. Aku terjebak! Terima kasih sebelumnya.


array-formulas excel-formula excel-2011
person user5225119    schedule 13.08.2015    source sumber
comment
Terima kasih, itu benar-benar berhasil - kecuali saya harus melakukan ini untuk beberapa kumpulan data, beberapa kali di masing-masing kumpulan data, untuk berbagai rentang dan jumlah nilai yang akan diambil. Jadi saya harus mengandalkan rumus yang dapat saya periksa ulang, dll. Saya bahkan memiliki jumlah/countif dalam rentang asal dan takdir saya sebagai pemeriksaan keamanan bahwa semua nilai diambil dengan benar! Bagaimanapun, terima kasih telah menambahkan trik Excel saya!   -  person user5225119    schedule 16.08.2015

Jawaban (1)


arrow_upward
1
arrow_downward

Tidak sepenuhnya jelas apakah Anda ingin menampilkan nilai yang disusun (tanpa kosong) dalam satu baris atau kolom, tetapi saya menduga dari kombinasi referensi sel baris/kolom absolut yang ingin Anda mulai dalam sel dan mengisinya dengan benar .

Rumus Anda hampir mengumpulkan urutan angka sambil mengabaikan angka yang kosong dan kurang dari atau sama dengan nol. Anda hanya perlu menyesuaikan parameter k dari Fungsi KECIL (2 dalam contoh rumus Anda di atas).

Jika Anda memasukkan =COLUMN(A:A) ke dalam sel dan mengisinya dengan benar, Anda akan menerima urutan bilangan bulat seperti 1, 2, 3, 4, dst. Demikian pula, jika Anda memasukkan =ROW(1:1) ke dalam sel dan mengisinya, Anda akan menerima lagi 1, 2, 3, 4, dst. Salah satu sub-rumus ini dapat digunakan untuk meningkatkan k SMALL Anda bergantung pada apakah Anda ingin mengisi rumus dengan benar untuk menerima nilai yang dikembalikan dalam satu baris atau mengisi hingga menerima nilai yang dikembalikan dalam satu kolom. Dengan cara ini, mengisi ke kanan atau ke bawah akan memberi Anda pengembalian kedua, ketiga, dan seterusnya.

Untuk menerima nilai yang dikembalikan secara berturut-turut, gunakan rumus array ini,

=IFERROR(INDEX(5:5, SMALL(IF($FR5:$GT5>0, COLUMN($FR:$GT)), COLUMN(A:A))),"9999")

Isi tepat seperlunya untuk menangkap semua nilai. Untuk menerima nilai yang dikembalikan dalam kolom, gunakan rumus array ini,

=IFERROR(INDEX($5:$5, SMALL(IF(FR$5:GT$5>0, COLUMN(FR:GT)), ROW(1:1))),"9999")

Isi seperlunya untuk menangkap semua nilai.

Rumus array perlu diselesaikan dengan Ctrl+Shift+Enter↵. Setelah dimasukkan ke dalam sel pertama dengan benar, sel tersebut dapat diisi atau disalin ke bawah atau ke kanan sama seperti rumus lainnya.

Saya tidak pernah menyukai akrobatik matematika (misalnya COLUMN($FR5:$GT5)-COLUMN($FR5)+1) yang digunakan rumus asli Anda untuk menentukan kolom (atau baris) dalam gaya rumus tersebut. Saya telah mengupasnya secara substansial dalam rumus-rumus setara di atas tetapi jumlahnya sama.

person Community    schedule 15.08.2015
comment
Terima kasih! Itu bekerja dengan sangat baik. Saya sangat menghargai Anda meluangkan waktu untuk menjelaskan mengapa ini berhasil juga :-) Saya curiga solusinya sudah cukup jelas...haha. Ini juga membantu untuk memiliki pendekatan yang lebih elegan terhadap solusi ke-n saya. - person user5225119; 16.08.2015