Câu hỏi Tạo danh sách nội dung ô được phân tách bằng dấu phẩy, trừ khoảng trống


Để nối một số ô nhất định, tôi sẽ sử dụng:

=CONCATENATE(A2,",",C2",",D2,",",F2)

Điều này sẽ làm cho nó để ...

  • A2 = "Matthew"
  • C2 = "Đánh dấu"
  • D2 = "Luke"
  • F2 = "John"

sẽ dẫn đến Matthew,Mark,Luke,John.

Nhưng chúng tôi gặp vấn đề với một cái gì đó như ...

  • A2 = "Chúa Giêsu"
  • C2 = ""
  • D2 = "Mary"
  • F2 = "Joseph"

mà sẽ dẫn đến Jesus,,Mary,Joseph.

Ở đây, dấu phẩy thêm là không mong muốn. Có cách nào để xử lý một cách duyên dáng điều này sao cho tất cả các ô không trống được bao gồm trong danh sách được phân tách bằng dấu phẩy, trong khi tránh việc bổ sung các dấu phẩy không cần thiết khi một số ô trống?

Chắc chắn, điều này có thể được thực hiện với một số công bằng của IF lồng nhau, nhưng tôi thực sự muốn tránh điều đó nếu có thể. Nó có thể được thực hiện với các hàm Excel bản địa, hoặc có lẽ là một công thức mảng? Hoặc một trong những sẽ phải nghỉ mát để VB Script cho một cái gì đó như thế này?


4
2017-09-19 19:24


gốc


Đây là giải pháp VBA, nhưng tôi cũng mong chờ một giải pháp không phải VBA. Câu trả lời được chấp nhận không hoạt động cho ví dụ của bạn vì bạn có thể xem ở đây - nixda


Các câu trả lời:


Như bạn đã nói, IF lồng nhau không phải là tách trà của bạn, nhưng tôi không thể thấy bất kỳ cách nào khác để làm điều này, mà không có giải pháp VBA được chỉ bởi @nixda.

Đối với các giải pháp tôi trình bày, tôi giả sử dữ liệu của bạn giống như sau:

 |    A    |
-+---------+
1| Matthew |
2| Mark    |
3| Luke    |
4| John    |

Tôi đã có một giải pháp đã làm những gì bạn muốn nhưng đã trở thành "chôn". Tôi hiểu nó sau khi đọc các bình luận bạn để lại trong một câu trả lời gần đây (đặc biệt là bình luận về thực tế bạn luôn luôn có ô đầu tiên được điền). Điều này cho phép không tồn tại một tổ, như điều kiện đầu tiên (trong lý luận dưới đây) luôn luôn là sai.

=CONCATENATE(A1;IF(ISBLANK(A2);"";"," & A2);IF(ISBLANK(A3);"";"," & A3);IF(ISBLANK(A4);"";"," & A4))

Bạn luôn có thể thả CONCATENATE và thay thế công thức bằng ký hiệu và như được chỉ ra trong câu trả lời của @Scott. Tại thời điểm này nó chỉ đơn giản là một vấn đề thẩm mỹ.

= A1 & NẾU (ISBLANK (A2); ""; "," & A2) & NẾU (ISBLANK (A3); ""; "," & A3) & NẾU (ISBLANK (A4); ""; "," & A4)

Giá trị đầu tiên luôn được viết, vì vậy điều duy nhất cần thiết là kiểm tra xem hoạt động ô trống và thêm dấu phẩy phía sau nếu không.

Bằng cách này bạn không cần bất kỳ tế bào trợ giúp nào, tất cả chỉ là một hàm duy nhất.

Tôi cũng đã viết một phiên bản phức tạp hơn, bởi vì tôi giả định thuật ngữ chung (tức là có thể có một tình huống mà bạn không điền vào ô đầu tiên của mình). Nó chỉ yêu cầu một tổ 2 cấp, bởi vì có tối đa 3 điều kiện cần được kiểm tra:

  • Có phải các ô trước hoạt động ô trống?
  • hoạt động ô trống?
  • Không có điều nào ở trên có đúng không? (điều kiện mặc định)

Định dạng cần thiết, giả sử bạn bắt đầu lý do ngay từ đầu, như sau:

  • Nếu có thì không đặt dấu phẩy trước dữ liệu ô. Nếu không, hãy tiếp tục.
  • Nếu có thì không đặt bất cứ thứ gì (một giải pháp thay thế sẽ là đặt ô).
  • Nếu không đúng, hãy đặt dấu phẩy phía sau dữ liệu ô.

Như vậy, đây là công thức tôi đã sử dụng. Tôi giả sử tất cả các dữ liệu trong một cột, để sử dụng trong một hàng thay đổi một số công thức và phạm vi.

=CONCATENATE(A1;IF(ROWS(A1:A1)=COUNTBLANK(A1:A1);A2; IF(ISBLANK(A2);"";"," & A2));IF(ROWS(A1:A2)=COUNTBLANK(A1:A2);A3; IF(ISBLANK(A3);"";"," & A3));IF(ROWS(A1:A3)=COUNTBLANK(A1:A3);A4; IF(ISBLANK(A4);"";"," & A4)))

ROWS đếm số hàng trong bất kỳ khoảng thời gian cụ thể nào. Nếu nó bằng số ô trống phía sau hoạt độngtế bào, sau đó nó có nghĩa là tất cả các tế bào đằng sau hoạt động ô trống, vì vậy không có dấu phẩy nào được đặt trước đó.

Khoảng thời gian trong ROWS và COUNTBLANK là những tế bào phía sau các hoạt động ô.


4
2017-09-19 22:21



Phiên bản Excel của Bồ Đào Nha có sử dụng dấu chấm phẩy để tách các đối số chức năng không? Nó thường là dấu phẩy cho tiếng Anh. - Iszi
Tùy chọn vùng (trong Windows) ở các quốc gia nói tiếng Bồ Đào Nha làm cho Excel sử dụng dấu chấm phẩy làm dấu tách đối số (vì chúng tôi sử dụng dấu phẩy cho dấu thập phân). Nếu nó làm phiền bạn tôi có thể chỉnh sửa câu trả lời của tôi. - Doktoro Reichard


Trong Excel 2016, bạn có thể sử dụng TEXTJOIN:

=TEXTJOIN(delimiter,ignore_blanks?,values_to_be_joined)

Vì vậy, trong trường hợp của bạn, bạn sẽ sử dụng công thức này:

=TEXTJOIN(",",TRUE,A2,C2,D2,F2)

3
2018-04-21 13:07



Trong khi điều này có thể trả lời câu hỏi, hãy xem xét thêm giải thích để làm cho nó dễ hiểu hơn. Điều này thực sự trả lời câu hỏi. - Vylix
Đây là câu trả lời tốt nhất và hiệu quả nhất - Vylix


Nếu bạn sẵn sàng sử dụng một số ô "trợ giúp" cho các giá trị trung gian, bạn có thể thích điều này:

Đặt ô AA2 (hoặc là Sheet2!A2, hoặc bất cứ nơi nào bạn muốn đặt nó)

=IF(A2="", "", A2&",")

(Ở đâu x & y chỉ là một cách nói ngắn hơn CONCATENATE(x, y)) và đặt ô AC2, AD2AF2 tương tự. Bộ AZ2 đến

=AA2 & AC2 & AD2 & AF2

Sau đó, kết quả cuối cùng của bạn là

=IF(AZ2="", "", LEFT(AZ2, LEN(AZ2)-1))

Giải trình:

Các AA- -AF các ô thêm dấu phẩy vào các giá trị không trống. Vì vậy, sử dụng ví dụ của bạn,

Matthew         Mark            Luke            John

sẽ dẫn đến

Matthew,        Mark,           Luke,           John,

trong khi

Jesus                           Mary            Joseph

sẽ dẫn đến

Jesus,                          Mary,           Joseph,

(lưu ý thiếu dấu phẩy trong Cột C).

AZ2 là một kết nối đơn giản của các bên trên: Jesus,Mary,Joseph,, vì vậy chúng tôi đã loại bỏ dấu phẩy thừa sau Jesus nhưng thêm một ở cuối. LEFT(AZ2, LEN(AZ2)-1) là tất cả AZ2 ngoại trừ ký tự cuối cùng. Và chúng ta cần kiểm tra xem AZ2 là null để tránh bị lỗi nếu tất cả bốn ô đầu vào đều trống.


2
2017-09-20 01:14



Điều tốt đẹp về "sử dụng một số" tế bào trợ giúp "giải pháp là thường là các công thức cho 'tế bào trợ giúp' có thể được viết ngay vào công thức cho ô mục tiêu. Tôi lấy ý tưởng của bạn và tích hợp tất cả vào một công thức. =CONCATENATE(S2,IF(U2="","",", "&U2),IF(V2="","",", "&V2),IF(W2="","",", "&W2)) Một điều tôi quên đề cập đến là ô đầu tiên trong danh sách của tôi (S2 trong ví dụ này) sẽ là bắt buộc. Vì vậy, bằng cách chấp nhận rằng ô đầu tiên sẽ luôn luôn được lấp đầy, chúng ta có thể di chuyển dấu phẩy đến trước mỗi chuỗi tiếp theo và tránh có dấu phẩy dư thừa ở cuối. - Iszi
Lưu ý: Mặc dù giải pháp này vẫn yêu cầu các câu lệnh IF, xin lưu ý rằng mục đích ban đầu của tôi là tránh lồng nhau NẾU Ở đây, các IF không cần lồng ghép, và không có sự lặp lại thực sự có liên quan - chỉ có một câu lệnh IF cho mỗi ô cần được ghép nối. Vì vậy, trừ khi có một giải pháp tốt hơn liên quan đến một số loại vòng lặp hoặc mảng, điều này chắc chắn là một câu trả lời chấp nhận được. - Iszi


Tôi đoán bạn đang tìm kiếm một giải pháp đơn giản và không phải để cải thiện kỹ năng của bạn.

Bạn có thể sử dụng bổ trợ nếu được phép (nghĩa là bạn có quyền cài đặt bổ trợ). ASAP Tiện ích là thứ tôi khuyên bạn nên sử dụng miễn phí cho mục đích phi thương mại.

Dưới đây là những gì bạn có thể làm với một trong các chức năng của họ

Tiện ích này kết hợp dữ liệu từ các cột trong lựa chọn của bạn. Đối với mỗi hàng trong lựa chọn của bạn, dữ liệu từ các cột liền kề sẽ   được nối vào ô đầu tiên trong hàng.

Bạn có thể chỉ định các ký tự sau: Dấu phân cách để đặt giữa ô   giá trị Bỏ qua ô trống Sử dụng giá trị, công thức hoặc giá trị được định dạng từ   các tế bào.

Screenshot


2
2017-10-12 16:02





Nếu bạn muốn có một giải pháp chung, câu trả lời tốt nhất sẽ liên quan đến VBA.

Nếu dữ liệu ví dụ của bạn gần như hoàn toàn là vấn đề bạn đang giải quyết, bạn có thể chỉ cần làm như sau để xóa dấu phẩy kép:

=SUBSTITUTE(CONCATENATE(A2,",",C2",",D2,",",F2),",,",",")

Ở trên sẽ chỉ hoạt động nếu bạn chỉ có khoảng trống một giá trị rộng. Bạn sẽ cần các công thức thay thế lồng nhau () để xử lý các khoảng trống lớn hơn.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(A2,",",C2,",",D2,",",F2),",,,,,,,,,,,,,,,,",","),",,,,,,,,",","),",,,,",","),",,,",","),",,",",")

Ở trên sẽ giảm tới 398 dấu phẩy tuần tự. Nó cũng sẽ giảm rất nhiều giá trị trên 399, nhưng 399 chính nó và một số khác sẽ dẫn đến nhiều hơn một dấu phẩy.


1
2017-09-20 17:55



Công thức cuối cùng là một tổ lớn ... và nếu sau là đúng, bạn đang thay thế 8 dấu phẩy thành 1, sau đó 4 đến 1 rồi 3 đến 1, sau đó 2 đến một. Tôi hiểu lý do đằng sau 3 (đó là số lẻ tiếp theo sau 1) Không nhận được nơi bạn sẽ giảm 398 dấu phẩy tiềm năng, trừ khi SUBSTITUTE tìm kiếm tất cả các kết quả phù hợp riêng biệt bên trong chuỗi. - Doktoro Reichard
SUBSTITUTE thay thế tất cả các kết quả phù hợp. 398 (24 * 16 + 14) dấu phẩy được giảm xuống còn 38 dấu phẩy, 38 (4 * 8 + 6) thành 10, 10 (2 * 4 + 2) thành 4, 4 (1 * 3 + 1) thành 2 và 2 đến 1. - Dane
Eww. Các hàm SUBSTITUTE lồng nhau thực sự không tốt hơn nhiều so với các IF lồng nhau. - Iszi
@iszi, bạn đang xử lý bao nhiêu ô trống? Làm tổ chỉ được yêu cầu nếu bạn muốn xử lý rất nhiều. Nếu bạn không muốn lồng nhau bất cứ điều gì, bạn sẽ cần một công thức do người dùng xác định. - Dane
@ Dane Tôi không cố gắng tránh "lồng nhau bất cứ điều gì", nhưng tôi muốn tránh một số lượng lớn của nhiều lớp làm tổ đặc biệt là khi làm tổ trong cùng một chức năng. Trang tính mà tôi đang làm việc với ngay bây giờ chỉ có 4 ô cần được ghép nối. Ô đầu tiên là bắt buộc - vì vậy nếu trống thì có thứ cần sửa chữa để bắt đầu - nhưng ba ô còn lại có thể được lấp đầy hoặc để trống. - Iszi


Tôi sẽ giả sử dữ liệu của bạn có tiêu đề và bắt đầu bằng A2.

  1. Đi đến bất kỳ cột dự phòng nào, tôi sẽ sử dụng B trong ví dụ này
  2. Trong ô B2 nhập đơn giản = A2
  3. Trong ô B3, hãy nhập = IF (A3 = "", B2, CONCATENATE (B2, ",", A3)
  4. Sao chép cột B từ B3 (không phải B2!) Vào hàng cuối cùng chứa dữ liệu trong Cột A

Thì đấy, danh sách được phân tách bằng dấu phẩy của bạn nằm trong ô cuối cùng trong Cột B :)

Tôi đến đây tìm kiếm một giải pháp, không tìm thấy giải pháp nào và do đó đã đưa ra các giải pháp trên. Dữ liệu của tôi có 1000 hàng, do đó việc thêm thủ công vào liên kết không phải là một tùy chọn.


1
2017-10-08 12:54



Đến nay là giải pháp đơn giản nhất. Cảm ơn :) - Karlth


Chỉ cần kết hợp Concatenate () với if () trong một hàm mảng:

=arrayformula(concatenate(if(not(isempty(a2:f2)),a2:f2&", ","")))

Hàm này sẽ để lại dấu phẩy ở cuối danh sách của bạn, vì vậy nếu bạn cần tránh điều đó và bạn biết chính xác nơi kết thúc danh sách là (trong ví dụ của bạn, cột f) chỉ xử lý một dấu phẩy:

=arrayformula(concatenate(if(not(isempty(a2:d2)),a2:d2&", ","")))&if(not(isempty(f2)),f2,"")

Chúc mừng!


0
2017-09-30 05:53



công thức đầu tiên cho #TÊN? lỗi - Prasanna
Đây dường như là công thức Bảng tính Google. Các công thức mảng được xác định bởi mục nhập phím tắt trong Excel, không phải bởi một hàm trang tính. - Excellll