{"id":10429,"date":"2025-01-14T04:10:39","date_gmt":"2025-01-14T04:10:39","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/14\/ung-dung-ham-lam-tron-so-trong-tinh-don-gia-xuat-kho-tren-excel\/"},"modified":"2025-01-14T04:10:39","modified_gmt":"2025-01-14T04:10:39","slug":"ung-dung-ham-lam-tron-so-trong-tinh-don-gia-xuat-kho-tren-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/14\/ung-dung-ham-lam-tron-so-trong-tinh-don-gia-xuat-kho-tren-excel\/","title":{"rendered":"\u1ee8ng d\u1ee5ng h\u00e0m l\u00e0m tr\u00f2n s\u1ed1 trong t\u00ednh \u0111\u01a1n gi\u00e1 xu\u1ea5t kho tr\u00ean Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Ch\u1eafc h\u1eb3n khi\u00a0t\u00ecm hi\u1ec3u\u00a0v\u1ec1 c\u00e1c h\u00e0m l\u00e0m tr\u00f2n s\u1ed1 trong Excel th\u00ec ch\u00fang ta th\u01b0\u1eddng th\u1eafc m\u1eafc kh\u00f4ng bi\u1ebft nh\u1eefng h\u00e0m \u0111\u00f3 \u0111\u01b0\u1ee3c \u1ee9ng d\u1ee5ng nh\u01b0 th\u1ebf n\u00e0o trong c\u00f4ng vi\u1ec7c. Trong b\u00e0i vi\u1ebft n\u00e0y ch\u00fang ta h\u00e3y c\u00f9ng H\u1ecdc Excel Online t\u00ecm hi\u1ec3u c\u00e1ch \u1ee9ng d\u1ee5ng h\u00e0m l\u00e0m tr\u00f2n s\u1ed1 trong\u00a0t\u00ednh \u0111\u01a1n gi\u00e1\u00a0xu\u1ea5t kho tr\u00ean Excel nh\u00e9:<\/p>\n<h2>L\u00e0m tr\u00f2n \u0111\u01a1n gi\u00e1 xu\u1ea5t kho c\u1ee7a\u00a0ph\u01b0\u01a1ng ph\u00e1p b\u00ecnh qu\u00e2n<\/h2>\n<p>Khi t\u00ednh \u0111\u01a1n gi\u00e1 xu\u1ea5t kho theo ph\u01b0\u01a1ng ph\u00e1p b\u00ecnh qu\u00e2n th\u00ec r\u1ea5t d\u1ec5 x\u1ea3y ra tr\u01b0\u1eddng h\u1ee3p \u0111\u01a1n gi\u00e1 xu\u1ea5t kho b\u1ecb ra k\u1ebft qu\u1ea3 l\u00e0 s\u1ed1 l\u1ebb c\u00f3 ph\u1ea7n th\u1eadp ph\u00e2n. Do \u0111\u00f3 khi l\u00e0m vi\u1ec7c tr\u00ean Excel th\u00ec ch\u00fang ta c\u1ea7n ph\u1ea3i k\u1ebft h\u1ee3p v\u1edbi h\u00e0m l\u00e0m tr\u00f2n s\u1ed1 khi t\u00ednh \u0111\u01a1n gi\u00e1 xu\u1ea5t kho theo ph\u01b0\u01a1ng ph\u00e1p n\u00e0y.<\/p>\n<p>V\u00ed d\u1ee5 nh\u01b0 sau:<\/p>\n<p>M\u1eb7t h\u00e0ng A c\u00f3 th\u00f4ng tin nh\u1eadp xu\u1ea5t kho nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-19895\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/du-lieu-kho-n-x.png\" width=\"661\" height=\"301\" title=\"du-lieu-kho-n-x\"><\/p>\n<p>Trong PXK01 c\u1ea7n x\u00e1c \u0111\u1ecbnh \u0111\u01a1n gi\u00e1 xu\u1ea5t kho \u0111\u1ec3 t\u00ednh ra gi\u00e1 tr\u1ecb th\u00e0nh ti\u1ec1n. Tr\u01b0\u1edbc \u0111\u00f3 c\u00f3 2 l\u1ea7n nh\u1eadp \u1edf c\u00e1c PNK01 v\u00e0 PNK02. Tr\u01b0\u1eddng h\u1ee3p n\u00e0y t\u00ednh nh\u01b0 sau:<\/p>\n<p>\u0110\u01a1n gi\u00e1 xu\u1ea5t kho b\u00ecnh qu\u00e2n = T\u1ed5ng gi\u00e1 tr\u1ecb h\u00e0ng t\u1ed3n kho tr\u01b0\u1edbc khi xu\u1ea5t \/ T\u1ed5ng s\u1ed1 l\u01b0\u1ee3ng h\u00e0ng t\u1ed3n kho c\u00f2n l\u1ea1i tr\u01b0\u1edbc khi xu\u1ea5t<\/p>\n<p>Trong \u0111\u00f3:<\/p>\n<div class=\"code-block code-block-3\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<p>D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, nh\u01b0ng k\u1ef9 n\u0103ng Excel v\u1eabn c\u1ef1c k\u1ef3 quan tr\u1ecdng v\u1edbi k\u1ebf to\u00e1n, b\u1ea1n \u0111\u00e3 v\u1eefng Excel ch\u01b0a? H\u00e3y \u0111\u1ec3 t\u00f4i gi\u00fap b\u1ea1n, \u0111\u0103ng k\u00fd kho\u00e1 h\u1ecdc Excel:<\/p>\n<p><a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/ex101_92019-nho-167.gif\" title=\"ex101_92019-nho-167\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/ex101_92019-nho-167.gif\" title=\"ex101_92019-nho-167\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<ul>\n<li>T\u1ed5ng gi\u00e1 tr\u1ecb h\u00e0ng t\u1ed3n kho tr\u01b0\u1edbc khi xu\u1ea5t = T\u1ed5ng gi\u00e1 tr\u1ecb h\u00e0ng nh\u1eadp kho tr\u01b0\u1edbc khi xu\u1ea5t \u2013 T\u1ed5ng\u00a0gi\u00e1 tr\u1ecb h\u00e0ng \u0111\u00e3 xu\u1ea5t\u00a0kho tr\u01b0\u1edbc khi xu\u1ea5t<\/li>\n<li>T\u1ed5ng s\u1ed1 l\u01b0\u1ee3ng h\u00e0ng t\u1ed3n kho c\u00f2n l\u1ea1i tr\u01b0\u1edbc khi xu\u1ea5t =\u00a0T\u1ed5ng\u00a0SL h\u00e0ng nh\u1eadp kho tr\u01b0\u1edbc khi xu\u1ea5t \u2013 T\u1ed5ng\u00a0SL h\u00e0ng \u0111\u00e3\u00a0xu\u1ea5t\u00a0kho tr\u01b0\u1edbc khi xu\u1ea5t<\/li>\n<\/ul>\n<p>E4=(SUMIF(B2:B3,\u201dNh\u1eadp\u201d,F2:F3)-SUMIF(B2:B3,\u201dXu\u1ea5t\u201d,F2:F3))\/(SUMIF(B2:B3,\u201dNh\u1eadp\u201d,D2:D3)-SUMIF(B2:B3,\u201dXu\u1ea5t\u201d,D2:D3))<\/p>\n<p>F4=D4*E4<\/p>\n<p style=\"padding-left: 30px\">Xem th\u00eam: <a href=\"http:\/\/blog.ezworkapp.com\/ham-sumif-trong-excel-ham-tinh-tong-theo-mot-dieu-kien-cho-truoc.html\">C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m SUMIF t\u00ednh t\u1ed5ng theo \u0111i\u1ec1u ki\u1ec7n<\/a><\/p>\n<p>K\u1ebft qu\u1ea3 nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-19896\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/tinh-don-gia-xk-binh-quan-01.png\" width=\"662\" height=\"302\" title=\"tinh-don-gia-xk-binh-quan-01\"><\/p>\n<p>\u0110\u00e2y l\u00e0 1 s\u1ed1 l\u1ebb c\u00f3 ph\u1ea7n th\u1eadp ph\u00e2n. V\u1ec1 nguy\u00ean t\u1eafc ti\u1ec1n trong k\u1ebf to\u00e1n kh\u00f4ng \u0111\u01b0\u1ee3c c\u00f3 ph\u1ea7n th\u1eadp ph\u00e2n n\u00ean \u1edf \u0111\u00e2y ch\u00fang ta c\u1ea7n k\u1ebft h\u1ee3p v\u1edbi h\u00e0m l\u00e0m tr\u00f2n s\u1ed1. C\u1ee5 th\u1ec3 c\u00f3 2 tr\u01b0\u1eddng h\u1ee3p l\u00e0m tr\u00f2n:<\/p>\n<ul>\n<li><strong>L\u00e0m tr\u00f2n ngay khi t\u00ednh \u0111\u01a1n gi\u00e1<\/strong>: Trong c\u00f4ng th\u1ee9c t\u1ea1i E4 \u0111\u1eb7t th\u00eam h\u00e0m ROUND l\u1ed3ng b\u00ean ngo\u00e0i, l\u00e0m tr\u00f2n h\u1ebft ph\u1ea7n th\u1eadp ph\u00e2n n\u00ean d\u00f9ng s\u1ed1 0 t\u1ea1i num_ditgits. Khi l\u00e0m tr\u00f2n ngay t\u1ea1i \u0111\u01a1n gi\u00e1 c\u00f3 th\u1ec3 khi\u1ebfn sai l\u1ec7ch trong t\u00ednh to\u00e1n s\u1ed1 ti\u1ec1n l\u00ean nhi\u1ec1u v\u00e0 c\u00f4ng th\u1ee9c nh\u00ecn d\u00e0i d\u00f2ng, ph\u1ee9c t\u1ea1p. Nh\u01b0ng vi\u1ec7c n\u00e0y \u0111\u1ea3m b\u1ea3o kh\u00f4ng c\u00f3 s\u1ed1 l\u1ebb trong c\u1ea3 \u0111\u01a1n gi\u00e1 v\u00e0 th\u00e0nh ti\u1ec1n.<\/li>\n<li><strong>L\u00e0m tr\u00f2n t\u1ea1i c\u1ed9t\u00a0th\u00e0nh\u00a0ti\u1ec1n<\/strong>: S\u1eed d\u1ee5ng h\u00e0m ROUND v\u1edbi c\u00f4ng th\u1ee9c t\u1ea1i F4. Vi\u1ec7c n\u00e0y khi\u1ebfn c\u00f4ng th\u1ee9c nh\u00ecn ng\u1eafn g\u1ecdn h\u01a1n, gi\u1ea3m thi\u1ec3u sai s\u1ed1 khi t\u00ednh to\u00e1n. Nh\u01b0ng ph\u1ea3i l\u01b0u \u00fd v\u1ec1 \u0111\u01a1n gi\u00e1 v\u1eabn l\u00e0 s\u1ed1 l\u1ebb c\u00f3 ph\u1ea7n th\u1eadp ph\u00e2n.<\/li>\n<\/ul>\n<p>K\u1ebft qu\u1ea3 s\u1ef1 kh\u00e1c nhau gi\u1eefa 2 c\u00e1ch l\u00e0m tr\u00f2n s\u1ed1:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-19897\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/tinh-don-gia-xk-binh-quan-co-lam-tron.png\" width=\"854\" height=\"171\" title=\"tinh-don-gia-xk-binh-quan-co-lam-tron\"><\/p>\n<p>Trong h\u00ecnh tr\u00ean ch\u00fang ta c\u00f3 th\u1ec3 th\u1ea5y \u0111\u01a1n gi\u00e1 l\u1ec7ch t\u1edbi 56 \u0111\u1ed3ng khi l\u00e0m tr\u00f2n theo 2 c\u00e1ch kh\u00e1c nhau. Do \u0111\u00f3 c\u1ea7n th\u1ed1ng nh\u1ea5t v\u1ec1 c\u00e1ch l\u00e0m tr\u00f2n s\u1ed1 t\u1eeb tr\u01b0\u1edbc \u0111\u1ec3 h\u1ea1n ch\u1ebf t\u1ed1i \u0111a s\u1ef1 sai l\u1ec7ch khi l\u00e0m tr\u00f2n s\u1ed1 g\u00e2y ra. N\u1ebfu c\u00f3 sai l\u1ec7ch th\u00ec ch\u00fang ta c\u0169ng bi\u1ebft nguy\u00ean nh\u00e2n v\u00e0 c\u00f3 th\u1ec3 \u0111i\u1ec1u ch\u1ec9nh \u0111\u01b0\u1ee3c.<\/p>\n<p>T\u00ecm hi\u1ec3u th\u00eam nh\u1eefng ki\u1ebfn th\u1ee9c v\u1ec1 k\u1ebf to\u00e1n kho tr\u00ean Excel:<\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-lap-cong-thuc-thong-bao-khi-xuat-kho-bi-am-tren-excel.html\">H\u01b0\u1edbng d\u1eabn l\u1eadp c\u00f4ng th\u1ee9c th\u00f4ng b\u00e1o khi xu\u1ea5t kho b\u1ecb \u00e2m tr\u00ean Excel<\/a><\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-lap-phieu-xuat-kho-theo-thong-tu-133-tren-excel.html\">H\u01b0\u1edbng d\u1eabn c\u00e1ch l\u1eadp Phi\u1ebfu xu\u1ea5t kho theo Th\u00f4ng t\u01b0 133 tr\u00ean Excel<\/a><\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-ve-nguyen-tac-ke-toan-hang-ton-kho-theo-thong-tu-133.html\">H\u01b0\u1edbng d\u1eabn v\u1ec1 nguy\u00ean t\u1eafc k\u1ebf to\u00e1n h\u00e0ng t\u1ed3n kho theo Th\u00f4ng t\u01b0 133<\/a><\/p>\n<p>Ngo\u00e0i ra H\u1ecdc\u00a0Excel Online xin gi\u1edbi thi\u1ec7u t\u1edbi c\u00e1c b\u1ea1n kh\u00f3a h\u1ecdc\u00a0<a href=\"http:\/\/bit.ly\/2Nhac5a\">Tr\u1ecdn b\u1ed9 k\u1ef9 n\u0103ng nghi\u1ec7p v\u1ee5 k\u1ebf to\u00e1n t\u1ed5ng h\u1ee3p online tr\u00ean\u00a0Excel<\/a>. Kh\u00f3a h\u1ecdc n\u00e0y s\u1ebd gi\u00fap b\u1ea1n b\u1ed5 sung v\u00e0 ho\u00e0n thi\u1ec7n ki\u1ebfn th\u1ee9c k\u1ebf to\u00e1n t\u1ed5ng h\u1ee3p,\u00a0\u0111\u1ed3ng th\u1eddi b\u1ea1n c\u00f3 th\u1ec3 t\u1ef1 tay x\u00e2y d\u1ef1ng\u00a0\u0111\u01b0\u1ee3c tr\u1ecdn b\u1ed9 file\u00a0Excel\u00a0\u0111\u1ec3 l\u00e0m k\u1ebf to\u00e1n t\u1ed5ng h\u1ee3p t\u1eeb 1 file\u00a0excel tr\u1eafng. Chi ti\u1ebft xem t\u1ea1i:<\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<p>D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, nh\u01b0ng k\u1ef9 n\u0103ng Excel v\u1eabn c\u1ef1c k\u1ef3 quan tr\u1ecdng v\u1edbi k\u1ebf to\u00e1n, b\u1ea1n \u0111\u00e3 v\u1eefng Excel ch\u01b0a? H\u00e3y \u0111\u1ec3 t\u00f4i gi\u00fap b\u1ea1n, \u0111\u0103ng k\u00fd kho\u00e1 h\u1ecdc Excel:<\/p>\n<p><a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/ex101_92019-nho-167.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":10430,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-10429","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/10429","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/comments?post=10429"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/10429\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/10430"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=10429"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=10429"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=10429"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}