Len công thức excel

Trong bài này chúng ta sẽ tìm hiểu về công thức (formula) trong Excel, đây được xem là trái tim của Excel nên được đánh giá là khó học nhất

Bài viết này đã được đăng tại freetuts. net , không được sao chép dưới mọi hình thức.

Đương nhiên, vì là một khối kiến ​​trúc nên nội dung của bài viết này chỉ giúp bạn hiểu và biết cách tạo một công thức trong Excel như thế nào thôi nhé

1. Công thức (Formula) trong Excel là gì?

Công thức trong Excel là một dãy lệnh được sử dụng để xử lý tính toán rất chuyên nghiệp, nó giúp giảm thời gian tính toán cho người sử dụng Excel

Như bài trước mình đã nói, khi nhập dữ liệu vào một ô bất kỳ thì Excel sẽ hiểu đó là dữ liệu chứ không phải là công thức, vì vậy để Excel biết bạn đang nhập công thức thì phải thêm dấu bằng = phía trước nhé.

Bài viết này đã được đăng tại [free tuts. mạng lưới]

=CONG_THUC

Ví dụ. Cho hai giá trị A1 =20 và B1 = 30. Hãy viết công thức cho ô C1 với nội dung là C1 = A1 * B1 = 30 * 20 = 600

B1. Nhập giá trị 20 vào ô A1 và 30 vào B1

B2. Chọn ô cần nhập công thức, thức được chọn vào C1. Sau đó gõ nội dung cho nó là =A1*B1

Bạn hãy lưu ý nhé, khi nhập công thức thì ô văn bản phía trên hiển thị cũng như nội dung tương tự như bạn đã nhập (Thanh công thức). Vì vậy thay vì nhập vào ô C1 thì bạn cũng có thể nhập vào ô văn bản đó

B3. Nhập xong bạn hãy nhấn Enter, kết quả sẽ xuất hiện là 600

Nếu muốn thay đổi công thức thì bạn chỉ việc thay đổi ở ô textbox mà mình khoanh tròn nhé

Đây là một ví dụ đơn giản giúp bạn hiểu về công thức trong Excel. Bạn cũng có thể coi đây là lập trình Excel, bởi vì nó giống như một ngôn ngữ lập trình ở mức cơ bản

2. Một ví dụ khác về công thức trong Excel

Mình sẽ làm thêm một ví dụ nữa để giúp các bạn hiểu rõ hơn về công thức (công thức)

bài toán. Tính điểm trung bình các môn học

B1. Cho bảng số liệu điểm của các môn học như sau

B2. Hãy tính điểm trung bình của 6 môn theo công thức. ĐTB = (tổng 6 môn) / 6

You select ô G2 và nhập công thức sau. =(A2+B2+C2+D2+E2+F2)/6, sau đó nhấn Enter

Kết quả trả về là 8. 5

3. Sử dụng chức năng trong công thức

Hàm là những hàm được Excel hỗ trợ sẵn giúp tính toán trở nên đơn giản hơn

Quay lại ví dụ tính điểm trung bình, mình sẽ dùng hàm SUM để tính tổng của 6 môn trên. Formula will like after. =SUM(A2:F2)/6

in which

• Hàm SUM là hàm tính tổng
• Constructor of SUM function as after. SUM(danh_sách_các_cell)
• A2. F2 is write off of a dãy các ô từ A2 đến F2, tức là A2:F2 = (A2,B2,C2,D2,E2,F2)

Một số chức năng thông thường được sử dụng như

• TỔNG. Tính tổng
• TỐI ĐA. Search the best value
• SQRT. base level hai
• ĐẾM. Dem number of cell (ô)
• ...

Mình sẽ giới thiệu chi tiết hơn về hàm trong Excel ở bài tiếp theo nhé, hoặc xem danh sách hàm Excel

Thank you it off. Chức năng của bạn không hoạt động. Nhưng tôi nên nói rằng liệu chúng ta có thể có một công thức giải pháp không phải là giải pháp VBA hay bất kỳ phần bổ trợ nào. Bởi vì nếu tôi gửi email cho đồng nghiệp của mình với phần bổ trợ của bạn, họ sẽ nhận được thông báo bật lên về vi-rút và phải đặt bí mật vĩ mô của bạn. Có thể có một công thức giải pháp??

Điều này có thể được thực hiện với một công thức duy nhất trong Excel 2016 trở đi

Trong khi giải pháp Pnuts liệt kê các ký tự rõ ràng rõ ràng để tách, giải pháp này liệt kê các ký tự rõ ràng rõ ràng

Use the bad data of you are in the column A. Giả sử bạn muốn xóa dữ liệu của mình trong cột B

Sử dụng công thức cuối cùng dưới đây, trong ô B1. Để nhập công thức vào ô B1, hãy làm như sau

• Click vào ô B1
• Bấm vào thanh công thức
• Dán công thức
• Bấm Ctrl+Shift+Enter

Sao chép ô B1 và ​​kéo nó xuống cột B theo ý muốn của bạn

Đầu tiên, đây là một ví dụ ngắn để giải thích những gì đang diễn ra

=TEXTJOIN("",TRUE,

IFs(
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "t", "t",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "e", "e",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "s", "s",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "T", "T",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "E", "E",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "S", "S",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "2", "2",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = " ", " ",
    true, ""
  )

)

Trong trường hợp này, tôi chỉ định các ký tự sau là hợp lệ. T, E, S, T, E, S, 2 và các ký tự không gian

Rõ ràng bạn cần mở rộng công thức ra bảng chữ cái đầy đủ, liệt kê bảng kê riêng từng ký tự trên và chữ thường riêng biệt. Tương tự như vậy, mở rộng nó ra để bao gồm các số nếu bạn muốn giữ chúng. Lưu ý rằng các số được mã hóa dưới dạng chuỗi

Làm thế nào điều này hoạt động

Tóm lại, chúng tôi đang chia chuỗi nguồn thành một mảng các ký tự riêng biệt, sau đó cho từng ký tự, kiểm tra xem nó có nằm trong tập hợp các ký tự hợp lệ của chúng tôi hay không và bao gồm nó trong kết nối

Hàm 'ifs' đi qua các đối số của nó một cặp tại một thời điểm. Nếu đối số đánh giá đầu tiên là đúng, thì đối số thứ hai được trả về. Nếu không, nó tiến hành với cặp đối số tiếp theo - đây là lý do tại sao bạn thấy các chữ cái được liệt kê hai lần trên mỗi hàng. Cặp giá trị cuối cùng trong hàm IFS là bộ 'true' và chuỗi trống. Điều này nói rằng nếu chúng ta đi đến cuối tập hợp các giá trị hợp lệ (tức là và không khớp với giá trị hợp lệ) thì hãy trả lại chuỗi trống

Thêm nền tảng về lý do tại sao điều khiển này đang hoạt động

Đây là một biến thể trên một giải pháp được đưa ra tại Exceljet. Trong giải pháp đó, hàm TextJoin được sử dụng (để kết hợp các giá trị của một mảng) với hàm gián tiếp (chia chuỗi thành một mảng) cùng với toán học toán học (biểu tượng cộng) để đánh giá tính toán giữa các phép toán . Các ký tự số trong chuỗi sẽ trả về các giá trị số trong khi các ký tự khác sẽ trả về lỗi. Giải pháp đó sử dụng hàm ISERR để kiểm tra lỗi quyết định có bao gồm một ký tự định nghĩa nhất trong đầu ra cuối cùng hay không. Một bài viết tương tự tồn tại ở đó để làm việc theo cách khác - để loại trừ các số và giữ các chữ cái

Vấn đề tôi muốn giải quyết là để mã hóa quyết định giá trị nào là hợp lệ và cái nào không. Tôi đã trải qua quá trình cố gắng kết hợp các hàm VLookup và Index với hàm gián tiếp nhưng chúng sẽ chỉ hoạt động trên các ký tự đầu tiên trong chuỗi. Bí quyết không phải là tất cả các chức năng sẽ hoạt động theo đầu ra của gián tiếp theo cách để đánh giá mọi yếu tố thành phần trong mảng (ví dụ:. Mỗi ký tự trong chuỗi). Bí mật là Exceljet đã sử dụng một toán tử toán học. Nếu bạn kiểm tra chức năng tham chiếu đầy đủ của Microsoft, IFS được phân loại là hàm "logic". Tôi nghi ngờ các hàm logic có thể được sử dụng với gián tiếp theo cách này

- 90 (chữ hoa) hoặc 97 - 122 (chữ thường) sẽ chỉ hoạt động nếu tất cả các ký tự trong chuỗi là chữ hoa hoặc tất cả đều là chữ thường, nhưng không phải nếu có hỗn hợp. )

Tôi không biết làm thế nào hiệu suất của giải pháp này so sánh với các đề xuất trước đó bằng cách sử dụng thay thế. Nếu bạn chỉ muốn loại bỏ một số ký tự, tôi khuyên bạn nên sử dụng giải pháp thay thế. Nếu bạn chỉ muốn xác định rõ ràng các ký tự hợp lệ để duy trì (đó là câu hỏi ban đầu), hãy sử dụng câu hỏi này

Cuối cùng, đây là câu trả lời chính xác mà bạn cần, bao gồm cả chuyển đổi sang chữ hoa mà bạn không lưu ý trong câu hỏi, nhưng được hiển thị trong ví dụ của bạn. . in list. )

=TEXTJOIN("",TRUE,
IFs(
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "A", "A",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "B", "B",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "C", "C",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "D", "D",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "E", "E",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "F", "F",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "G", "G",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "H", "H",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "I", "I",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "J", "J",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "K", "K",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "L", "L",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "M", "M",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "N", "N",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "O", "O",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "P", "P",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "Q", "Q",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "R", "R",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "S", "S",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "T", "T",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "U", "U",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "V", "V",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "W", "W",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "X", "X",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "Y", "Y",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "Z", "Z",
    true, ""
)
)

Với câu hỏi ban đầu là "bỏ tất cả trừ các ký tự alpha" - câu trả lời này thực hiện thủ thuật trong một công thức mà không cần VBA

Làm cách nào để lọc bảng chữ cái trong excel?

Select Data> Filter. Lấy bộ lọc văn bản hoặc bộ lọc số, sau đó chọn một bộ so sánh, như giữa. Nhập tiêu chí bộ lọc và chọn OK. Chọn Bộ lọc văn bản hoặc Bộ lọc số, sau đó chọn phép so sánh, chẳng hạn như Giữa. Nhập tiêu chí lọc và chọn OK .

Làm thế nào để bạn thiết lập văn bản trong excel?

Đây là cách thực hiện điều này. .

Chọn các ô nơi bạn có văn bản

Chuyển đến dữ liệu -> Công cụ dữ liệu -> Văn bản đến cột

Trong bản văn để định hướng cột Bước 1, chọn phân định và nhấn tiếp theo

Trong bước 2, hãy kiểm tra tùy chọn khác và nhập @ vào hộp ngay vào đó

Ở bước 3, cài đặt chung hoạt động tốt trong trường hợp này

Select on end

Làm cách nào để loại bỏ các số và giữ bảng chữ cái trong Excel?

.(2) Hàm trích xuất này cũng sẽ loại bỏ tất cả các loại ký tự ngoại trừ các ký tự số.nhập trực tiếp công thức =EXTRACTNUMBERS(A2,TRUE) vào ô đã chọn, rồi kéo núm điều khiển Điền vào phạm vi mà bạn cần . (2) Hàm EXTRACTNUMBERS này cũng sẽ xóa tất cả các loại ký tự ngoại trừ ký tự số.

Làm thế nào để tôi chỉ cho phép văn bản trong excel?

Hạn chế nhập dữ liệu. .

Chọn các ô mà bạn muốn giới hạn chế độ nhập dữ liệu

Trên tab Dữ liệu, nhấp vào Xác thực dữ liệu > Xác thực dữ liệu

Trong hộp cho phép, chọn loại dữ liệu bạn muốn cho phép và điền vào các tiêu chí và giới hạn giá trị