Bài viết: Cách viết một công thức dài và phức tạp

Liên hệ QC

kyo

Nguyễn Khắc Duy
Thành viên danh dự
Tham gia
4/6/06
Bài viết
901
Được thích
2,714
Cách viết một công thức dài và phức tạp


Sẽ có rất nhiều lúc bạn lướt giaiphapexcel, và bạn sẽ thấy có những công thức rất dài. Bạn chợt nghĩ, trông nó thật phức tạp và có lẽ bạn sẽ không bao giờ có thể viết được nó. Thật sự thì, tôi tin rằng kể cả các cao thủ của GPE, rất ít người có thể viết nổi nếu viết liền một mạch. Tuy nhiên, để có thể hoàn tất một công thức dài, chúng ta sẽ cần phải viết từ từ, từng chút một. Và bài viết này muốn hướng dẫn bạn cách đi từng bước để có được các loại công thức như thế này.

Để cụ thể hơn, chúng ta cùng bước vào một ví dụ. Bạn hãy nhìn hình dưới đây, chúng ta có một bài tập tách hàng bao gồm các ký tự đầu, và tách số cây thông qua những con số phía sau.

VfJ2rTtRJl0Uy291Guze7IvfMIu9HgN2LfXDDUZx10BLTGva-sljb5qOCZugW7_QuvZNL46gwwQDstLIHfrnlOLhSvWtSmzxmEuOT2WLQWzizYuTm5GXPLxBL36cTrrLvohSidztnJD125CwHGj53Tuh-BDd3sqTxIfheE8i2zZjK6KQcE9yFG2ULR2fRaVHjW9NuMjm9jhSyXyu9TwJ4Escl71K8QJz-l9IWrozaw2owfC3Uu1tT4awsgv9fQAxbcVjOiWzf5G5R-Ftd8sytKT9rYT6uKLF0GBYeK5J4CRKgdjl_gPJC0KP0SroFCjn3ms2K9LgAhhOmWClMQQOOZ-Mc0k7j8bx1Z1nLv20R9CVAlQe2WTb_Dg_dOvSj1wZdDRtriVcnLhFj7j20xxKm_zPVqgJ08qvTSAx5mneHMumbkVxq7_9HsL0WsBVWNw4ebadQANLn-U_FCUdmJJG4Zge-lnR6NHiiIGEj5jNiwtq6S91qh-NnHb9SkLxCKa-jgVU-ibY9hl1WsvZBBNTM2xHb5zXzWs4PHyOhEwrHwukEWnrfqjwjm1aL4-RP3WVgrx9nQnKfd1juFkykhqh6WoIQqO9bwpGa_3OlhDAOcEwILUVldzQ=w514-h150-no


Trước tiên, khi bắt đầu một bài gì đó, bạn hãy phân tích hướng giải trước. Ở đây, một nguyên tắc chung mà chúng ta có thể thấy, chính là:
- Bước 1: Biến toàn bộ dấu chấm thành khoảng trắng (tốt nhất là càng nhiều càng tốt, 1000 khoảng trắng chẳng hạn).
- Bước 2: Dùng hàm LEFT lấy khoảng 1000 ký tự từ trái qua, lồng thêm hàm TRIM để lấy hàng.
- Bước 3: Dùng hàm RIGHT lấy khoảng 1000 ký tự từ phải qua, lồng thêm hàm TRIM để lấy số cây.

Sau khi bạn đã biết được hướng đi, bước tiếp theo bạn nên làm đó chính là ráp từng công thức một để cụ thể hóa nguyên tắc trên.

Biến toàn bộ dấu chấm thành rất nhiều khoảng trắng

Ở đây, để biến toàn bộ dấu chấm thành khoảng trắng, tôi nghĩ ngay đến hàm SUBSTITUTE. Từ đó, tôi tiến hành thử nghiệm ngay tại ô B4 như sau: =SUBSTITUTE(A4,"."," ").

WxFswvhStvhw9em_XxLguyuXViQXpBdnu61i80sb8RMBvxFf_dt9tqvIY-G8wuuhgupEb93vVUenIOyaaeR0rey1Iex4nk4KTY_228yBHi-cXIEhs8JbKWi3uP_pwTbPowLYJBqkik1DyfQN4EeuEaFX3T2RYMVd4OiLJJk-TFPsYd1tpgVWDuYlE_STHb-VQM8xDfgxcbvugTSoXqY9Xf4py4GPODsh4OhMw5yA_ekbsgS7QYowhRjSlq2D6sDtxYcBJHYEeQJft5AKx95T9ZwHo781QOZGQe_uzgtGVbVHgQlutxALP9i3twh406VT5Li35MkqVsY29hlFpijsujyFeSo43-KgOFDUerQn4sJnV_onO-_fkxlpVz67Xo-inP4U3jQVUjIuG6z-Tz2aOSCwvrGGSfy2RyxTPKffwfseSMquaeyESnhAmdodxmQvTrxaRRPJ0buv3_yi5IwKd-J1s70SrZi8K-QHqChMLplpyCU_KLHP4PO14MighPS9hi8qSjtX-DQD0eBqtPJuw_q78g61FYha2Kgd6tPZBJT7EKYZ4YFHic4dQUawZzkG7YcptBbQcQ03OyCHU0WSY-w57He_5VbMhRbgjd9Z9gH63ouHp_bb=w812-h245-no


Tuy nhiên, khi bạn quan sát kết quả, bạn sẽ thấy tất cả các dấu chấm không được thay thế hoàn toàn. Đó là bởi vì khi bạn bấm các dấu chấm, nếu có 3 dấu chấm liên tiếp, Excel sẽ tự động chuyển nó thành ký tự ba chấm (lưu ý đây là một ký tự ba chấm, không phải lá 3 ký tự dấu chấm). Do vậy, nó khiến cho quá trình chuyển đổi không hoàn thiện. Vì vậy, chúng ta phải tiếp tục đổi thêm ký tự ba chấm này nữa cũng với hàm SUBSTITUTE như sau tại ô C4: =SUBSTITUTE(B4,"…"," ").

SxLW0gAEEsu2d2osl0VUG_E_A-sDxb60g0yxwlVwUuM3ccB64FUfl0MkK3UUCDafWhIvWBlPUbd1rhKmwqbCQGd6bpItX7VGO8dFDp8sdAipBhthlk6Iv_3PXhze7pwbggGssOoiybms5BfGMy3gOl94ZsEwZ_L3dhF9WcuDeDstX0ZWlF5bFD1E6cTZ6oSrZ3J79rsqtbe1IRvAhPQAyDol34Xj00fr6XKc-rKbm_3OoPViFr8kkiEtGs9lfNFzii-pOpVBM9_Tw9CkPPZz-vWcr3IhwwzvOaaasevUe1sqHikZ8pXxubJR71nUt0T8OvG_aCk4j492RzHVHZrmCqRoUiSgtFjG5K3DKDrwm3CuKs_mUGmVz06AY1Sa6J_cYZ8yAgZqRrKk5xV-Pn3kyku2zRwKIttXXHESv4KvbP65VEjHuqh1KCdvz0HDQeFF069UMXVjYueEZmk7WZML1RQeeIJKldyvgX3lzkYdnBn2WEfcX5VwAVu8rpRMlbROzrii_2F8DAzltG2dKOb8S3VUK9y917UnPzaMF-D291uUz9cTkDxHoKZQL324B0vNYKP0aWgalMzZXDklwh5xw5Xe1wXfsZG33lRW-wJqcnBmQjhGx01n=w1470-h218-no


Bạn thấy kết quả rồi chứ? Tất cả dấu chấm của bạn đều được chuyển hết thành khoảng trắng. Tuy nhiên, có một lưu ý rằng, bạn nên sử dụng hàm CHAR(133) thay vì … để tránh sơ xuất nếu Excel không tự động chuyển 3 dấu chấm liên tiếp của bạn thành ký tự ba chấm. Vì vậy, công thức của bạn sẽ trở thành: =SUBSTITUTE(B4,CHAR(133)," ")

Dùng hàm LEFT lấy khoảng 1000 ký tự từ trái qua, lồng thêm hàm TRIM để lấy hàng

1000 ký tự chỉ là con số phỏng đoán thôi bạn nhé, đại khái bạn có thể hiểu rằng, bạn cố ý tạo nên rất rất nhiều khoảng trắng, sau đó dùng hàm LEFT để ước lượng một con số khá lớn để Excel lấy giúp bạn tất cả ký tự sẽ có ở bên trái. Cụ thể ở đây, tôi phân tích là bài toán của tôi không thể nào lấy quá 10 ký tự được, do đó tôi dùng hàm LEFT lấy 10 ký tự bằng cách: =LEFT(C4,10)

NYbgLrFm1QHWMb564P8KYcqDgEPcrdlVw_CVD93u-ODR0HlU5uliBTgHvxvaz8LypDOjA_jbWgBrF0mz28UHwSFu9Q52JfCtLtQGC92Q8QK3TQy79TeqrhcRzSmQtxPIc3nwZucezpckr14j3MqZr67QBcEIULSIQZgNylAkZdb-XxgDDsLSM3s6hU55GXUZwjtZZWAgxfd4h_GZJMBukUzp3xRHLkahmVjbeDouL6sxd0GbRTEJ03SJBZUr8Tnl5OAOWO3GqURKNRuIMpOZRZJ57UIhSTKh9VinlLBfXleh2CJoF1KHMYEpR8Zhqdj8wcNkGAi1uY1RRuAUnqIO53TKYGYi5hW6LOg-H-vgWzxciRtbJnHVfBvhu8aywseBES5r7b9apjE7k9VOOnYD7oX41nz2E6hISXgfVomLKcFsQ2bXm3dtwLFOlLRyYgfg6NtaDZlB5xKJ6KLHZIgyEifuK363iFy2kVZXx0-iAkd5x5JvsaAFan5qrg6p0f4lyFVqVbhqu5HfpB-9JxCcGCpWR9ubMJGgyRSKhL48VyMwYTD5kdcX6izoZkyanIS1HhvWgKGRQAcsvBzRh3LfH3DBTm24OQoY3oM00Uigjmoz9fbVMaGJ=w937-h163-no


Tuy nhiên, chắc chắn một điều ô D4 của bạn sẽ bao gồm cả khoảng trắng, bởi vì "H1” chỉ có 2 ký tự mà thôi (và đây cũng chính là chủ đích khi tách chuỗi). Do đó, bạn cần dùng tiếp hàm TRIM để cắt khoảng trắng đi. Bạn hãy lồng tiếp vào nhé: =TRIM(LEFT(C4,10))

Dùng hàm RIGHT lấy khoảng 1000 ký tự từ phải qua, lồng thêm hàm TRIM để lấy số cây

Tương tự cho phần còn lại, bạn dùng hàm RIGHT với phỏng đoán tương đối, ở đây tôi có thể phỏng đoán khoảng 20 ký tự, hoặc để chắc ăn, bạn dùng hàm LEN và trừ đi độ dài ký tự bạn tách từ hàm LEFT phía trên để bảo đảm phần còn lại của chuỗi đã được bạn lấy hết. Bạn có thể đặt công thức từ từ vào các cột kế tiếp: =RIGHT(C4,LEN(C4)-LEN(D4))

uvd7C99Sc6a2JYaCHcTrarEDvHKGT4G17uEDIskbhgRgWxKjA5Yfegh3kII8e5j-prqKc9ZrSGHXP3Knwg9gtK_kQvX-eEwfoPo-F5H8f03Z2zaH9V0woJ4BeY6gRssKfGBrCxwMvpeIkzZMLTHggMtGLp9q8axJroL8vWhBf_t4BmdAt6txjo6b4B6cf9G07cB5UxnHie2XrAQzYMEk70VdhYUQolGaM9h68WK_r6lcUSrZukYDA6PnRwAJec0UXRmYaJqgUK5XI6_Q_DJDbbd-KN9uUN_e3XKNOWgrxd5zqY0yCMm8Rabyk7tsPqCU7RthV7_55Nhn3iUDSdWTxJYHO_97mWgVFu-Ffwbi71DjJTdw92ha75UrHqzqR3T6MJC_D_maMkRo85LcL_aVIK1-dm8W3o-DdLOtI07N4uJ-F004ddKEqJMsDFE1nJNu7BkKfoGTOvZzn53GT2gBHJUM3OfIlOjJvxlDMprlOZ8lwGtuWqUhZRDOWh3heIalWh5iA2RrvDhovm0aMCUnB_vIvOPb1aEZSelRK6GC6PPzlUWYZL5LnXALPRS3xZHtjspCNkDVIkfyxLlPUfZ7YlnfIctJIAKVLgLYYAMLegAm2_pPtQnN=w1470-h167-no


Sau đó bạn dùng TRIM để cắt khoảng trắng: =TRIM(RIGHT(C4,LEN(C4)-LEN(D4)))

Vậy là bạn đã có kết quả rồi đấy. Và tôi fill công thức xuống cho các dòng còn lại, tôi thu được kết quả khá ổn.

CHElpIeucCci9jV9jYyLmWAHMMgHz4XV6s_naAlCmPSt7Q8QpNNAvsjrP-O5n7ciB7dIIdW1molYa_l4TKlTitbZ16h2QLnB5OYsCMsURr2uu45gWUQfaG6rvCBB5AwdhD11dKhO8MpxXPrRbtNsRE0-6XR-IKjN--kZwyM0LvXrDhbYyBneL73byOON3q6ngs8PDUlqgyhN-KQ0-JNxjpJMlpgKlcJHmwudsr_LuuAltu1rl5EzH2bWpLzBa48eufM5EyfSsfBlcZuQC55Tc0PzXXrS1dh9KTfbPvKuULV61K_O1Qqr3qiF2uLVD0GFV6Rdy8JDZe6hWheek6Ow6lv-xkNZe_bAcMkNMC-cyZw24n2MErj3kHwRDHtSqOQbPAiqx4gBbGzF-8LU2fBK4CnrnBwQquVZKSlAmsOHveY-F6fD3FBtk9eT5QiYwenzDY8cMdRDTnfxiaQdht7ADNgSaS2ywUkIH2-FPGM4xkIjeLM0SdSkd-k2CI3R3gK4qa8e98biXX1cA1mM3ZvkkmA509MEn6mU7foJEfjOm29N7eqmxYCNX8CBhKzljwOjJ9KH-hAdiICY958YgcgBnMhkFI_zVMLMZ2pkSHc_obg_QdEOHieo=w731-h214-no


Bây giờ, sau thử nghiệm, chúng ta sẽ ghép các công thức lại làm một và sẽ có được một công thức hoàn chỉnh như sau:
- Hàng: =TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A4,"."," "),CHAR(133)," "),10))
- Số cây: =TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A4,"."," "),CHAR(133)," "),LEN(SUBSTITUTE(SUBSTITUTE(A4,"."," "),CHAR(133)," "))-LEN(B4)))

ar3bH1B0GoBWn0o6dZirgXJ3vgCDXMUWk3FoZ348svywWqR-XnS7hgHuphnwRrtO25jXNu_Vtj8yzbaH-cJzvZFPVugv24FiGYStNHTh96zNji3dOiV1Zui3gL19ezgp5oGkM7WEIcFoZZsp9oQoYStt07VmzJtSaXEKuhTG0KpwLvO3z9BjZ8sCQjU-P8hHaeh-rvXeCiU6LY63LydZX11D5RHpCpI7Byi9HUrE0ol0oVgmtKykgQvS80SshQbDRa0S1FgBUNeEggbYSVkV24Ds9q8vzfwZvkMe13pKuc3ASyMn-zQc4ueCjz00VjDGQNqINEneOCU0B_XpRy_nCtb6hp0HiWGvv-l3CPYKaWxw_CHobQYKjM7ZvOD11fa-qau3ueG-6m5hvZoDMJ-jFx2Pw1NKq3SClSwtQOVueyMMw-JAxHJZ4UC_0jRF3FYIXzDpQ0CvcDe41X0HJM4Ctx5y6lhYRzYCKqWligP1fKNeU5J0HNgv5z1x-snf89JJGHROEwynhcB0aRZ6Urdtv4PrUKje0CzXAwBzEd1wQw_yXEgK77YJCK-weBNJ1CIYfMt7Rt9N35qA2pkJ8Qd5YzgSKNn41p4C1TxBXs84ZzmhnHU-CRii=w997-h240-no


Nhìn chung ví dụ này hãy còn khá đơn giản nhưng cũng đủ khiến cho công thức trông có vẻ dài và thực sự thì, có rất nhiều người nhìn thấy độ dài này là cảm thấy choáng ngợp, nhưng nếu cẩn thận phân tích, làm từng bước thì nó lại không hề khó chút nào. Vì vậy, khi gặp một bài toán nào đó, bạn hãy từ từ phân tích, làm từng bước, thử từng hàm một rồi hẵng ghép với nhau, đừng quá nôn nóng viết một công thức dài, vừa dễ sai, khó sửa và lại khiến bạn mau nản.

Chúc bạn thành công!

Một số bài viết có liên quan:
1/ Chiêu 38: Thêm dữ liệu vào danh sách Validation một cách tự động
2/ Sử dụng các nút điều khiển (Form Controls) trong một bảng tính
3/ Sử dụng hộp thoại GoTo Special
4/ Tổng hợp dữ liệu trên nhiều bảng tính
5/ SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện
6/ Sử dụng các công cụ tạo mô hình kinh doanh của Excel (phần 3)
7/ Sử dụng các công cụ tạo mô hình kinh doanh của Excel (phần 2)
8/ Sử dụng các công cụ tạo mô hình kinh doanh của Excel (phần 1)
9/ Excel nâng cao: Sử dụng sự lặp lại và các tham chiếu tuần hoàn
10/ Làm việc với công thức mảng trong Excel
 
Lần chỉnh sửa cuối:
Upvote 0
Sao lại phức tạp vậy. Đầu tiên dùng char(133) để đổi "..." thành " ". Sau đó dùng lệnh trim rồi dùng text to columns cũng được kết quả như vậy mà.
 
Sao lại phức tạp vậy. Đầu tiên dùng char(133) để đổi "..." thành " ". Sau đó dùng lệnh trim rồi dùng text to columns cũng được kết quả như vậy mà.

Cám ơn bạn đã góp ý, tuy nhiên bài viết này không hướng tới việc tối ưu hóa công thức hay kết hợp các công cụ khác nhau. Cái tinh thần kyo muốn truyền tải là cách viết, đó chính là phải tìm được hướng đi và viết từ từ, thử sai từ từ với chi tiết và tổng quát hóa nó để áp dụng cho các trường hợp. Và khi học được kỹ năng đó rồi thì các công thức khác sẽ được viết dễ dàng hơn.

kyo
 
Cám ơn bạn đã góp ý, tuy nhiên bài viết này không hướng tới việc tối ưu hóa công thức hay kết hợp các công cụ khác nhau. Cái tinh thần kyo muốn truyền tải là cách viết, đó chính là phải tìm được hướng đi và viết từ từ, thử sai từ từ với chi tiết và tổng quát hóa nó để áp dụng cho các trường hợp. Và khi học được kỹ năng đó rồi thì các công thức khác sẽ được viết dễ dàng hơn.

kyo
cảm ơn bạn kyo rất nhiều
 
Nếu con số 254 có thể là số thập phân, VD: 254.4, thì sẽ phức tạp hơn.
 
Nếu con số 254 có thể là số thập phân, VD: 254.4, thì sẽ phức tạp hơn.

SỐ CÂY mà cũng có thập phân nữa sao Bebo? 254.4 cây là 254 cây 4 chỉ chắc? --=0 (nhiều quá... cất nhà mua xe và cưới vợ.. bé được rồi)
----------------------------------
Nói chung bài này người ta HƯỚNG DẪN CÁCH VIẾT MỘT CÔNG THỨC DÀI sao cho người mới học khỏi lúng túng chứ không phải cố tình muốn giải quyết bài toán tách chuỗi
 
ADD ƠI. BÂY GIỜ DÃY SỐ CỦA TỚ THẾ NÀY THÌ LÀM NHƯ NÀO NHỈ
1/1 123456 1234567890 KHÓ LÀM QUÁ
TỚ MUỐN TÁCH THÀNH CÁC CỘT KHÁC NHAU
vd: 1/1 CỘT 1, 123456 CỘT 2, 1234567890 CỘT 3, KHÓ LÀM QUÁ CỘT 4 ????
THANK YOU !
 
Web KT
Back
Top Bottom