Ubah urutan kolom dengan duplikat menjadi kolom unik

Saya ingin tahu apakah mungkin untuk mengubah urutan kolom dengan bidang duplikat yang dikelompokkan, seperti misalnya:

+---------+------+
| field_1 | 1    |
| field_2 | 2    |
| field_3 | 3    |
| field_4 | 4    |
| field_1 | 5    |
| field_2 | 6    |
| field_3 | 7    |
| field_4 | 8    |
| field_1 | 9    |
| field_2 | 10   |
| field_3 | 11   |
| field_4 | 12   |
+---------+------+

ke kolom unik, seperti ini:

+---------+---------+---------+---------+
| field_1 | field_2 | field_3 | field_4 |
+---------+---------+---------+---------+
| 1       | 2       | 3       | 4       |
| 5       | 6       | 7       | 8       |
| 9       | 10      | 11      | 12      |
+---------+---------+---------+---------+

Apakah ini mungkin tanpa skrip?


excel excel-formula
person vedar    schedule 25.05.2016    source sumber
comment
Tanpa skrip (misalnya menggunakan vba) pertanyaan Anda mungkin lebih baik ditanyakan di situs saudara superuser.com.   -  person surfmuggle    schedule 26.05.2016
comment
@slackmuggle cukup umum untuk melakukan sesuatu di excel tanpa skrip. Ada seluruh tag untuk itu excel-formula. Yang mana yang harus ditambahkan.   -  person Forward Ed    schedule 26.05.2016
comment
@ForwardEd ini mungkin terjadi tetapi pemahaman saya adalah bahwa ini akan lebih cocok di superuser.com.   -  person surfmuggle    schedule 26.05.2016

Jawaban (3)


arrow_upward
5
arrow_downward

Masukkan rumus di bawah ini pada E3 (menggunakan Ctrl+Shift+Enter) lalu isi ke bawah lalu ke seberang.

=OFFSET($C$1,LARGE((ROW($B$2:$B$13)-1)*($B$2:$B$13=E$2),ROW()-2),0) & ""

masukkan deskripsi gambar di sini

Nilai tidak berada dalam urutan yang tercantum tetapi setidaknya berada di kolom yang benar.

Akan mencoba menjelaskan:

  • Semua nilai yang kita inginkan untuk masing-masing nilai "bidang" yang berbeda diimbangi oleh sejumlah baris "x" dari C1

  • Mengambil "field_1" sebagai contoh (ROW($B$2:$B$13)-1)*($B$2:$B$13=E$2) akan mengembalikan array nomor baris dikalikan dengan 1 (TRUE) atau nol (FALSE) tergantung pada apakah nilai sel "B" cocok dengan "field_1" (dari E2). -1 adalah penyesuaian karena kita memulai pada baris 2. Array yang dikembalikan terlihat seperti:

    [1,0,0,0,5,0,0,0,9,0,0,0]

  • Mengingat array tersebut, kita perlu menemukan cara untuk mendapatkan nilai bukan nol satu per satu: kita dapat menggunakan LARGE() untuk melakukannya, menambahkan argumen kedua ke LARGE menggunakan ROW()-2 (-2 karena kita ingin argumen kedua tersebut dimulai dari 1 dan rumus dimasukkan pada baris 3). Ini memberi kita susunan nilai yang terurut:

    [9,5,1,0,0,0,0,0,0,0,0,0]

  • Kita meneruskan larik terurut ini sebagai argumen "offset baris" ke OFFSET(), menghitung mundur dari C2: semua nilai bukan nol memberi kita nilai yang kita inginkan dari Kolom C, sedangkan semua nilai nol hanya mengembalikan C2 (yang kosong, dan akan ditampilkan sebagai 0 tanpa & "" terakhir

  • Semua referensi sel dalam rumus menggunakan $ jika diperlukan sehingga menyesuaikan sesuai kebutuhan saat menyeret untuk mengisi ke bawah/melintasi

person Tim Williams    schedule 26.05.2016
comment
Sialan kau menjawab saat aku sedang membereskannya! Pilihan yang bagus! Saya melakukan hal yang sama seperti yang Anda lakukan dalam hal urutan tampilan saat pertama kali dijalankan. Saya rasa jika Anda menggunakan KECIL dan bukan BESAR, Anda akan mempertahankan urutan vertikal kemunculan informasi. Dengan besar Anda hanya bekerja dari bawah ke atas. - person Forward Ed; 26.05.2016
comment
DOH Saya lupa Anda akan mendapatkan semua angka 0 palsu itu dengan angka kecil. Mengalami masalah itu kemarin! Bisakah Anda menambahkan COUNT($B$2:$B$13<>E$2) ke ROW()-2 untuk melewati semua angka 0 jika Anda menggunakan rumus versi KECIL? - person Forward Ed; 26.05.2016
comment
@ForwardEd - Saya memang mempertimbangkan untuk mengganti angka nol tetapi itu hanya menambah kerumitan yang menurut saya dapat dilakukan tanpa... - person Tim Williams; 26.05.2016
comment
Saya tahu saya membutuhkan row entah bagaimana tetapi tidak dapat membuatnya berfungsi :/ Dapatkah Anda memposting rumus sebagai teks, bukan gambar sehingga saya dapat menyalin/menempelkannya? - person findwindow; 26.05.2016
comment
Awwwwwwwwww kamu tidak perlu ‹333 (aku hanya bercanda XD) - person findwindow; 26.05.2016
comment
Ahahahah bagus. Saya setengah jalan mempelajarinya sekarang^^ Edit: Anda tidak menjelaskan mengapa data perlu dimulai pada B2 tapi saya menemukan jawabannya =P Edit2: Saya tahu SANGAT ingin jawaban dijelaskan tetapi saya tidak. Lebih baik mempelajarinya sendiri ^_^; - person findwindow; 26.05.2016
comment
Data dimulai di B2 karena disitulah letak datanya? - person Tim Williams; 26.05.2016
comment
Oh, Anda termasuk perbatasan XD Sebenarnya, saya menyadari Anda cukup menghapus/mengubah perhitungan matematika tergantung pada lokasi ^_^; - person findwindow; 26.05.2016

arrow_upward
1
arrow_downward

Ini hanya membawa Anda sejauh ini. Anda masih perlu menghapus duplikat. Dalam D2:

=INDEX($B1:$B$12,MATCH(D$1,$A1:$A$12,0))

masukkan deskripsi gambar di sini

person findwindow    schedule 25.05.2016
comment
Tidak dapat membuat @scottcraner kecemerlangan berfungsi XD Sunting: Saya rasa saya perlu sumproduct oh baiklah, waktunya pulang. - person findwindow; 26.05.2016
comment
Saya mengakhiri dengan menggunakan offset dari B1 ke B4: =OFFSET($B1;(ROW()-1)*3;0), lalu isi otomatis ke bawah - person vedar; 26.05.2016
comment
@findwindow Dan di sini saya akan memberi Anda +1 untuk formula yang ringkas dan efisien! - person Forward Ed; 26.05.2016
comment
@findwindow Karena Anda mengatakan bahwa Anda kesulitan memahami SUMproduct beberapa hari yang lalu, saya pikir Anda mungkin ingin membaca separuh jawaban terakhir diposting di sini. - person Forward Ed; 26.05.2016
comment
Bung, apa yang kami katakan tentang tangkapan layar/kata-kata yang tidak cukup XD - person findwindow; 26.05.2016

arrow_upward
0
arrow_downward

Jawaban saya sangat mirip dengan jawaban Tim tetapi menghindari bagian CSE dan menginternalisasikannya dalam fungsi AGGREGATE. Juga percaya itu akan mempertahankan urutan vertikal di mana elemen di B muncul (meskipun tidak diuji).

Di D1 gunakan rumus berikut dan salin ke kanan untuk mendapatkan daftar header unik Anda.

=INDEX($A$1:$A$12,MATCH(0,INDEX(COUNTIF($C1:C1,$A$1:$A$12),0,0),0))

Kemudian di D2 gunakan yang berikut ini untuk menarik informasi Anda dari kolom B. Dapat disalin ke kanan dan bawah. Jika suatu item tidak ditemukan maka akan ditampilkan tidak ditemukan. Anda dapat mengubahnya menjadi "" jika Anda mau.

=IFERROR(INDEX($B$1:$B$12,AGGREGATE(15,6,ROW($A$1:$A$12)/($A$1:$A$12=D$1),ROW(1:1))),"Not found")

Bukti dari konsep

Bukti konsep

Catatan tentang OFFSET

OFFSET adalah fungsi yang mudah menguap yang berarti ia akan menghitung ulang kapan pun apa pun di lembar kerja berubah, bukan hanya sel tempat ia bekerja. Jadi, jika Anda hanya menggunakannya beberapa kali, bukan masalah besar. Namun jika Anda menggunakannya berulang kali, Anda mungkin mengalami perlambatan pada lembar kerja Anda.

person Forward Ed    schedule 26.05.2016