Hàm Excel khó nhất là gì?

Công thức Microsoft Excel có thể làm hầu hết mọi thứ. Trong bài viết này, bạn sẽ tìm hiểu sức mạnh của các công thức Microsoft Excel và định dạng có điều kiện, với ba ví dụ hữu ích

Làm quen với Microsoft Excel

Chúng tôi đã đề cập đến một số cách khác nhau để sử dụng Excel tốt hơn, chẳng hạn như nơi tìm các mẫu Excel có thể tải xuống tuyệt vời và cách sử dụng Excel làm công cụ quản lý dự án

Phần lớn sức mạnh của Excel nằm sau các công thức và quy tắc Excel giúp bạn thao tác dữ liệu và thông tin một cách tự động, bất kể bạn chèn dữ liệu gì vào bảng tính

Hãy tìm hiểu cách bạn có thể sử dụng công thức và các công cụ khác để sử dụng Microsoft Excel tốt hơn

Định dạng có điều kiện với công thức Excel

Một trong những công cụ mà mọi người không thường xuyên sử dụng là Định dạng có điều kiện. Với việc sử dụng các công thức, quy tắc Excel hoặc chỉ một vài cài đặt thực sự đơn giản, bạn có thể chuyển đổi bảng tính thành bảng điều khiển tự động

Để đến Định dạng có điều kiện, bạn chỉ cần nhấp vào tab Trang chủ và nhấp vào biểu tượng thanh công cụ Định dạng có điều kiện

Trong Định dạng có điều kiện, có rất nhiều tùy chọn. Hầu hết trong số này nằm ngoài phạm vi của bài viết cụ thể này, nhưng phần lớn trong số đó là về tô sáng, tô màu hoặc tô bóng ô dựa trên dữ liệu trong ô đó

Đây có lẽ là cách sử dụng phổ biến nhất của định dạng có điều kiện—những việc như biến một ô thành màu đỏ bằng cách sử dụng các công thức nhỏ hơn hoặc lớn hơn. Tìm hiểu thêm về cách sử dụng câu lệnh IF trong Excel

Một trong những công cụ định dạng có điều kiện ít được sử dụng hơn là tùy chọn Bộ biểu tượng, cung cấp một bộ biểu tượng tuyệt vời mà bạn có thể sử dụng để biến ô dữ liệu Excel thành biểu tượng hiển thị bảng điều khiển

Khi bạn nhấp vào Quản lý quy tắc, nó sẽ đưa bạn đến Trình quản lý quy tắc định dạng có điều kiện

Tùy thuộc vào dữ liệu bạn đã chọn trước khi chọn bộ biểu tượng, bạn sẽ thấy ô được chỉ định trong cửa sổ Trình quản lý với bộ biểu tượng bạn vừa chọn

Khi bạn nhấp vào Chỉnh sửa quy tắc, bạn sẽ thấy hộp thoại nơi điều kỳ diệu xảy ra

Đây là nơi bạn có thể tạo công thức logic và phương trình sẽ hiển thị biểu tượng bảng điều khiển mà bạn muốn

Bảng điều khiển ví dụ này sẽ hiển thị thời gian dành cho các nhiệm vụ khác nhau so với thời gian được lập ngân sách. Nếu bạn vượt quá một nửa ngân sách, đèn vàng sẽ hiển thị. Nếu bạn hoàn toàn vượt quá ngân sách, nó sẽ chuyển sang màu đỏ

Như bạn có thể thấy, bảng điều khiển này cho thấy việc lập ngân sách thời gian không thành công

Gần một nửa thời gian được sử dụng vượt quá số tiền dự trù

Thời gian để tập trung lại và quản lý thời gian của bạn tốt hơn

1. Sử dụng Hàm VLookup

Nếu bạn muốn sử dụng các chức năng nâng cao hơn của Microsoft Excel, thì đây là một vài chức năng để bạn thử

Có thể bạn đã quen với hàm VLookup, cho phép bạn tìm kiếm một mục cụ thể trong một danh sách trong một cột và trả về dữ liệu từ một cột khác trong cùng một hàng với mục đó

Thật không may, chức năng này yêu cầu mục bạn đang tìm kiếm trong danh sách nằm ở cột bên trái và dữ liệu bạn đang tìm kiếm ở bên phải, nhưng nếu chúng bị chuyển đổi thì sao?

Trong ví dụ dưới đây, nếu tôi muốn tìm Nhiệm vụ mà tôi đã thực hiện vào ngày 25/6/2018 từ dữ liệu sau thì sao?

Trong trường hợp này, bạn đang tìm kiếm thông qua các giá trị ở bên phải và bạn muốn trả về giá trị tương ứng ở bên trái

Nếu bạn đọc các diễn đàn dành cho người dùng chuyên nghiệp về Microsoft Excel, bạn sẽ thấy nhiều người nói rằng điều này là không thể với VLookup. Bạn phải sử dụng kết hợp các hàm Index và Match để thực hiện việc này. Điều đó không hoàn toàn đúng

Bạn có thể làm cho VLookup hoạt động theo cách này bằng cách lồng một hàm CHOOSE vào đó. Trong trường hợp này, công thức Excel sẽ như thế này

 "=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)" 

Hàm này có nghĩa là bạn muốn tìm ngày 25/6/2013 trong danh sách tra cứu rồi trả về giá trị tương ứng từ chỉ số cột

Trong trường hợp này, bạn sẽ nhận thấy rằng chỉ số cột là "2", nhưng như bạn có thể thấy, cột trong bảng trên thực tế là 1, phải không?

Điều đó đúng, nhưng những gì bạn đang làm với chức năng "CHỌN" đang thao túng hai trường

Bạn đang gán các số "chỉ mục" tham chiếu cho phạm vi dữ liệu - gán ngày cho chỉ mục số 1 và nhiệm vụ cho chỉ mục số 2

Vì vậy, khi bạn gõ "2" trong hàm VLookup, thực ra bạn đang đề cập đến Chỉ mục số 2 trong hàm CHOOSE. Mát mẻ, phải không?

VLookup hiện sử dụng cột Ngày và trả về dữ liệu từ cột Tác vụ, mặc dù Tác vụ ở bên trái

Bây giờ bạn đã biết mẩu tin nhỏ này, hãy tưởng tượng bạn có thể làm gì khác

Nếu bạn đang cố gắng thực hiện các tác vụ tra cứu dữ liệu nâng cao khác, thì hãy xem bài viết này về cách tìm dữ liệu trong Excel bằng các hàm tra cứu

2. Công thức lồng nhau để phân tích chuỗi

Đây là một công thức Excel điên rồ hơn dành cho bạn. Có thể có trường hợp bạn nhập dữ liệu vào Microsoft Excel từ một nguồn bên ngoài bao gồm một chuỗi dữ liệu được phân tách

Khi bạn đưa dữ liệu vào, bạn muốn phân tích dữ liệu đó thành các thành phần riêng lẻ. Dưới đây là ví dụ về thông tin tên, địa chỉ và số điện thoại được phân tách bằng dấu ";"

Đây là cách bạn có thể phân tích cú pháp thông tin này bằng công thức Excel (xem liệu bạn có thể tinh thần làm theo cùng với sự điên rồ này không)

Đối với trường đầu tiên, để trích xuất mục ngoài cùng bên trái (tên của người đó), bạn chỉ cần sử dụng hàm LEFT trong công thức

 "=LEFT(A2,FIND(";",A2,1)-1)" 

Đây là cách logic này hoạt động

• Tìm kiếm chuỗi văn bản từ A2
• Tìm dấu ";"
• Trừ một cho vị trí thích hợp của phần cuối của chuỗi đó
• Lấy văn bản ngoài cùng bên trái đến điểm đó

Trong trường hợp này, văn bản ngoài cùng bên trái là "Ryan". nhiệm vụ hoàn thành

3. Công thức lồng nhau trong Excel

Nhưng những phần khác thì sao?

Có thể có nhiều cách dễ dàng hơn để làm điều này, nhưng vì chúng tôi muốn thử và tạo công thức Nested Excel điên rồ nhất có thể (thực sự hiệu quả), nên chúng tôi sẽ sử dụng một cách tiếp cận độc đáo

Để trích xuất các phần bên phải, bạn cần lồng nhiều hàm RIGHT để lấy phần văn bản cho đến dấu ";" đầu tiên. . Đây là giao diện của phần trích xuất phần số đường của địa chỉ

 "=LEFT((RIGHT(A2,LEN(A2)-FIND(";",A2))),FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))),1)-1)" 

Trông có vẻ điên rồ, nhưng không khó để ghép lại với nhau. Tất cả những gì tôi đã làm là lấy chức năng này

 RIGHT(A2,LEN(A2)-FIND(";",A2)) 

Và chèn nó vào mọi vị trí trong hàm TRÁI ở trên, nơi có "A2"

Điều này trích xuất chính xác phần thứ hai của chuỗi

Mỗi phần tiếp theo của chuỗi cần tạo một tổ khác. Bây giờ tất cả những gì bạn cần làm là lấy phương trình "RIGHT" mà bạn đã tạo trong phần trước và dán nó vào một công thức RIGHT mới với công thức RIGHT trước đó được dán vào nơi bạn thấy "A2". Đây là những gì nó trông giống như

 (RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))) 

Sau đó, bạn cần lấy công thức ĐÓ và đặt nó vào công thức TRÁI ban đầu ở bất cứ nơi nào có "A2"

Công thức bẻ cong tâm trí cuối cùng trông như thế này

 "=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),FIND(";",(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),1)-1)" 

Công thức đó trích xuất chính xác "Portland, ME 04076" ra khỏi chuỗi gốc

Để trích xuất phần tiếp theo, hãy lặp lại quy trình trên một lần nữa

Các công thức Excel của bạn có thể trở nên thực sự lộn xộn, nhưng tất cả những gì bạn đang làm là cắt và dán các công thức dài vào chính chúng, tạo ra các tổ dài vẫn hoạt động

Vâng, điều này đáp ứng yêu cầu về "điên rồ". Nhưng thành thật mà nói, có một cách đơn giản hơn nhiều để thực hiện điều tương tự với một chức năng

Chỉ cần chọn cột có dữ liệu được phân tách, sau đó trong mục menu Dữ liệu, chọn Văn bản thành Cột

Điều này sẽ mở ra một cửa sổ nơi bạn có thể chia chuỗi theo bất kỳ dấu phân cách nào bạn muốn. Chỉ cần nhập ';'

Trong một vài cú nhấp chuột, bạn có thể làm điều tương tự như công thức điên rồ ở trên. nhưng đâu là niềm vui trong đó?

Phát điên với các công thức Microsoft Excel

Vì vậy, có bạn có nó. Các công thức trên chứng minh mức độ vượt trội của một người khi tạo các công thức Microsoft Excel để hoàn thành một số tác vụ nhất định

Đôi khi những công thức Excel đó không thực sự là cách dễ nhất (hoặc tốt nhất) để hoàn thành mọi việc. Hầu hết các lập trình viên sẽ yêu cầu bạn giữ cho nó đơn giản và điều đó đúng với các công thức Excel cũng như với bất kỳ thứ gì khác.

Nếu bạn thực sự muốn nghiêm túc với việc sử dụng Excel, bạn sẽ muốn đọc qua hướng dẫn sử dụng Microsoft Excel cho người mới bắt đầu của chúng tôi. Nó có mọi thứ bạn cần để bắt đầu tăng năng suất với Excel. Sau đó, hãy đảm bảo tham khảo bảng tóm tắt các chức năng Excel cần thiết của chúng tôi để được hướng dẫn thêm

Hàm excel cao cấp nhất là gì?

Một số hàm Excel phức tạp là gì?

Công thức phức tạp trong Excel là gì?