Xin chào, Tôi mới sử dụng Microsoft Excel và có một câu hỏi. Tôi đã thiết lập một bảng tính để theo dõi doanh số tôi kiếm được tại doanh nghiệp của mình. Trên cùng là doanh thu hàng năm của tôi cho năm 2013. Dưới đây là các biểu đồ cho từng tháng riêng lẻ. Tôi muốn có thể nhập thủ công doanh số hàng tháng của mình cho mỗi tháng và doanh số bán hàng năm tự động được thêm vào ở trên cùng. Show
Tôi biết tôi có thể làm điều này bằng tay. Ngay bây giờ tôi chỉ thiết lập nó cho tháng 1 và, ví dụ, công thức cho C4 =C67. Tôi tự hỏi liệu có cách nào dễ dàng hơn để áp dụng điều này hơn là đi từng ô và nhập D4 =D67, E4 =E67, F4 =F67, C5 =C68, v.v. Điều này sẽ đặc biệt quan trọng trong những tháng tới vì tôi muốn một cách nhanh chóng sửa đổi công thức để bao gồm Tháng Hai, Tháng Ba, Tháng Tư, v.v. Tôi hy vọng tôi đã giải thích điều này đủ rõ ràng. Tôi không thể đính kèm hình ảnh để làm rõ do là người dùng mới (tôi đoán là các biện pháp chống thư rác). Tôi sẽ đăng hình ảnh trong một bài tiếp theo nếu tôi có thể. Trong ví dụ được hiển thị, chúng tôi muốn kiểm tra xem tất cả các giá trị trong mỗi hàng có bằng nhau không. Để làm điều này, chúng tôi sử dụng một biểu thức so sánh giá trị trong cột đầu tiên (B5) với các cột còn lại (C5. F5) Bởi vì chúng tôi đang so sánh một giá trị ô với các giá trị trong bốn ô khác, kết quả là một mảng có bốn giá trị TRUE hoặc FALSE. Ở hàng 5, tất cả các giá trị đều bằng nhau, vì vậy tất cả các giá trị đều là TRUE Mảng này được trả về trực tiếp cho hàm AND, hàm này trả về TRUE, vì tất cả các giá trị trong mảng đều là TRUE Trong ô H6, B6=C6. F6 tạo một mảng có hai giá trị FALSE, vì D6 và F6 khác nhau Mảng này được chuyển đến hàm AND, trả về FALSE đếm sự khác biệtCông thức trong I5 sử dụng hàm COUNTIF để đếm sự khác biệt trong mỗi hàng như thế này Tiêu chí được cung cấp là "<>"&B5, có nghĩa là "không bằng B5" Bạn có thể điều chỉnh công thức để bắt chước hoạt động của công thức AND ở trên như thế này Ở đây, chúng ta chỉ cần so sánh kết quả từ COUNTIF với 0. Số 0 trả về TRUE và bất kỳ số nào khác trả về FALSE Có nhiều cách khác nhau để kết hợp các cột trong Excel và tôi sẽ chỉ cho bạn 5 công thức khác nhau mà bạn có thể sử dụng để kết hợp nhiều cột thành một. Ba trong số các công thức này sẽ kết hợp các cột theo chiều ngang và hai trong số chúng sẽ kết hợp các cột theo chiều dọc Ngay bên dưới là các hướng dẫn nhanh để kết hợp các cột, nhưng cũng có thể xem thêm bên dưới để biết các ví dụ chi tiết cũng như các cách kết hợp cột bổ sung Để kết hợp các cột theo chiều ngang trong Excel, hãy làm theo các bước sau
(Xem hướng dẫn chi tiết bên dưới để biết cách thêm dấu cách hoặc ký tự đặc biệt giữa các cột) Để kết hợp các cột theo chiều dọc trong Excel, hãy làm theo các bước sau
Dưới đây là các công thức sẽ kết hợp các cột trong ExcelCông thức kết hợp cột ngang
Công thức kết hợp cột dọc
Bài học này sẽ hướng dẫn bạn cách kết hợp các cột trong Excel, nhưng hãy xem bài viết này nếu bạn muốn tìm hiểu cách kết hợp các cột trong Google Trang tính Sử dụng mảng trong ExcelLưu ý rằng một số công thức chúng ta đang sử dụng trong bài học này sử dụng cái được gọi là "mảng". Một công thức mảng được nhập vào một ô duy nhất nhưng áp dụng công thức cho một dải ô. Nếu bạn đang sử dụng phiên bản Excel cũ hơn, khi sử dụng "mảng", bạn sẽ cần giữ "Ctrl" và "Shift" trên bàn phím, sau đó nhấn "Enter" khi nhập công thức mảng của mình. Đây được gọi là công thức CSE (Control / Shift / Enter) và đôi khi còn được gọi là mảng "Di sản", trái ngược với các mảng "Động" mới hơn không yêu cầu bước bổ sung này Sau khi thực hiện phương thức Ctrl/Shift/Enter, công thức của bạn sẽ được bao bọc trong dấu ngoặc nhọn { }. Lưu ý rằng hai công thức dưới đây sẽ thực hiện cùng một tác vụ, nhưng đơn giản là được nhập khác nhau, công thức dưới cùng là công thức "CSE" =A3. A10&B3. B10 {=A3. A10&B3. B10} Kết hợp các cột trong Excel (Ngang)Trước tiên tôi sẽ chỉ cho bạn cách kết hợp các cột trong Excel theo chiều ngang Nếu bạn có hai cột mà bạn muốn kết hợp nội dung của chúng, trong đó giá trị của các ô trong mỗi hàng sẽ được kết hợp với nhau theo chiều ngang, thì có một số cách đơn giản để thực hiện việc này
Cả hai phương pháp này đều có thể được sử dụng để kết hợp các ô riêng lẻ mà không cần phải sử dụng hàm ARRAYFORMULA Sử dụng toán tử AND/dấu và (&) để kết hợp các cộtTrước tiên, tôi sẽ chỉ cho bạn cách phổ biến và chức năng nhất để kết hợp các cột theo chiều ngang trong Excel. Trong ví dụ này, chúng tôi sẽ kết hợp họ và tên vào một cột duy nhất Bằng cách sử dụng toán tử "&", bạn sẽ có thể kết hợp nhiều cột trong Excel và bạn cũng có thể chỉ định các giá trị và chuỗi văn bản mà bạn muốn đính kèm vào tổ hợp cột Để kết hợp dữ liệu từ các ô riêng lẻ trong Excel (chẳng hạn như họ và tên), hãy làm theo các bước sau
Trong ví dụ đầu tiên này, chúng tôi có một danh sách tên trong một cột và họ trong một cột khác… và chúng tôi muốn kết hợp các cột này theo chiều ngang để trong mỗi hàng, tên được kết hợp đầy đủ được hiển thị dưới dạng một chuỗi văn bản, trong một Chúng tôi cũng sẽ đặt một khoảng cách giữa các nội dung được kết hợp trong ví dụ này. Điều này được thực hiện bằng cách sử dụng dấu ngoặc kép với khoảng cách giữa chúng tôi. e. ("") trong công thức mảng (hiển thị bên dưới) Nhiệm vụ. Kết hợp theo chiều ngang cột "tên" với cột "kích thước" và đặt khoảng cách giữa các nội dung.. để tên đầy đủ được hiển thị trong một cột (Trường hợp nội dung của các ô trong mỗi hàng được kết hợp thành một chuỗi văn bản duy nhất được phân tách bằng dấu cách) Hợp lý. Kết hợp theo chiều ngang nội dung của các ô trong phạm vi A3. A12, với nội dung của các ô từ phạm vi B3. B12 và thêm khoảng trắng giữa văn bản/nội dung từ các cột được kết hợp công thức. Công thức dưới đây, được nhập vào ô màu xanh lam (D3), ví dụ này =A3. A12&" "&B3. B12 {=A3. A12&" "&B3. B12} (Phiên bản cũ / CSE)  Nhớ giữ Ctrl + Shift + Enter để lập công thức mảng, với các phiên bản Excel cũ Kết hợp nhiều hơn 2 cột theo chiều ngang trong ExcelNếu bạn muốn kết hợp nhiều hơn 2 cột theo chiều ngang trong Excel, bạn có thể thực hiện việc này bằng toán tử "&", còn được gọi là "dấu và" Ví dụ: nếu bạn muốn kết hợp các cột A, B và C theo chiều ngang (có khoảng trắng ở giữa), thì bạn có thể sử dụng công thức bên dưới =A3. A12&" "&B3. B12&" "&C3. C12 Sử dụng CONCAT hoặc CONCATENATE để hợp nhất các cột trong ExcelTrong ví dụ này, chúng ta sẽ làm điều tương tự như trong ví dụ trên, nhưng chúng ta sẽ sử dụng dữ liệu khác. Chúng tôi có một danh sách các mặt hàng tồn kho quần áo trong một cột và kích cỡ quần áo cho mặt hàng đó được liệt kê trong một cột khác… và chúng tôi muốn kết hợp các cột này theo chiều ngang để trong mỗi hàng, mặt hàng quần áo và kích cỡ được hiển thị dưới dạng một chuỗi văn bản, Chúng ta sẽ sử dụng hàm CONCATENATE, tương thích với mảng, giúp dễ dàng kết hợp toàn bộ cột với một công thức duy nhất Tuy nhiên, lưu ý rằng bạn cũng có thể sử dụng hàm CONCAT để kết hợp các ô và thậm chí đặt dấu cách/ký tự giữa các cột đó, giống như chúng ta đã làm với ký hiệu "&"… nhưng hàm CONCAT không hoạt động với mảng và vì vậy nếu bạn sử dụng Để kết hợp dữ liệu từ các ô bằng cách sử dụng CONCATENATE trong Excel, hãy làm theo các bước sau
Để kết hợp dữ liệu từ các ô với công thức CONCAT trong Excel, hãy làm theo các bước sau
Xem ví dụ/hướng dẫn ở trên để biết cách thêm dấu cách/ký tự đặc biệt giữa các cột Nhiệm vụ. Kết hợp theo chiều ngang cột hiển thị các mặt hàng quần áo với cột hiển thị kích cỡ quần áo… để mặt hàng quần áo và kích cỡ được hiển thị trong một cột (Trường hợp nội dung của các ô trong mỗi hàng được kết hợp thành một chuỗi văn bản) Hợp lý. Kết hợp phạm vi A3. B, và phạm vi B3. B thành một cột, sao cho nội dung từ các ô được hợp nhất theo chiều ngang thành một chuỗi văn bản công thức. Công thức dưới đây, được nhập vào ô màu xanh lam (D3), ví dụ này =CONCATENATE(A3. A12," ",B3. B12) {=CONCATENATE(A3. A12," ",B3. B12)} (Phiên bản cũ / CSE) Các công thức ở trên là phiên bản mảng, nhưng trong hình ảnh, bạn có thể thấy phiên bản cơ bản của công thức với một công thức trên mỗi ô Lưu ý rằng hàm CONCAT (Hiển thị bên dưới) thực hiện tương tự như hàm CONCATENATE (Hiển thị bên trên) =CONCAT(A3," ",B3) Kết hợp các cột trong Excel (Dọc)Bây giờ mình sẽ hướng dẫn các bạn cách gộp cột trong Excel theo chiều dọc hay nói cách khác là "chồng cột" Có 2 cách khác nhau để kết hợp các cột trong Excel theo chiều dọc bằng cách sử dụng công thức, tùy thuộc vào cách bạn muốn công thức hoạt động
Công thức gộp (xếp chồng) cột trong Excel =IF(A3<>"",A3,INDIRECT("B"&ROW()-COUNTIF(A$3. 99 đô la Úc,"<>"))) Công thức gộp cột, đồng thời luân phiên qua lại giữa các cột trong Excel =INDEX($A$2. $B$99,ROW()/2,MOD(ROW(),2)+1) * Lưu ý cả 2 công thức này đều phải copy/điền vào các ô cho đầy đủ cột (Mỗi ô 1 công thức) Kết hợp các cột theo chiều dọc trong khi xếp một cột lên trên cột khácTrong ví dụ này, chúng tôi có hai danh sách khoảng không quảng cáo khác nhau mà chúng tôi muốn kết hợp thành một. Giả sử hai nhân viên khác nhau đếm các mặt hàng quần áo ở các phần riêng biệt trong cửa hàng của bạn và bạn muốn kết hợp các danh sách mà họ trả lại cho bạn thành một danh sách/cột Bằng cách sử dụng hàm INDIRECT, hàm ROW và hàm COUNTIF, chúng ta có thể tạo một công thức sẽ xếp chồng nội dung từ một cột lên trên nội dung của cột khác. Với ví dụ/công thức được hiển thị bên dưới, nội dung của toàn bộ một cột sẽ được liệt kê cùng với công thức, trước khi nội dung của cột thứ hai được liệt kê bên dưới nó (Ngược lại với phương pháp xen kẽ cột trong ví dụ tiếp theo) Nhiệm vụ. Kết hợp hai danh sách các mặt hàng quần áo thành một danh sách các mặt hàng tồn kho duy nhất… hay nói cách khác, tạo một danh sách hiển thị một trong số từng mặt hàng có trong kho bằng cách kết hợp hai danh sách Hợp lý. Kết hợp phạm vi A3. A10 và phạm vi B3. B10 theo chiều dọc thành một cột công thức. Công thức dưới đây, được nhập vào ô màu xanh lam (D3), ví dụ này =IF(A3<>"",A3,INDIRECT("B"&ROW()-COUNTIF(A$3. 1000 đô la Úc,"<>"))) Đảm bảo rằng bạn đặt công thức của mình vào đúng hàng, theo các hàng đầu tiên của tham chiếu trong công thức (Nếu tham chiếu của công thức bắt đầu ở hàng 3, hãy đảm bảo rằng công thức đầu tiên của bạn nằm ở hàng 3 Lưu ý trong hình ảnh ví dụ cách công thức trong ô D3 đã được sao chép vào các ô bên dưới nó. Hãy nhớ làm điều này trong trang tính của bạn nữa Kết hợp các cột theo chiều dọc trong khi xen kẽ từ cột này sang cột khác cho mỗi hàngTrong ví dụ này, chúng ta sẽ kết hợp các danh sách khoảng không quảng cáo giống như ví dụ trước, theo chiều dọc thành một cột duy nhất, nhưng lần này chúng ta sẽ sử dụng một công thức khác xen kẽ giữa các cột khi liệt kê dữ liệu cột được kết hợp (Trái ngược với việc xếp chồng một Để làm điều này, chúng tôi sẽ sử dụng chức năng INDEX, chức năng ROW và chức năng MOD Nhiệm vụ. Kết hợp hai danh sách các mặt hàng quần áo vào một danh sách duy nhất và hiển thị tất cả các mặt hàng được tìm thấy Hợp lý. Ghép các nội dung trong phạm vi A3. A10 và các nội dung trong phạm vi B3. B10 theo chiều dọc thành một cột công thức. Công thức dưới đây, được nhập vào ô màu xanh lam (D3), ví dụ này =INDEX($A$2. $B$1000,ROW()/2,MOD(ROW(),2)+1) Giống như công thức trong ví dụ trước, đảm bảo rằng công thức của bạn được đặt ở đúng hàng, theo các hàng đầu tiên của tài liệu tham khảo công thức của bạn. Tuy nhiên, với công thức cụ thể này, nếu dữ liệu của bạn bắt đầu trên một hàng lẻ, chẳng hạn như hàng 3, thì tham chiếu công thức của bạn sẽ cần bắt đầu trên hàng trước đó/chẵn để đảm bảo rằng tất cả dữ liệu đều được ghi lại (Như minh họa trong ví dụ Lưu ý trong hình ảnh ví dụ cách công thức trong ô D3 được sao chép vào các ô bên dưới. Hãy nhớ làm điều này trong trang tính của bạn nữa Cách kết hợp các cột từ các trang tính riêng biệtBạn có thể thấy các tình huống yêu cầu bạn kết hợp các cột từ các tab riêng biệt trong Excel Điều này có thể được thực hiện bằng cách chỉ cần tham khảo một tên tab nhất định khi chỉ định phạm vi trong công thức. Vì vậy, nơi bạn thường đặt một phạm vi như "A1. C", khi tham chiếu một trang tính khác trong khi kết hợp các cột, bạn sẽ chỉ định tên tab, bằng cách thêm tên tab và dấu chấm than trước khi liệt kê cột và hàng cho phạm vi, chẳng hạn như "TabName. A1. C" Tuy nhiên, khi tên tab có khoảng trắng, bạn sẽ cần sử dụng dấu nháy đơn trước và sau khi nhập tên tab, chẳng hạn như 'Tên tab'. A1. C Dưới đây là ví dụ về cách kết hợp các cột từ nhiều tab trong Excel, trong đó có hai danh sách trong các tab khác nhau, được kết hợp thành một danh sách trên một tab hoàn toàn riêng biệt Giả sử bạn có một danh sách các mặt hàng quần áo trên một tab, một danh sách các mặt hàng quần áo khác trên một tab riêng biệt và bạn muốn hiển thị danh sách tổng hợp các mặt hàng quần áo duy nhất có trong kho, trên một tab hoàn toàn khác (có nghĩa là *Lưu ý rằng bạn có thể tham chiếu các tab đã chỉ định với bất kỳ công thức nào mà bạn đã học trong bài viết này, nhưng đối với ví dụ này, chúng tôi đang sử dụng một mảng có dấu và (&) để minh họa cách kết hợp các cột từ các tab khác nhau Nhiệm vụ. Kết hợp danh sách các mặt hàng quần áo trên tab có nhãn "Danh sách 1" với danh sách các mặt hàng quần áo trên tab có nhãn "Danh sách 2" và hiển thị danh sách các mặt hàng quần áo kết hợp, trên tab hoàn toàn riêng biệt Hợp lý. Kết hợp phạm vi 'Danh sách 1'. A3. A12, với phạm vi 'Danh sách 2'. A3. A12 công thức. Công thức dưới đây, được nhập vào ô màu xanh lam (A3), ví dụ này ='Danh sách 1′. A3. A12&" "&'Danh sách 2′. B3. B12 Đây là hai danh sách khác nhau về các mặt hàng quần áo, được giữ trên hai tab riêng biệt có nhãn "Danh sách 1" và "Danh sách 2" Và đây là danh sách kết hợp các mặt hàng quần áo, trong đó công thức và dữ liệu kết hợp được giữ trên một tab riêng Bài kiểm tra nhỏ bất ngờ. Kiểm tra kiến thức của bạnTrả lời các câu hỏi dưới đây về cách kết hợp các cột trong Excel để trau dồi kiến thức của bạn. Cuộn xuống dưới cùng để tìm câu trả lời cho bài kiểm tra Công thức kết hợp cột ngang=A3. A12&" "&B3. B12 =CONCAT(A3," ",B3) =CONCATENATE(A3. A12," ",B3. B12) Công thức kết hợp cột dọc=IF(A3<>"",A3,INDIRECT("B"&ROW()-COUNTIF(A$3. 1000 đô la Úc,"<>"))) =INDEX($A$2. $B$1000,ROW()/2,MOD(ROW(),2)+1) Câu hỏi 1Công thức nào sau đây sẽ cho phép bạn kết hợp các cột theo chiều dọc?
Câu hỏi 2Công thức nào sau đây sẽ đặt khoảng cách giữa các nội dung được kết hợp theo chiều ngang?
Câu hỏi số 3Đúng hay sai. Đối với các phiên bản Excel cũ hơn, bạn phải giữ Ctrl + Shift + Enter trên bàn phím trước khi nhập công thức mảng Làm cách nào để dồn dữ liệu từ nhiều cột vào một cột trong công thức Excel?Kết hợp dữ liệu bằng hàm CONCAT . Chọn ô mà bạn muốn đặt dữ liệu kết hợp Nhập =CONCAT( Chọn ô bạn muốn kết hợp trước. Sử dụng dấu phẩy để phân tách các ô bạn đang kết hợp và sử dụng dấu ngoặc kép để thêm dấu cách, dấu phẩy hoặc văn bản khác Đóng công thức bằng dấu ngoặc đơn và nhấn Enter Công thức nhân cột trong Excel là gì?Nhân một cột số với một hằng số
. Đảm bảo bao gồm ký hiệu $ trước B và trước 2 trong công thức, rồi nhấn ENTER. =A2*$B$2 in a new column in your spreadsheet (the above example uses column D). Be sure to include a $ symbol before B and before 2 in the formula, and press ENTER. |
