Tính đơn giá trong Excel

Deal Score-1
Deal Score-1

Trong nhiều trường hợp, việc xác định đơn giá (hoặc tính đơn giá) thường khiến chúng ta cảm thấy khó khăn. Vì điều này đòi hỏi bạn phải sử dụng thành thạo các hàm, biết cách tổ chức bảng dữ liệu và có tư duy logic tốt. Để giúp bạn thực hành những điều này, hãy cùng phanmemfree chúng tôi tìm hiểu 4 cách xác định đơn giá dựa trên nhiều điều kiện trong excel.

Ví dụ: chúng tôi có bảng dữ liệu bán hàng sau:

Tính đơn giá trong Excel

Đơn giá của từng sản phẩm được xác định dựa trên 2 yếu tố là mã hàng và mã công ty.

Yêu cầu là xác định đơn giá có giá trị tương ứng trong khu vực G3: G9 dựa trên mã công ty kết hợp với mã hàng trong bảng đơn giá.

Phương pháp 1: Kết hợp hàm Vlookup và hàm MATCH

Bài viết tham khảo: Cách kết hợp hàm vlookup và hàm Match khi tìm kiếm theo nhiều cột chứa kết quả

Khi xem xét nhiều điều kiện (từ 2 điều kiện trở lên) để báo một giá trị (đặc biệt là đơn giá ở đây), chúng ta cân nhắc sử dụng hàm Vlookup kết hợp với hàm MATCH.

Cốt lõi của phương pháp này là bạn phải xác định giá trị VLOOKUP và giá trị MATCH nào. Cuối cùng, chúng tôi nhận thấy cấu trúc của bảng đơn giá:

Cấu trúc như sau: VLOOKUP (mã công ty, bảng đơn giá, khớp (mã hàng, mã hàng, 0), 0)

Khi thay thế nội dung bằng tọa độ tham chiếu, chúng ta có:

Tính đơn giá trong Excel

Phương pháp 2: Kết hợp hàm HLOOKUP và hàm MATCH

Hàm HLOOKUP cũng là một hàm tìm kiếm tương tự như hàm VLOOKUP, chỉ khác là nó thay đổi hướng và hướng tìm kiếm. Trong trường hợp này, chúng tôi lập luận như sau:

Cấu trúc như sau: GIỜLOOKUP (Mã hàng, Bảng đơn giá, MATCH (Mã Công ty, cột Mã Công ty, 0), 0)

Khi thay thế nội dung bằng tọa độ tham chiếu, chúng ta có:

Tính đơn giá trong Excel

Phương pháp 3: Sử dụng hàm INDEX và MATCH

Bạn có biết rằng sử dụng Chỉ mục + Đối sánh tốt hơn sử dụng Vlookup hoặc Hlookup không? Nếu bạn không bị thuyết phục, vui lòng kiểm tra bài viết này:

Tại sao sử dụng INDEX và MATCH trong Excel tốt hơn là sử dụng hàm VLOOKUP

Trong trường hợp này, chúng ta hãy tìm hiểu cách sử dụng INDEX MATCH.

Khi nói đến INDEX MATCH, hãy nhớ 3 yếu tố:

Cấu trúc cụ thể: INDEX (Bảng dữ liệu, MATCH (Mã công ty, Mã công ty cột, 0), MATCH (Mã hàng, Mã mục hàng, 0))

Khi thay thế nội dung bằng tọa độ tham chiếu, chúng ta có:

Tính đơn giá trong Excel

Phương pháp 4: Sử dụng hàm SUMPRODUCT để xác định đơn giá dựa trên nhiều điều kiện

Hàm SUMPRODUCT là một hàm có thể được sử dụng trong nhiều trường hợp. Và chúng ta hoàn toàn có thể áp dụng chức năng này để xác định đơn giá trong nhiều điều kiện. như sau:

Khi thay thế nội dung bằng tọa độ tham chiếu, chúng ta có:

Tính đơn giá trong Excel

Cả 4 phương pháp đều cho kết quả giống nhau.

Bạn thấy đấy, có nhiều cách khác nhau để thực hiện một yêu cầu trong Excel. Có rất nhiều chức năng có thể được sử dụng và có liên quan với nhau để giúp chúng ta đạt được kết quả mong muốn. Tìm hiểu thêm về Excel với phanmemfree.

43 Comments
  1. I was just searching for this information for a while. After 6 hours of continuous Googleing, at last I got it in your site. I wonder what’s the lack of Google strategy that do not rank this kind of informative websites in top of the list. Usually the top websites are full of garbage.

  2. Only wanna input on few general things, The website style is perfect, the subject matter is really wonderful. “In business school classrooms they construct wonderful models of a nonworld.” by Peter Drucker.

  3. very good post, i certainly love this web site, keep on it

  4. Thank you for sharing superb informations. Your site is very cool. I’m impressed by the details that you’ve on this blog. It reveals how nicely you understand this subject. Bookmarked this web page, will come back for more articles. You, my pal, ROCK! I found just the information I already searched everywhere and just couldn’t come across. What a perfect web site.

  5. Very well written post. It will be supportive to anybody who employess it, as well as myself. Keep up the good work – for sure i will check out more posts.

  6. great issues altogether, you just won a new reader. What may you recommend in regards to your submit that you made some days in the past? Any positive?

  7. Very interesting topic, thanks for putting up.

  8. This web site is my inspiration , really wonderful style and perfect written content.

  9. hello there and thank you for your info – I have definitely picked up something new from right here. I did however expertise some technical issues using this website, since I experienced to reload the web site many times previous to I could get it to load correctly. I had been wondering if your web hosting is OK? Not that I am complaining, but slow loading instances times will very frequently affect your placement in google and could damage your high quality score if ads and marketing with Adwords. Anyway I’m adding this RSS to my e-mail and can look out for a lot more of your respective exciting content. Ensure that you update this again very soon..

  10. An impressive share, I just given this onto a colleague who was doing a little analysis on this. And he in fact bought me breakfast because I found it for him.. smile. So let me reword that: Thnx for the treat! But yeah Thnkx for spending the time to discuss this, I feel strongly about it and love reading more on this topic. If possible, as you become expertise, would you mind updating your blog with more details? It is highly helpful for me. Big thumb up for this blog post!

  11. I also believe thus, perfectly written post! .

  12. Great tremendous things here. I am very satisfied to look your article. Thanks a lot and i’m having a look forward to touch you. Will you please drop me a e-mail?

  13. Very interesting subject, appreciate it for putting up.

  14. Whats up very nice blog!! Guy .. Excellent .. Wonderful .. I’ll bookmark your blog and take the feeds additionally…I’m satisfied to seek out numerous helpful info here within the submit, we want work out extra strategies in this regard, thanks for sharing.

  15. Wow, incredible blog format! How lengthy have you been running a blog for? you made blogging glance easy. The whole look of your web site is fantastic, let alone the content material!

  16. There is noticeably a bundle to know about this. I assume you made certain nice points in features also.

  17. I am glad to be one of several visitants on this great web site (:, thankyou for putting up.

  18. I went over this web site and I think you have a lot of excellent info , saved to bookmarks (:.

  19. Nice read, I just passed this onto a colleague who was doing a little research on that. And he just bought me lunch because I found it for him smile So let me rephrase that: Thanks for lunch!

  20. I think other web site proprietors should take this site as an model, very clean and excellent user friendly style and design, as well as the content. You’re an expert in this topic!

  21. I will right away grab your rss feed as I can’t to find your email subscription hyperlink or newsletter service. Do you have any? Kindly allow me realize in order that I could subscribe. Thanks.

  22. Excellent website. A lot of useful info here. I am sending it to a few pals ans also sharing in delicious. And naturally, thank you to your effort!

  23. Thanks a lot for sharing this with all of us you really know what you are talking about! Bookmarked. Kindly also visit my site =). We could have a link exchange arrangement between us!

  24. My coder is trying to persuade me to move to .net from PHP. I have always disliked the idea because of the costs. But he’s tryiong none the less. I’ve been using WordPress on several websites for about a year and am nervous about switching to another platform. I have heard great things about blogengine.net. Is there a way I can import all my wordpress posts into it? Any kind of help would be greatly appreciated!

  25. F*ckin¦ amazing issues here. I am very glad to peer your post. Thanks so much and i am taking a look ahead to touch you. Will you please drop me a e-mail?

  26. I feel that is one of the so much significant information for me. And i’m glad studying your article. But want to statement on few common issues, The web site taste is perfect, the articles is in point of fact great : D. Excellent task, cheers

  27. You actually make it seem so easy with your presentation but I find this matter to be really something that I think I would never understand. It seems too complicated and extremely broad for me. I am looking forward for your next post, I’ll try to get the hang of it!

  28. Wonderful beat ! I would like to apprentice while you amend your web site, how could i subscribe for a blog site? The account aided me a acceptable deal. I had been tiny bit acquainted of this your broadcast provided bright clear idea

  29. Great work! This is the type of information that should be shared across the internet. Disgrace on the seek engines for now not positioning this submit higher! Come on over and visit my web site . Thank you =)

  30. Thank you for the sensible critique. Me & my neighbor were just preparing to do some research about this. We got a grab a book from our area library but I think I learned more from this post. I am very glad to see such excellent info being shared freely out there.

  31. I really like your writing style, superb info, appreciate it for putting up :D. “In every affair consider what precedes and what follows, and then undertake it.” by Epictetus.

  32. I simply couldn’t go away your website before suggesting that I really enjoyed the standard info a person provide for your guests? Is going to be back ceaselessly in order to inspect new posts

  33. I got what you intend,saved to favorites, very nice site.

  34. Excellent blog here! Also your web site loads up fast! What host are you using? Can I get your affiliate link to your host? I wish my website loaded up as fast as yours lol

  35. I am continuously invstigating online for ideas that can facilitate me. Thanks!

  36. I went over this site and I conceive you have a lot of wonderful info, saved to fav (:.

  37. As I website possessor I conceive the articles here is real great, thankyou for your efforts.

  38. Hi there, I discovered your blog by the use of Google at the same time as looking for a related topic, your site came up, it appears to be like great. I’ve bookmarked it in my google bookmarks.

  39. Oh my goodness! an incredible article dude. Thanks Nonetheless I’m experiencing situation with ur rss . Don’t know why Unable to subscribe to it. Is there anyone getting equivalent rss drawback? Anyone who is aware of kindly respond. Thnkx

  40. My partner and I absolutely love your blog and find most of your post’s to be exactly what I’m looking for. Do you offer guest writers to write content to suit your needs? I wouldn’t mind composing a post or elaborating on many of the subjects you write related to here. Again, awesome web site!

  41. Great blog! I am loving it!! Will be back later to read some more. I am bookmarking your feeds also

  42. Great write-up, I am regular visitor of one?¦s blog, maintain up the nice operate, and It is going to be a regular visitor for a lengthy time.

Leave a reply

Phần mềm FREE