物流管理 工作中的EXCEL應(yīng)用1

《物流管理 工作中的EXCEL應(yīng)用1》由會(huì)員分享，可在線閱讀，更多相關(guān)《物流管理 工作中的EXCEL應(yīng)用1（23頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

1、 思路與EXCEL 物流十多年工作中解決各種問題的實(shí)例 葉劍 倉(cāng)儲(chǔ)作業(yè)高效思路EXCEL培訓(xùn)專用教材 前言 物流工作十多年來，從快遞工作到第三方物流和企業(yè)物流管理,尤其是在倉(cāng)庫(kù)作業(yè)中，我有很多提高效率和準(zhǔn)確率的革新，而這些革新中EXCEL給了我很大的幫助，讓我十分受益。因?yàn)镋XCEL讓我受益匪淺，所以，我很樂意跟大家分享EXCEL那些對(duì)我們工作有幫助的功能，尤其是從事物流工作或以后可能會(huì)從事物流工作的人，應(yīng)當(dāng)需要熟練運(yùn)用EXCEL的一些功能。 EXCEL是一種辦公軟件，對(duì)我來講，它是很好的工具，它不能代替其他軟件和工具，但其他軟件和工具也

2、不能代替EXCEL。各有所長(zhǎng)，各有所短。沒有好與壞，只有適合之說。能解決實(shí)際問題的就是好工具! EXCEL的特點(diǎn)，就是靈活性比較高，受限止少,是大家常用的辦公軟件,EXCEL可以通過函數(shù)和VBA制作許多的小程序，進(jìn)行系統(tǒng)性的操作。EXCEL不僅可以進(jìn)行各種運(yùn)算制作各種圖表，還能繪制許多圖形?？梢杂盟治鰯?shù)據(jù)，還能以很多形式去體現(xiàn)數(shù)據(jù)，功能十分強(qiáng)大！ EXCEL的缺點(diǎn)就是存儲(chǔ)數(shù)據(jù)有限，數(shù)據(jù)量大計(jì)算過程較多時(shí)，反應(yīng)速度會(huì)比較慢。所以，我們用EXCEL，就要揚(yáng)長(zhǎng)避短。 我認(rèn)為，作為一個(gè)物流工作人員，尤其是從事倉(cāng)庫(kù)管理工作，如果不懂點(diǎn)EXCEL操作，可能會(huì)過度依賴現(xiàn)有的系統(tǒng)軟件等條件,不能靈活機(jī)

3、動(dòng),與時(shí)俱進(jìn),導(dǎo)致許多作業(yè)的浪費(fèi)。工作中，我們需要用到EXCEL對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，分析，計(jì)算，這樣可以開拓很多思路，不會(huì)用EXCEL，就意味著很多思路沒有打開，自然就會(huì)做得不夠好。當(dāng)然，即便是EXCEL用得很好的人，沒有物流實(shí)戰(zhàn)經(jīng)驗(yàn)和管理經(jīng)驗(yàn)，也不一定就能把倉(cāng)庫(kù)管理得好，故實(shí)踐方能出真知。作為倉(cāng)庫(kù)管理人員,如果倉(cāng)庫(kù)沒有自動(dòng)分揀和電子揀貨系統(tǒng),但你要有自動(dòng)分揀和電子揀貨系統(tǒng)的思路；沒動(dòng)自動(dòng)化的立體倉(cāng)庫(kù),但你要有自動(dòng)化立體倉(cāng)庫(kù)的思路,做到閉著眼睛也能配出貨品；沒有RFID射頻技術(shù),但你要有RFID高效掃描的思路；沒有飛機(jī)大炮,就把小米加步槍的潛力挖掘起來充分利用. …… 日積月累,非常有幸的是能

4、借助物流沙龍這個(gè)平臺(tái),將經(jīng)驗(yàn)和大家分享,為了能讓大家有更多的認(rèn)識(shí),我將十多年工作中用EXCEL解決的種種問題和如何提高操作效率的應(yīng)用表格匯編于本冊(cè)，第一部分為常用函數(shù)應(yīng)用示例(截圖),EXCEL函數(shù)不熟悉的人員需要先學(xué)習(xí)第一部分后再看第二部分案例。由于我一開始EXCEL尚不精通，故資料中定有一些表格會(huì)有煩瑣之處。如有不值一看的表,還請(qǐng)一帶而過。無(wú)論讀者是EXCEL初,中,高級(jí)或在不同的物流倉(cāng)儲(chǔ)管理領(lǐng)域,只希望大家能從中各取所需,通過一些案例打開更多的工作思路，在工作中不斷的加以優(yōu)化。 @56r 2014年7月24日 目錄 目錄 3 第一部分 EXCEL基礎(chǔ)及常見函

5、數(shù)應(yīng)用示例 5 01.了解EXCEL表格 5 02.運(yùn)算及引用符號(hào) 5 03.加減乘除的基本計(jì)算 6 04.“$”的應(yīng)用 6 05.年，月，日的計(jì)算 7 06.時(shí)，分，秒的計(jì)算 7 07.數(shù)據(jù)統(tǒng)計(jì)中的應(yīng)用（求各種值—圖例） 8 08.字段信息的提取與編輯 13 09.“IF”函數(shù)的廣范應(yīng)用 15 10.文字格式的轉(zhuǎn)換 18 11.信息的查找與匹配 18 12.“INDEX”在信息查詢與提取中的應(yīng)用及說明 21 第二部分 十年工作中的應(yīng)用實(shí)例 23 01．在KPI統(tǒng)計(jì)表中，求滿足多種條件的值 23 02．從相對(duì)規(guī)則的信息中提取所要的信息數(shù)據(jù) 26 03．從工作量統(tǒng)

6、計(jì)表中提取每一個(gè)人的工作量 27 04．工作量分值與績(jī)效考核 27 05．考勤異常情況提醒 29 06．根據(jù)打卡記錄生成考勤表 31 07．根據(jù)考勤機(jī)打卡記錄統(tǒng)計(jì)加班人數(shù) 33 08．考勤統(tǒng)計(jì)表的系統(tǒng)性制作 36 09．將已知信息錄入到指定表格 40 10．?dāng)?shù)據(jù)透視的應(yīng)用 41 11．每周工作量的匯總 42 12．工作計(jì)劃的”7要素” 43 13．倉(cāng)儲(chǔ)費(fèi)用計(jì)算 45 14．簡(jiǎn)易庫(kù)存賬的制作 47 15．第三方物流臺(tái)賬 48 16．倉(cāng)庫(kù)可視化管理 51 17．如何做到庫(kù)存貨品的先進(jìn)先出 54 18．如何判斷貨品是否按先進(jìn)先出發(fā)貨 55 19．大宗貨位的自動(dòng)選擇 5

7、6 20．服裝線上與線下入庫(kù)貨品庫(kù)位分配 58 21．服裝退貨整理與箱單打印 61 22．退貨上架貨品堆碼指定托盤號(hào) 63 23．箱嘜連續(xù)打印的VBA方法 65 24．自動(dòng)化分揀思路之服裝越庫(kù)操作 67 25．根據(jù)已揀貨品量求剩余量 69 26．貨品完全匹配條件的分揀 70 27．RFID條件下的高效分揀思路 71 28．RFID思路之整托貨品越庫(kù)分配的計(jì)算（精典案例） 76 29．發(fā)貨包裝（拼箱）自動(dòng)計(jì)算 82 30．庫(kù)內(nèi)短駁加箱(零箱)拼箱標(biāo)簽的制作 84 31．根據(jù)整箱規(guī)格檢查播種或揀貨過程的準(zhǔn)確性 85 32．播種后如何提高點(diǎn)數(shù)裝箱的效率與準(zhǔn)確率? 85 3

8、3．播種單格式高效讀報(bào)方式的轉(zhuǎn)換 86 34．分批入庫(kù)貨品的配發(fā) 91 35．黃金包裝需求量計(jì)算思路 93 36．不同配比條件下的黃金包發(fā)貨量計(jì)算 95 37．電商操作與物流發(fā)貨思路 99 38．電商揀貨流程優(yōu)化 103 39．電商分區(qū)揀貨再合單的操作思路 111 40．電商兩地發(fā)貨模式的單據(jù)審核 114 41．電商揀貨路徑優(yōu)化與距離計(jì)算 119 42．亞馬遜電商導(dǎo)出數(shù)據(jù)的整理 127 43．第三方物流（企業(yè)物流）發(fā)貨方式及承運(yùn)商自動(dòng)選擇思路 131 44．兩家貨運(yùn)公司價(jià)格與時(shí)效對(duì)比參考 132 45．上海市內(nèi)快遞編號(hào)法分揀 134 46．上海同城快遞“蜘蛛網(wǎng)”中轉(zhuǎn)班

9、車運(yùn)行設(shè)計(jì) 136 47．全國(guó)辦事處倉(cāng)庫(kù)分布圖 138 48．上海地區(qū)店鋪分布圖 139 49．長(zhǎng)三角地帶物流中心選址分析 141 50．服裝的標(biāo)準(zhǔn)化物流 142 51．不同規(guī)格的包裝以滿足不同需求的發(fā)貨 146 倉(cāng)儲(chǔ)現(xiàn)場(chǎng)管理心得 147 作者簡(jiǎn)介: 葉劍,部隊(duì)優(yōu)秀”四會(huì)教練員”,從快遞員,倉(cāng)管員,客服員,調(diào)度做起,是具有豐富的物流現(xiàn)場(chǎng)實(shí)戰(zhàn)和管理經(jīng)驗(yàn)的物流師,歷任快遞公司,第三方物流公司主管,目前就職于綾致時(shí)裝任倉(cāng)儲(chǔ)部經(jīng)理.在從事快遞,第三方物流,企業(yè)倉(cāng)庫(kù)管理的近15年中,憑借清晰的工作思路,在企業(yè)沒有WMS的情況下,借助EXCEL,提高效率和準(zhǔn)確率,降低成本的革新

10、案例達(dá)一百多項(xiàng),為服務(wù)的企業(yè)節(jié)約物流操作成本達(dá)上千萬(wàn)元,成為許多院校,電商倉(cāng)儲(chǔ)企業(yè)和物流沙龍受歡迎的講師和顧問. 第一部分 EXCEL基礎(chǔ)及常見函數(shù)應(yīng)用示例 在第一部分中，Excel函數(shù)基礎(chǔ)應(yīng)用及說明都以精簡(jiǎn)截圖的方式表達(dá)，希望讓讀者在入門的時(shí)候能夠?qū)xcel函數(shù)功能有更直觀的了解。 01.了解EXCEL表格 02.運(yùn)算及引用符號(hào) 03.加減乘除的基本計(jì)算 04.“$”的應(yīng)用 單元格的位置，由列坐標(biāo)和行坐標(biāo)組合確定，列坐標(biāo)用英文字母表示，行坐標(biāo)用阿拉伯?dāng)?shù)字表示，如第1列的表達(dá)式是“A”，第2列的表達(dá)式是“B”，以此類推。第1行的表達(dá)式

11、是“1”，第2行的表達(dá)式是“2”，以此類推。第2列的第2行位置就是單元格B2，第1列的第11行位置就是單元格A11，在我們用函數(shù)輸入公式時(shí)，A1就是表示A1這個(gè)單元格的值，A11就是表示A11這個(gè)單元格的值，如果在某個(gè)單元格內(nèi)輸入“=A11”，那么A11單元格里面是什么，這個(gè)單元格將體現(xiàn)什么。如上表中，A11單元格內(nèi)是一個(gè)“甲”字，在B11中輸入了=A11，結(jié)果也就顯示了“甲”字。在選中B11這個(gè)單元格往下拉時(shí)，相應(yīng)的單元格B12,B13,B14,分別就有了結(jié)果A12,A13,A14。但如果在公式中的行號(hào)前面加“$”，再往下拉時(shí)，仍是11，也就是說“$”已經(jīng)對(duì)11進(jìn)行了鎖定。要注意的是，將選中

12、輸入公式的單元格，如果公式中的單元格行號(hào)沒有用$鎖定，向下拖動(dòng)時(shí)會(huì)依次增加，列號(hào)不變。如果列號(hào)沒有用$鎖定，則往右拖動(dòng)時(shí)，列號(hào)依次增加，行號(hào)不變。 05.年，月，日的計(jì)算 求某個(gè)日期的年份，月份和號(hào)，分別用：YEAR,MONTH,DAY. 在EXCEL任意單元格內(nèi)輸入此“公式”，回車后就會(huì)體現(xiàn)當(dāng)天的日期/當(dāng)時(shí)的時(shí)間。 用結(jié)束日期減開始日期，即可得到它們之間相隔的天數(shù)。然求當(dāng)中的工作日，則需要用函數(shù)NETWORKDAYS. 如果要知道某一天是星期幾？可以先通過WEEKDAY函數(shù)求出所在周的第幾天，然后再去匹配第幾天是星期幾，在計(jì)算時(shí)需要注意中西方一周的第一天是不同的。

13、 06.時(shí)，分，秒的計(jì)算 求某個(gè)時(shí)間的時(shí)，分，秒，可以直接用函數(shù)：HOUR,MINUTE,SECOND. 如果計(jì)算某項(xiàng)工作開始到結(jié)束，用了多少分鐘，可以先用結(jié)束時(shí)間減開始時(shí)間，得到用了多少小時(shí)多少分鐘，然后用小時(shí)數(shù)×60＋分鐘分即可。 07.數(shù)據(jù)統(tǒng)計(jì)中的應(yīng)用（求各種值—圖例） 尤其求最大公約數(shù)，可以確定最小需求單位，在物流中意義很大。 直接輸入=PI()，就得到圓周率。 MAX，求一組數(shù)值中的最大值；MIN，求一組數(shù)值中的最小值。 SUM，常用的求和函數(shù)，直接引用，即可求某個(gè)范圍所有數(shù)值的和。 COUNT與COUNTA都是計(jì)數(shù)，區(qū)別在于COUNTA可

14、以將文本格式的值進(jìn)行計(jì)數(shù)，而COUNT只對(duì)數(shù)值進(jìn)行計(jì)數(shù)。 AVERAGE與AVERAGEA都是求平均值，但AVERAGE只是針對(duì)數(shù)值，用數(shù)值的和除以數(shù)值的個(gè)數(shù)，而得到平均值。但AVERAGEA，則是用數(shù)值的和除以所有參與的（包含文本）的人數(shù)，而得到的平均值。 INT，取整，不保留任何小數(shù)。 ROUNDUP，向上舍入，可按需求保留小數(shù)位數(shù)，“0”即是不保留。 MOD，求被除數(shù)與除數(shù)的余數(shù)。 此例在倉(cāng)庫(kù)管理中計(jì)算入庫(kù)上架庫(kù)位和貨品盤點(diǎn)中應(yīng)用較多。 RANK用于求某個(gè)值，在某個(gè)范圍的大小序列。B2單元格是王二小的成績(jī)，在B2:B7（6個(gè)人的成績(jī)）中排名第5。 注意計(jì)算排

15、名與倒數(shù)排名公式的區(qū)別，在后面加上“1”的作用。 =SUMPRODUCT((A2:A13=”南大”)*(B2:B13=”大一”)*(D2:D13)) 解釋：求符合多種條件的和，即當(dāng)學(xué)校為“南大”時(shí)(A2:A13=”南大”)，班級(jí)為“大一”時(shí)(B2:B13=”大一”)，有多少人參加（計(jì)算范圍D2:D13），“*”為連接多種滿足條件的符號(hào)。 注意在使用SUMPRODUCT時(shí)，“，”與“*”的區(qū)別，如上SUMPRODUCT(B2:B9,C2:C9)，其結(jié)果就是從B2*C2一直加到B9*C9。 COUNTIF，求在什么范圍，滿足“什么”條件的數(shù)值有幾個(gè)，計(jì)數(shù)所用。 SU

16、BTOTAL函數(shù)，具有多種函數(shù)的功能，在計(jì)算范圍前加“1,2,3,4,5,6,7,8,9”結(jié)果各不一樣。 SUMSQ，所有數(shù)值平方的和；SQRT，求某個(gè)數(shù)值的平方根。 SUMIF是求滿足條件的和，如求一年級(jí)有多少學(xué)生，及在A14:A22年級(jí)范圍內(nèi)有多少是等于A14”一年級(jí)”的，如果是“一年級(jí)”，那么后面的“人數(shù)”相加即得到所求的值。 注意：COUNT，COUNTA，COUNTIF的區(qū)別。 COUNTIF與SUMIF的區(qū)別，一是計(jì)數(shù)，一是求和。 MIN（計(jì)算范圍）＝SMALL（計(jì)算范圍,1） MAX（計(jì)算范圍）＝LARGE（計(jì)算范圍,1） 解釋：

17、上表公式，是計(jì)算某個(gè)產(chǎn)品本次的發(fā)貨日期減去上一次的發(fā)貨日期，得到間隔的發(fā)貨時(shí)間。 其中嵌用了：INDEX,LARGE,IF,ROW. 在D4單元格輸入上面公式，然后下拉即可，{} 是同時(shí)按：ctrl, shift, enter 生成。 08.字段信息的提取與編輯 注意：&與CONCATENATE的區(qū)別。“&”的輸入，是按住shift鍵同時(shí)按上行數(shù)字鍵“7”即可。 注意：LEN與LENB的區(qū)別，及字符與字符串的區(qū)別，一個(gè)文字是一個(gè)字符串，一字符串可能是一個(gè)字符，也可能是兩個(gè)字符，一個(gè)數(shù)字或字母只有一個(gè)字符，但一個(gè)漢字卻有兩個(gè)字符。 小竅門——求字段中有幾個(gè)漢字：如果在上表中，

18、用計(jì)算出來的字符數(shù)減去計(jì)算出來的字符串?dāng)?shù)，就可以知道字段中有幾個(gè)漢字了。 LEFT(A5,3),是求A5單元格中左側(cè)3位的字段。A5單元格內(nèi)是“江蘇省泰州市”，左側(cè)3位是“江蘇省”。 MID是從字段中間提出信息的函數(shù)，一個(gè)人的出生年份，在身份證號(hào)碼中，是從第7位，向后4位。 上表中=RIGHT（A4，1），A4中的值是“中華人民共和國(guó)”，“中華人民共和國(guó)”的右側(cè)第1位，是“國(guó)”。 FIND，查找某個(gè)字符串在某個(gè)字段中的第幾位。 REPT，將需要重復(fù)的字段，重復(fù)“N”次。 REPLACE，將字段中第幾位指定往后幾位的字段，替換成相應(yīng)的內(nèi)容。 SUBS

19、TITUTE，將字段中，符合替換的字段，替換成相應(yīng)的內(nèi)容。 09.“IF”函數(shù)的廣范應(yīng)用 IF，如果滿足條件，即為結(jié)果“A”，否則就為結(jié)果“B”。 =IF(B3>=60,”是”,””) 如果王二小的成績(jī)B3大于等于60，即顯示“是”，否則什么也不顯示。 =IF(B11<60,”不及格”,””) 如果王二小的成績(jī)B11小于60，即顯示“不及格”，否則什么也不顯示。 “”表示不滿足條件時(shí)，不顯示任何信息。 AND,如果三門課程的成績(jī)同時(shí)達(dá)到90,即”是”全優(yōu)生,否則不是,即為”否”. OR,有任一門課程達(dá)到90分,即為滿足條件。 上表以“王二小”為例，成績(jī)

20、為76，使用“IF”判斷，由于不滿90，所以不是“優(yōu)”，由于不滿80，所以也不是“良”，接下為大于60，所以評(píng)定為“及格”。而用“LOOKUP”函數(shù)，76位于60-80之間，匹配結(jié)果為“及格”。 先判斷兩個(gè)日期之間相隔的年份，注意公式的使用，而不是YEAR(C4)－YEAR(B4)。 再判斷當(dāng)工齡大于等于5年時(shí)，給予獎(jiǎng)勵(lì)，否則沒有。 或者說如果工齡不小于5年時(shí)，給予獎(jiǎng)勵(lì)。 錢永正的入職日期是2011-3-5，雖然2013減2011等于2，但是由于2013-3-5小于2013-1-1，所以結(jié)果就需要減去1，即錢永正的工齡滿1年，不滿2年。 =IF($A9<=$B$1,$A9

21、,””),在B1(即$B$1)中輸入需要打印的箱數(shù)“5”，由于A9中的值“6”大于“5”，所以結(jié)果為””，而不是“6”， ””即不體現(xiàn)任何值。 箱數(shù)輸入“2”，則體現(xiàn)2箱的打印內(nèi)容。 10.文字格式的轉(zhuǎn)換 11.信息的查找與匹配 VLOOKUP，查找匹配函數(shù)，在數(shù)據(jù)首列搜索某個(gè)值，匹配首列往右“N”列所對(duì)應(yīng)的值。 關(guān)鍵詞：首列。 MATCH，在區(qū)域里A2:A9，找出所求值，從A2起算所在的序列號(hào)，。 選定區(qū)域很重要，如區(qū)域設(shè)定為B1:I1，即從B1算起，B1作為第1列，香蕉則是在第3列。 HLOOKUP，與VLOOKUP用法相同，區(qū)別在于

22、搜索的值一個(gè)是在首行，一個(gè)是在首列。 OFFSET，從某個(gè)單元格起，向下N列，再向右N列的單元格的值。 注意發(fā)現(xiàn)上表中，INDEX與OFFSET的區(qū)別。 ADDRESS，生成指定單元格的位置，ADDRESS（指定的行號(hào)，指定的列號(hào)） INDIRECT通常與ADDRESS結(jié)合使用，注意INDIRECT(F3)與INDIRECT(“F3”)的區(qū)別。 注意：INDIRECT(ADDRESS(1,1))的結(jié)果，為INDIRECT(“A1”)。 12.“INDEX”在信息查詢與提取中的應(yīng)用及說明 說明：通過MATCH函數(shù)可以計(jì)算出所求值所在的“行數(shù)”和“列數(shù)”，表中“梨子”在第3列，而“價(jià)格”和“產(chǎn)地”分別在第2行和第3行，因此，通過INDEX查找，第2行第3列的值是“2.8”，第3行第3列的值是“濰坊”。 上表是根據(jù)倉(cāng)庫(kù)的溫度和干濕表溫差求庫(kù)房?jī)?nèi)的相對(duì)濕度.通過INDEX函數(shù)直接計(jì)算出結(jié)果. 通常情況下，查找信息，會(huì)用“篩選”來操作，比如在“分配地區(qū)”中篩選“上海”，可以得知有哪些箱號(hào)的貨品分配給上海。但對(duì)于經(jīng)常性的操作，“篩選”就不太方便，而通過函數(shù)公式設(shè)置好后，查詢?nèi)我坏貐^(qū)的信息就會(huì)自動(dòng)生成，十分方便。 圖示說明：