Home-theater-designers
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 công thức Microsoft Excel và định dạng có điều kiện mạnh mẽ như thế nào, 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ể dữ liệu bạn chèn vào bảng tính là gì.
Hãy cùng 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 sử dụng đủ thường xuyên 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 một bảng điều khiển tự động.
Để đến với Định dạng có điều kiện, bạn chỉ cần nhấp vào Nhà và nhấp vào Định dạng có điều kiện biểu tượng thanh công cụ.
Trong Định dạng có điều kiện, có rất nhiều tùy chọn. Hầu hết những điều 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 là về tô sáng, tô màu hoặc tô bóng các ô 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 thứ như chuyển ô sang màu đỏ bằng cách sử dụng 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à 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ý các 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 .
không thể khởi động windows 10 ở chế độ an toà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ỉ ra trong cửa sổ Trình quản lý có 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 trang tổng quan mà bạn muốn.
Trang tổng quan mẫu 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 đã 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 rằng 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 dành cho số tiền dự trù.
Đã đến lúc tái tập trung 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 hàm Microsoft Excel nâng cao hơn, thì đây là một vài hàm để bạn thử.
Có thể bạn đã quen thuộc với hàm VLookup, cho phép bạn tìm kiếm trong danh sách cho một mục cụ thể trong một cột và trả về dữ liệu từ một cột khác trong cùng hàng với mục đó.
Thật không may, chức năng 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 được chuyển đổi thì sao?
Trong ví dụ dưới đây, nếu tôi muốn tìm Tác 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 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 của 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 nó. Trong trường hợp này, công thức Excel sẽ giống như sau:
'=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)'Chức năng này có nghĩa là bạn muốn tìm ngày 25/6/2013 trong danh sách tra cứu và sau đó trả về giá trị tương ứng từ chỉ mục 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 sự là 1, phải không?
Điều đó đúng, nhưng những gì bạn đang làm với ' LỰA CHỌN hàm 'đang thao tác trên hai trường.
Bạn đang chỉ định số 'chỉ mục' tham chiếu cho phạm vi dữ liệu - chỉ định ngày tháng cho số chỉ mục 1 và nhiệm vụ cho chỉ mục số 2.
Vì vậy, khi bạn gõ '2' vào hàm VLookup, bạn thực sự đang đề cập đến Chỉ mục số 2 trong hàm CHOOSE. Tuyệt, phải không?
VLookup hiện sử dụng Ngày và trả về dữ liệu từ Nhiệm vụ , mặc dù Nhiệm vụ ở bên trái.
tại sao ảnh của tôi không gửi
Bây giờ bạn đã biết điều 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, hãy xem bài viết này trên tìm kiế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 cú pháp 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ẻ. Đây là một 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 ';' tính cách.
Đây là cách bạn có thể phân tích cú pháp thông tin này bằng cách sử dụng công thức Excel (xem liệu bạn có thể theo dõi tinh thần với sự điên rồ này hay 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 ';' ký hiệu dấu phân cách
- Trừ một số cho vị trí thích hợp của phần cuối của phần 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'. Hoàn thành nhiệm vụ.
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 để thực hiện việc này, nhưng vì chúng tôi muốn thử và tạo công thức Excel lồng nhau điên rồ nhất có thể (cách này thực sự hoạt động), chúng tôi sẽ sử dụng một cách tiếp cận duy nhất.
Để 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 phần đầu tiên ';' và thực hiện lại hàm LEFT trên nó. Đây là những gì trông giống như khi trích xuất mộ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à sử dụng chức năng này:
RIGHT(A2,LEN(A2)-FIND(';',A2))Và chèn nó vào mọi nơi trong BÊN TRÁI chức năng phía trên nơi có một 'A2'.
Điều này trích xuất chính xác phần thứ hai của chuỗi.
nóng quá thì cpu quá nóng
Mỗi phần tiếp theo của chuỗi cần một tổ khác được tạo. Bây giờ tất cả những gì bạn cần làm là lấy ' ĐÚNG '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 như thế nào.
(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 kỳ nơi nào có 'A2'.
Công thức uốn nắn 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.
Để giải nén phần tiếp theo, hãy lặp lại quá trình trên một lần nữa.
Các công thức Excel của bạn có thể thực sự trở nên 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.
Có, điều này đáp ứng yêu cầu cho 'điên'. 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 đó bên dưới Dữ liệu mục menu, chọn Văn bản thành cột .
Thao tác này sẽ hiển thị 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. Đơn giản chỉ cần nhập ' ; 'và bạn sẽ thấy rằng bản xem trước của dữ liệu đã chọn của bạn sẽ thay đổi theo.
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 thú vị ở chỗ nào?
Phát điên với công thức Microsoft Excel
Vì vậy, bạn có nó. Các công thức trên chứng minh mức độ vượt trội mà một người có thể nhận được khi tạo 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 công việc. Hầu hết các lập trình viên sẽ yêu cầu bạn giữ 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ỳ công thức nào 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 của mình với Excel. Sau đó, hãy nhớ tham khảo bảng gian lận các hàm cần thiết trong Excel của chúng tôi để được hướng dẫn thêm.
Tín dụng hình ảnh: kues / Depositphotos
Đăng lại Đăng lại tiếng riu ríu E-mail 7 chức năng tài chính hàng đầu trong ExcelCho dù bạn là một kế toán hay một chuyên gia tài chính, bạn nên biết các công thức Excel này.
Đọc tiếp Chủ đề liên quan- Năng suất
- Mẹo về bảng tính
- Microsoft Excel
- Microsoft Office 2016
- Mẹo Microsoft Office
- Microsoft Office 2019
Ryan có bằng Cử nhân Kỹ thuật Điện. Anh ấy đã làm việc 13 năm trong lĩnh vực kỹ thuật tự động hóa, 5 năm trong lĩnh vực CNTT và hiện là Kỹ sư ứng dụng. Từng là Biên tập viên quản lý của MakeUseOf, ông đã phát biểu tại các hội nghị quốc gia về Trực quan hóa Dữ liệu và đã được giới thiệu trên đài truyền hình và đài phát thanh quốc gia.
Xem thêm từ Ryan DubeTheo dõi bản tin của chúng tôi
Tham gia bản tin của chúng tôi để biết các mẹo công nghệ, đánh giá, sách điện tử miễn phí và các ưu đãi độc quyền!
Bấm vào đây để đăng ký