실용적인 엑셀 스킬 공유: 기능을 교묘하게 활용하여 자동 통계 구매, 판매, 재고 테이블 생성

이전 기사 "실용적인 Excel 기술 공유: "열 정렬 도구"의 여러 실제 작업"에서 여러 정렬 도구의 실제 작업을 배웠습니다. 오늘은 4가지 기능을 능숙하게 활용하여 자동으로 재고를 계산할 수 있는 "구매, 판매, 재고 및 인바운드 및 아웃바운드 통계 테이블"을 만들 수 있습니다. 이 동작을 배우면 지루한 작업에서 벗어나 시와 거리를 즐길 수 있습니다.

실시간 재고와 입고 및 출고 수량을 자동으로 계산할 수 있는 구매, 판매 및 재고 테이블을 만드는 방법은 실제로 어려운 기술이 필요하지 않습니다. 네 가지 기능만 마스터하면 됩니다. 기본적인 Excel 편집 및 조판 기능을 직접 만들 수 있습니다.

이 네 가지 기능은 vlookup, iferror, sumif 및 if입니다. 다음으로, 베테랑이 이 입구 및 출구 테이블을 만드는 방법을 단계별로 안내하도록 하십시오.

"구매, 판매, 재고 및 인바운드 및 아웃바운드 통계 테이블" 기능 설명:

실시간 통계 기능: 규정된 형식에 따라 아웃바운드 및 인바운드 흐름 테이블만 기록하면 자동으로 수행할 수 있습니다. 최신재고 및 입,출고 수량에 대한 실시간 통계를 제공합니다.

스마트 알림 기능: 품목의 재고가 안전 재고 수량보다 낮을 경우 자동으로 표시되어 경고 효과를 줍니다.

"구매, 판매, 재고 및 인바운드 및 아웃바운드 통계 테이블"의 구성:

가장 기본적인 요구에 따라 구매, 판매, 재고 및 인바운드 및 아웃바운드 테이블을 만들려면 일반적으로 세 부분이 필요합니다. 기본 데이터 테이블(기본정보 테이블이라고도 함), 입출고 기록 테이블(유수 상세 테이블이라고도 함), 재고 통계 테이블(결과 쿼리 테이블이라고도 함) 다음은 이 세 부분의 방법을 각각 설명합니다.

1. 기본 데이터 테이블

기업의 실제 요구에 맞게 설계하고 기본 원칙을 파악하여 테이블은 품목의 모든 속성을 반영할 수 있어야 하며, 각 속성은 별도의 테이블에 저장되어야 합니다. 열. 테이블이 아름다울 필요는 없으며 병합된 셀이 나타나서는 안 됩니다.

예를 들어 아래 그림은 비교적 표준화된 기본 데이터 테이블입니다.

참고: 일련번호는 필요하지 않으며 단지 쉬운 검색을 위한 것입니다. 통계에서는 일반적으로 제품 코드를 유일한 기초로 사용합니다. 회사 제품에 코드가 없는 경우 일련번호를 코드로 사용할 수 있습니다.

통계 데이터의 정확성을 보장하기 위해 새로운 제품이 있는 경우 테이블에 기록을 추가해야 합니다. 오래된 제품이 있는 경우 원본 기록을 삭제할 필요는 없습니다.

2. 들어오고 나가는 기록

보통 나가는 시트와 들어오는 시트를 2장으로 나누어 보관하거나, 편의상 함께 보관하는 경우를 예시로 들어보겠습니다.

테이블의 데이터 열에는 기본 제품 정보와 입고 및 출고 창고의 날짜 및 수량을 포함해야 합니다. 형식은 대략 다음과 같습니다.

위의 흐름 테이블에는 파란색 A, E만 있습니다. , F, G. 열은 적시에 기록되어야 합니다. 분류, 이름, 단위 열 등의 기본 정보는 수식을 통해 자동 생성됩니다. 이제 vlookup이 등장할 차례입니다!

네, 이때가 vlookup이 작동하는 시점입니다. 아래 그림을 보면 인코딩 후의 세 열이 모두 vlookup 함수를 사용하여 얻어지는 것을 볼 수 있습니다. B2 셀의 수식은 다음과 같습니다. =VLOOKUP($A2, 기본 데이터 테이블!$B:$E,COLUMN(B1),0)=VLOOKUP($A2,基础数据表!$B:$E,COLUMN(B1),0)

公式解读：vlookup一共需要四个参数，基本格式为

=vlookup(查找值，查找区域，列数，精确查找)

• 第一个参数$A2表示想要查找的内容，注意因为公式要右拉下拉，因此在A前面加了$对列进行锁定，防止右拉时发生错误；

• 第二个参数基础数据表!$B:$E表示要查找的区域（文章前面介绍的基础数据表），注意这个区域是以编码为首列的，因为编码在基础数据表的B列，所以区域也是从B列开始而不是从A列开始，这一点一定要记住，因为很多新手使用vlookup都在这个地方犯了错误；

• 第三个参数表示返回的内容为查找区域的第几列，因为公式要右拉，所以我们使用column(B1)作为返回列数。

  column的作用是得到参数的列号。我们要返回基础数据表$B:$E 中的C列即第2列， 在整个参数基础数据表中B1单元格的列号是2，因此这里用column(B1)

  
• 공식 해석: vlookup에는 총 4개의 매개변수가 필요하며 기본 형식은
  =vlookup(검색값, 검색 영역, 열 수, 정확한 검색)🎜
  🎜🎜첫 번째 매개변수입니다. $A2는 찾으려는 내용을 나타냅니다. 수식을 오른쪽으로 끌어내려야 하기 때문에 A 앞에 <code>$를 추가하여 오른쪽으로 끌어당길 때 오류가 발생하지 않도록 열을 잠급니다. 🎜🎜🎜🎜두 번째 매개변수 기본 데이터 테이블!$B:$E는 검색할 영역을 나타냅니다(기사 앞부분에서 소개한 기본 데이터 테이블). 첫 번째 열은 코딩이 기본 데이터 테이블 B 열에 있기 때문에 영역도 A 열이 아닌 B 열에서 시작됩니다. 많은 초보자가 vlookup을 사용할 때 이 위치에서 실수를 하기 때문에 기억해야 합니다. 🎜🎜🎜🎜The 세 번째 매개변수는 반환값을 나타냅니다. 내용은 검색 영역의 열 번호입니다. 수식을 오른쪽으로 가져와야 하므로 반환된 열 번호로 column(B1)을 사용합니다. 🎜🎜column의 기능은 매개변수의 열 번호를 가져오는 것입니다. 기본 데이터 테이블 $B:$E의 컬럼 2인 C 컬럼을 반환하려고 합니다. 전체 매개변수 기본 데이터 테이블에서 셀 B1의 컬럼 번호는 2이므로 column (B1)반환될 열 수를 나타냅니다. 수식을 오른쪽으로 당기면 B1은 C1이 되고 열 번호는 2에서 3으로 변경되어 수식 오른쪽으로 당겨 여러 열 참조를 완성하는 목적을 달성합니다. 🎜🎜🎜🎜마지막 매개변수 0은 정확한 검색을 의미합니다. 🎜

테이블의 마지막 세 열인 날짜와 들어오고 나가는 수량을 실제 상황에 따라 기록할 수 있습니다. 일반적인 상황에서는 이 흐름도가 완성되지만, 보다 지능적으로 사용하기 위해 vlookup 부분을 최적화할 수도 있습니다.

제품 코드를 입력하면 항목에 오류가 있을 수 있으며(또는 기본 데이터 테이블에 없는 새 코드) 일부 잘못된 코드가 표시됩니다.

효과가 없습니다. 매우 아름답습니다. 따라서 이 문제를 해결하려면 vlookup과 협력하기 위해 또 다른 함수인 iferror가 필요합니다. 공식은 다음과 같이 수정됩니다. =IFERROR(VLOOKUP($A2, 기본 데이터 테이블!$B:$E,COLUMN(B1) ),0),"인코딩이 잘못된지 확인해주세요!")=IFERROR(VLOOKUP($A2,基础数据表!$B:$E,COLUMN(B1),0),"编码有误请核查！")

可能有些朋友是第一次见到iferror这个函数，简单介绍一下：

=iferror(公式，公式结果错误时显示的内容)，公式只需要两个参数，第一个参数是一个公式，第二个参数是当公式结果错误时需要显示的内容。以本例来说，第一参数就是vlookup，当vlookup的结果正确时，iferror不发生作用，但是当vlookup的结果错误时，就会显示需要的内容，本例是显示了一串文字：编码有误请核查！注意：如果要显示的内容是文本一定要加引号。

三、库存统计表

这个库存统计表的功能是对所有产品的库存情况进行实时显示，大致有以下一些信息：累计出库数量、累计入库数量、当前库存数量；如果需要进行缺货提示的话还需要一个安全库存数量以及是否缺货的内容。

这个统计表并不需要单独再建立一个sheet，只需要在基础数据表的后面添加刚才列出来的这些内容就OK了，格式如下图所示：

可以看到，在基础数据表后面增加了六列内容，其中只有初始库存和安全库存数是需要录入的，累计出库数量、累计入库数量和是否缺货都是通过公式来实现的，以下对这些字段做个简要的说明：

初始库存：也可以叫做库存结转，在启用这个出入库统计表的时候对原有库存进行记录。

累计出库数量（G列）：使用公式=SUMIF(出入库记录表!A:A,B2,出入库记录表!F:F)统计所得：

公式解析：sumif函数需要三个参数，基本结构为=SUMIF(条件区域, 条件, 求和区域)

• 第一个参数出入库记录表!A:A表示条件列；

• 第二个参数B2表示前面条件列应该满足的条件（对应该行物品编码）；

• 第三个参数出入库记录表!F:F表示对满足条件的在此列求和。

同样的方法将第三个参数出入库记录表!$F:$F换成出入库记录表!$G:$G得到累计入库数量（H列）：

当前库存数量：用初始库存-累计出库数量+累计入库数量 即可；

安全库存数量：本例中都设置的是50，可以根据每个产品的情况进行确定。此项需要手工输入。

是否缺货：这里用到了IF函数，公式为：=IF(I2>J2,"","缺货")

If函数的基本格式为if(条件, 成立时需要的结果, 不成立时需要的结果)；

本例中条件为I2>J2

iferror 함수를 처음 보는 친구들도 있을 것입니다. 간단히 소개하겠습니다:

=iferror (공식, 수식 결과가 틀렸을 때 표시되는 내용) , 수식에는 두 개의 매개변수만 필요하며 첫 번째 매개변수는 수식, 두 ​​번째 매개변수는 수식 결과가 틀렸을 때 표시해야 하는 내용입니다. 이 예에서 첫 번째 매개변수는 vlookup입니다. vlookup 결과가 올바르면 iferror가 적용되지 않습니다. 그러나 vlookup 결과가 잘못된 경우 필요한 내용이 표시됩니다. 인코딩이 잘못됐는지 확인해주세요! 참고: 표시할 내용이 텍스트인 경우 따옴표로 묶어야 합니다.

3. 재고 통계 테이블

이 재고 통계 테이블의 기능은 모든 제품의 재고 상태를 표시하는 것입니다. 실시간으로 누적 아웃바운드 수량, 누적 인바운드 수량 및 현재 재고 수량과 같은 정보가 있습니다. 부족 알림이 필요한 경우 안전 재고 수량 및 품절 여부도 필요합니다.

🎜본 통계표는 별도의 시트를 생성할 필요 없이 기본 데이터표 마지막에 방금 기재한 내용만 추가하시면 됩니다. 형식은 아래와 같습니다. 🎜🎜🎜🎜기본 데이터 표 뒤에 6개가 추가된 것을 볼 수 있습니다 항목 내용에는 초기재고 및 안전재고 수량만 입력하면 됩니다. 누적 출고 수량, 누적 입고 수량, 품절 여부는 모두 수식으로 구현됩니다. 🎜🎜초기 항목은 다음과 같습니다. 재고: 재고 이월이라고도 할 수 있으며, 입고 및 출고 재고 통계 테이블이 활성화되면 원래 재고가 기록됩니다. 🎜🎜누적 아웃바운드 수량(G열): =SUMIF(아웃바운드 및 아웃바운드 레코드 테이블! A:A, B2, 아웃바운드 및 아웃바운드 레코드 테이블! F:F) 수식을 사용하여 통계를 얻습니다. 🎜 🎜🎜🎜수식 분석 : sumif 함수 세 개의 매개변수가 필요하며 기본 구조는 =SUMIF(조건 영역, 조건, 합산 영역)🎜

• 🎜첫 번째 매개변수 인바운드 및 아웃바운드 레코드 테이블! A:A는 조건 열을 나타냅니다. 🎜🎜
• 🎜두 번째 매개변수 B2는 이전 조건을 나타냅니다. (이 행 항목 코드에 해당하는) 열이 충족되어야 합니다. 🎜🎜
• 🎜세 번째 매개변수 인바운드 및 아웃바운드 레코드 테이블! F:F는 조건을 충족하는 열의 합을 의미합니다. 🎜🎜🎜🎜같은 방법으로 세 번째 매개변수인 Inbound and outbound Record table!$F:$F를 Inbound and outbound Record table!$G:$G로 바꾸세요. 총 수신 수량(열 H)을 얻으려면: 🎜🎜🎜🎜현재 재고 수량: 초기 재고 사용 - 누적 출고 수량 + 누적 입고 수량 🎜🎜🎜🎜안전재고 수량: 본 예시에서는 50으로 설정되어 있으며, 이는 각 상품의 상황에 따라 결정될 수 있습니다. 이 항목은 수동으로 입력해야 합니다. 🎜🎜품절 여부: 여기서는 IF 함수가 사용되며 공식은 다음과 같습니다. =IF(I2>J2,"","Out of stock")🎜🎜🎜🎜If 함수의 기본 형식은 if(조건 성립 시 필요한 결과, 성립되지 않은 경우 필요한 결과) 🎜🎜이 예에서는 조건이 I2>J2이므로, 현재 재고 수량이 안전 재고 수량보다 크면 공백이 표시됩니다. 어쨌든 "재고 없음"이라는 단어가 표시됩니다. 🎜🎜동시에 이 열에 조건부 서식을 설정하세요. 품절이 발생하면 색상을 사용하여 눈길을 끄는 효과를 얻으세요. 🎜🎜설정 방법은 k열을 선택하고 [조건부 서식] → [셀 규칙 강조] → [같음] 클릭: 🎜🎜🎜🎜🎜왼쪽 상자에 품절이라는 단어를 입력하고 필요한 것을 선택하세요. 오른쪽 효과가 나타난 후 확인하세요. 🎜

  

  이제 자동 통계 입력 및 종료 테이블을 쉽게 구현할 수 있습니다! 이 도구를 사용하면 더 이상 수천 개의 품목에 대한 창고 재고를 잘못 계산하는 것에 대해 걱정할 필요가 없습니다. 부족함이 발견되면 조달 담당자에게 구매를 지시할 수 있으며 효율성도 향상되었습니다!

  마지막으로 유사한 인바운드 및 아웃바운드 통계 테이블에 대한 디자인 아이디어는 거의 동일하다는 점을 지적하고 싶습니다. 단가 및 기타 정보가 있는 경우 일부 최적화가 이루어질 수 있습니다. 기본 데이터 테이블로 이동한 다음 수량 * 단가를 사용하여 금액을 가져옵니다.

  데이터 유효성을 사용하여 데이터 입력을 표준화하는 것이 좋습니다. 예를 들어 인코딩이 고유해야 하는 경우 유효성을 설정하여 반복 입력을 방지할 수 있습니다. (방법을 모르는 경우 메시지를 남길 수 있습니다.) 아직 하세요).

  오작동으로 인해 수식이 파괴되고 데이터의 정확성 등에 영향을 미치는 것을 방지하기 위해 수식 보호를 설정합니다.

  관련 학습 권장 사항: excel 튜토리얼

위 내용은 실용적인 엑셀 스킬 공유: 기능을 교묘하게 활용하여 자동 통계 구매, 판매, 재고 테이블 생성의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!