Chuyển đổi hàm COUNTIFS sang Excel 2003

Tôi đang làm việc trên trang THỐNG KÊ cho một bảng tính ở công ty. Tôi vừa nhận ra rằng công ty sử dụng Office 2003 trong khi tôi đang dùng Office 2007.
Làm thế nào để chuyển đổi các công thức sau sang định dạng năm 2003?

=COUNTIFS(SQUAD!Q$2:Q$109, "<=" & EDATE(TODAY(), 6), SQUAD!D$2:D$109, A3)

Và

=COUNTIF(SQUAD!H95:H101,"P*")

Cảm ơn



Trả lời:

Steven đã viết:

Tôi đang làm việc trên trang THỐNG KÊ cho một bảng tính ở công ty. Tôi vừa nhận ra rằng công ty sử dụng Office 2003 trong khi tôi đang dùng phiên bản 2007. Làm thế nào để chuyển đổi các công thức sau sang định dạng của Office 2003?

=COUNTIFS(SQUAD!Q$2:Q$109, "<=" & EDATE(TODAY(), 6), SQUAD!D$2:D$109, A3)
Và
=COUNTIF(SQUAD!H95:H101,"P*")


Không cần thay đổi hàm COUNTIF. Hàm này tương thích với Excel 2003.

Thay vì sử dụng hàm COUNTIFS, công thức sau đây hoạt động trong tất cả các phiên bản Excel:

=SUMPRODUCT((SQUAD!Q$2:Q$109<=EDATE(TODAY(),6))*(SQUAD!D$2:D$109=A3))

Phép nhân (*) có hai mục đích. Nó hoạt động giống như phép AND, nhưng chúng ta không thể sử dụng phép AND trong ngữ cảnh này với mục đích đã định. Và nó chuyển đổi TRUE và FALSE thành 1 và 0 khi cần thiết.



Trả lời:
Có thể sử dụng hàm SUMPRODUCT cho việc này.
>
>=SUMPRODUCT(--( SQUAD!Q2:Q109 <= EDATE(TODAY(), 6)),--( SQUAD!D$2:$D$109 = A3)) >và >=SUMPRODUCT(--(LEFT( SQUAD!H95:H101 ,1)="P")) >Ký tự đại diện có thể gây ra vấn đề, nhưng bạn có thể khắc phục được. > >
Trang web xldynamic có bài viết giải thích khá đầy đủ tại đây: http://xldynamic.com/source/xld.SUMPRODUCT.html

Trả lời:
Khi so sánh với dữ liệu, kết quả thu được dường như không đúng như mong đợi.
Nó trả về 7 (thực tế là số ô chứa nội dung mà ô A3 đề cập đến). Đáng lẽ nó phải trả về 1, vì trong 7 kết quả trả về trước đó, đây là kết quả duy nhất đáp ứng tiêu chí dữ liệu.

Tôi đang cố gắng lấy một ô để đếm số ngày trong một khoảng thời gian nhất định mà ngày đó cách ngày HÔM NAY (TODAY) không quá sáu tháng.

Ngoài ra, cần tra cứu một số tiêu chí nhất định trước khi tính toán.

Ví dụ, tôi muốn nó tìm kiếm trong cột B tất cả các văn bản có chứa "LSMW" và sau đó xem chúng có ngày tương ứng nằm trong khoảng từ HÔM NAY đến sáu tháng sau hay không.





Trả lời:
Tôi đã thử cả công thức của bạn và của Paul nhưng dường như không cho ra kết quả đúng khi so sánh với dữ liệu.

Nó trả về 7 (thực tế là số ô chứa thông tin mà ô A3 đề cập đến). Điều đáng lẽ nó phải trả về là 1, vì trong số 7 kết quả trả về trước đó, đây là kết quả duy nhất đáp ứng tiêu chí dữ liệu.

Tôi đang cố gắng lấy một ô để đếm số ngày trong một khoảng thời gian nhất định mà ngày đó cách ngày HÔM NAY (TODAY) không quá sáu tháng.

Ngoài ra, cần tra cứu một số tiêu chí nhất định trước khi tính toán.

Ví dụ, tôi muốn nó tìm kiếm trong cột B tất cả các văn bản có chứa "LSMW" và sau đó xem chúng có ngày tương ứng nằm trong khoảng từ HÔM NAY đến sáu tháng sau hay không.



Trả lời:
>>
>=SUMPRODUCT(--( SQUAD!Q2:Q109 <= EDATE(TODAY(), 6)),--( SQUAD!D$2:$D$109 = A3)) > >=SUMPRODUCT(--(LEFT( SQUAD!H95:H101 ,1)="P"))

Chào Paul,

Hãy cẩn thận đừng bao gồm ký hiệu $ trong mảng, điều này có thể gây ra lỗi hoặc kết quả sai nếu sao chép và dán xuống dưới. Tuy nhiên, Steven không đề cập đến việc điều này sẽ ảnh hưởng đến mảng hay không mà chỉ là biện pháp phòng ngừa.

Stevenmoss, vui lòng kiểm tra lại, có thể ngày tháng trong Cột Q không chính xác. Tôi biết cả hai công thức tổng tích được đề xuất đều hoạt động tốt nếu danh sách trong D và Q chính xác.

Chúc may mắn!

Jaeson



Trả lời:
Tôi đã kiểm tra tất cả các định dạng ngày tháng, tất cả đều có vẻ ổn. Tôi cũng đã thử bỏ dấu $ đi, nhưng vẫn không được.


Trả lời:

Steven đã viết:

Tôi đã thử cả công thức của bạn và của Paul nhưng dường như không cho ra kết quả đúng khi so sánh với dữ liệu.


Có sự khác biệt giữa cách hàm COUNTIFS và SUMPRODUCT diễn giải dữ liệu dạng số trong ô. COUNTIFS sẽ diễn giải nó như một giá trị số; SUMPRODUCT sẽ diễn giải nó như một dạng văn bản.

Vậy hãy thử cách sau:

=SUMPRODUCT((--SQUAD!Q$2:Q$109<=EDATE(TODAY(),6))
*(--SQUAD!D$2:D$109=A3))

Dấu phủ định kép (--) có tác dụng chuyển đổi văn bản số thành số. Nó cũng có tác dụng với các số thông thường.

Nếu bất kỳ ô nào trong Q2:Q109 và D2:D109 có thể chứa văn bản không phải là số, công thức đó có thể trả về lỗi #VALUE.

Trong trường hợp đó, bạn cần nhập chuỗi sau (nhấn tổ hợp phím Ctrl+Shift+Enter thay vì chỉ Enter):

=SUMPRODUCT(
IF(ISNUMBER(--SQUAD!Q$2:Q$109),--SQUAD!Q$2:Q$109<=EDATE(TODAY(),6))
*IF(ISNUMBER(--SQUAD!D$2:D$109),--SQUAD!D$2:D$109=A3))

Dĩ nhiên, tốt hơn hết là nên tránh sử dụng văn bản số ngay từ đầu.

Để giúp bạn giải quyết vấn đề đó, chúng tôi có thể cần thêm thông tin. Nhưng bạn có thể thử chọn ô Q2:Q109 và sử dụng tính năng Chuyển văn bản thành cột. Thực hiện tương tự với ô D2:D109.

Nếu tất cả những cách trên đều không hiệu quả, tôi khuyên bạn nên tải lên một tệp Excel ví dụ (không chứa bất kỳ dữ liệu riêng tư nào) để minh họa vấn đề lên một trang web chia sẻ tệp.

Sau đó, hãy đăng liên kết "đã chia sẻ", "công khai" hoặc "chỉ xem" (hay còn gọi là URL; http://...) trong phần trả lời tại đây. Dưới đây là danh sách một số trang web chia sẻ tập tin miễn phí; hoặc bạn có thể sử dụng trang web của riêng mình.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
Chia sẻ nhanh: http://www.rapidshare.com



Trả lời:
Cảm ơn sự giúp đỡ của bạn, tôi sẽ thử gợi ý của bạn.


Cảm ơn bạn một lần nữa


Trả lời:

=COUNTIFS(SQUAD!Q$2:Q$109, "<=" & EDATE(TODAY(), 6), SQUAD!D$2:D$109, A3)

CHÀO,

Hãy thử cách này xem...

=SUMPRODUCT((SQUAD!Q$2:Q$109<=DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY())))*(SQUAD!D$2:D$109=A3))



Trả lời:
Tôi đã kiểm tra tất cả các định dạng ngày tháng, tất cả đều có vẻ ổn. Tôi cũng đã thử bỏ dấu $ đi, nhưng vẫn không được.


CHÀO,

Tôi thắc mắc tại sao bạn không nhận được câu trả lời chính xác khi sử dụng hàm SUMPRODUCT được đề xuất, trong khi ban đầu bạn đã thực hiện phép tính bằng hàm COUNTIFS vào năm 2007.

Nếu cách này hiệu quả vào năm 2007:

=COUNTIFS(SQUAD!Q$2:Q$109, "<=" & EDATE(TODAY(), 6), SQUAD!D$2:D$109, A3)

Vậy thì cái này sẽ ổn vào năm 2003 (từ Paul với một chút chỉnh sửa)...

=IF(A3="",0,SUMPRODUCT(--(Squad!Q$2:Q$109<=(EDATE(TODAY(),6))),--(Squad!D$2:$D$109=A3)))

Nếu tất cả các công thức vẫn không hoạt động... Hãy tải tệp lên như Joeu đã đề xuất.

Chúc một ngày tốt lành!

Jaeson

Comments

Post a Comment

Popular posts from this blog

Xử lý hàng loạt các tệp Excel được bảo vệ bằng mật khẩu

Xử lý hàng loạt các tệp Excel được bảo vệ bằng mật khẩu Tôi đã tạo một bộ bảng chấm công của nhân viên trong một thư mục, mỗi bảng được xác định bằng tên và số ID của nhân viên, đồng thời được bảo vệ bằng Số Bảo hiểm Quốc gia của nhân viên làm mật khẩu. Họ sẽ thêm các yêu cầu làm thêm giờ và số dặm vào bảng tính của họ và tôi sẽ xử lý tất cả các tệp vào cuối tháng để lập bảng lương, thêm tất cả các bản ghi từ mỗi tệp vào bảng cơ sở dữ liệu MS Access. Một vấn đề mà tôi gặp phải là tôi không biết cách mở các tệp được bảo vệ bằng mật khẩu trong VBA để xử lý chúng. Có ai ở đó có kinh nghiệm về quá trình tương tự không? Stuart Trả lời: Xin chào, VBA để bảo vệ và không bảo vệ sổ làm việc Sub ProtectAll() Dim sh As Worksheet Làm mờ myPassword dưới dạng chuỗi myPassword = "mật khẩu" Đối với mỗi sh trong ActiveWorkbook.Worksheets sh.Protect Mật khẩu:=myPassword tiếp theo Kết thúc phụ Sub UnrotectAll() Dim sh As Worksheet Làm mờ myPassword dưới dạng chuỗi myPassword = ...
Read more

Thu hẹp và mở rộng cột trong Excel

Thu hẹp và mở rộng cột trong Excel Làm thế nào để thu hẹp hoặc mở rộng một cột khi tôi không nhìn thấy thước kẻ có chữ cái cột? Trả lời: Nhấp chuột phải vào cột > Chiều rộng cột > Tại đây, bạn có thể nhập giá trị cho các cột để thu nhỏ hoặc mở rộng. Ví dụ, bạn có thể nhập giá trị 8, gần với chiều rộng cột mặc định. Cột sẽ hiển thị. Bạn có thể kéo theo dấu phân cách cột (hoặc thước kẻ). Nếu những điều trên không phải là điều bạn muốn, tức là nếu chữ cái cột không hiển thị > Chọn bất kỳ ô nào trong cột đó > CTRL+Phím cách để chọn cột > Nhấp chuột phải > Chiều rộng cột như trong bước 1. Trả lời: Chỉ là một ý tưởng, một lựa chọn khác là sử dụng Freeze Panes để bạn luôn có thể nhìn thấy tiêu đề Cột trên cùng để thay đổi kích thước cột... Trên thanh Ribbon, nhấp vào Đóng băng ngăn - Đóng băng hàng trên cùng
Read more

Excel: Tìm và trả về "số cuối cùng" trong một cột, trong đó cột chứa: cả ô trống và số dương tùy ý (lớn hơn 0).

Image
Excel: Tìm và trả về "số cuối cùng" trong một cột, trong đó cột chứa: cả ô trống và số dương tùy ý (lớn hơn 0). Tôi có kinh nghiệm sử dụng Excel ở mức trung bình nhưng không thể hiểu cách đạt được những điều sau: 1) Về Cột K, khoảng K12 đến K377. 2) Tất cả các ô trong phạm vi trên đều là ô trống hoặc các ô chứa số dương lớn hơn 0, ví dụ: 14,37. (Lưu ý rằng sự tồn tại của các ô và số trống trong phạm vi này là hoàn toàn tùy ý (không có bất kỳ mẫu logic, lặp lại nào). 3) Tôi đang cố gắng viết một công thức (hoặc sử dụng hàm hoặc macro) yêu cầu như sau: A) Tìm kiếm trong phạm vi K12 đến K377. B) Xác định vị trí và xác định số "cuối cùng" trong cột. (LƯU Ý: Với "cuối cùng", ý tôi là số nằm gần cuối phạm vi của cột nhất. Tức là số gần nhất với hàng 377, trong cột K.) C) Dù số đó là gì, tôi muốn số đó được trả về và hiển thị trong ô tôi chọn, giả sử ô K10. D) Tự động làm mới: Lý tưởng nhất là bất cứ khi nào cột (cột K) số được chỉnh sửa (thêm vào hoặc tr...
Read more