Praktische Excel-Kenntnisse teilen: Funktionen geschickt nutzen, um eine automatische statistische Einkaufs-, Verkaufs- und Lagertabelle zu erstellen

Im vorherigen Artikel „Praktischer Excel-Fähigkeitsaustausch: Mehrere praktische Vorgänge des „Spaltensortiertools““ haben wir die praktischen Vorgänge mehrerer Sortierwerkzeuge kennengelernt. Heute lernen wir vier Funktionen kennen, mit denen wir eine „Einkaufs-, Verkaufs-, Bestands- und Bestandsstatistiktabelle“ erstellen können, die den Bestand automatisch zählen kann. Sobald Sie diesen Vorgang erlernt haben, können Sie sich von mühsamer Arbeit befreien und die Poesie und die Ferne genießen. Kommen Sie und werfen Sie einen Blick darauf!

Wie erstellt man eine Einkaufs-, Verkaufs- und Lagertabelle, die den Lagerbestand sowie die ein- und ausgehenden Mengen automatisch zählen kann? Tatsächlich ist dafür keine schwierige Technologie erforderlich. Sie müssen nur vier Funktionen beherrschen und einige davon haben Grundlegende Excel-Bearbeitungs- und Satzfähigkeiten. Sie können es selbst erstellen.

Diese vier Funktionen sind: vlookup, iferror, sumif und if. Lassen Sie sich anschließend vom Veteranen Schritt für Schritt durch die Erstellung dieser Ein- und Ausstiegstabelle führen.

Funktionsbeschreibung „Einkauf, Verkauf, Lagerbestand sowie Eingangs- und Ausgangsstatistiktabelle“:

Echtzeitstatistikfunktion: Sie müssen nur die Ausgangs- und Eingangsflusstabelle gemäß dem vorgeschriebenen Format aufzeichnen und können diese automatisch ausführen Echtzeitstatistiken zum aktuellen Lagerbestand sowie zu ein- und ausgehenden Mengen.

Intelligente Erinnerungsfunktion: Wenn der Lagerbestand eines Artikels unter der Sicherheitsbestandsmenge liegt, wird er automatisch markiert, um einen Warneffekt zu erzielen.

Die Zusammensetzung der „Einkaufs-, Verkaufs-, Lagerbestands- und Eingangs- und Ausgangsstatistiktabelle“:

Entsprechend den grundlegendsten Anforderungen erfordert die Erstellung einer Einkaufs-, Verkaufs-, Lagerbestands- sowie Eingangs- und Ausgangstabelle normalerweise drei Teile: Grunddaten Tabelle (auch Basisinformationstabelle genannt), Ein- und Ausgangsdatensatztabelle (auch Fließwasserdetailtabelle genannt), Bestandsstatistiktabelle (auch Ergebnisabfragetabelle genannt). Im Folgenden werden die Methoden dieser drei Teile beschrieben.

1. Basisdatentabelle

Die Tabelle wurde entsprechend den tatsächlichen Anforderungen des Unternehmens entworfen und basiert auf einem Grundprinzip. Sie sollte in der Lage sein, alle Attribute des Artikels widerzuspiegeln, und jedes Attribut sollte separat gespeichert werden Spalte. Die Tabelle muss nicht schön sein und es dürfen keine verbundenen Zellen angezeigt werden.

Das Bild unten ist beispielsweise eine relativ standardisierte Basisdatentabelle:

Hinweis: Die Seriennummer ist nicht erforderlich, nur zur einfacheren Suche; Statistiken verwenden normalerweise Produktcodes als einzige Grundlage Firma Wenn das Produkt keinen Code hat, kann die Seriennummer als Code verwendet werden.

Um die Genauigkeit der statistischen Daten sicherzustellen, müssen bei neuen Produkten Datensätze zur Tabelle hinzugefügt werden. Wenn veraltete Produkte vorhanden sind, müssen die ursprünglichen Datensätze nicht gelöscht werden. ??

Die Datenspalten in der Tabelle müssen grundlegende Produktinformationen sowie das Datum und die Menge der eingehenden und ausgehenden Lager enthalten. Das Format ist ungefähr:

In der Flusstabelle oben gibt es nur blaue A, E , F und G. Die Spalten müssen zeitnah erfasst werden. Grundlegende Informationen wie Klassifizierung, Name und Einheitenspalten werden automatisch über Formeln generiert. Sie haben es sicher erraten, es ist Zeit für vlookup!

Ja, hier kommt vlookup ins Spiel. Auf dem Bild unten können Sie sehen, dass die drei Spalten nach der Kodierung alle mit der vlookup-Funktion abgerufen werden. Die Formel in Zelle B2 lautet: =VLOOKUP($A2, Basisdatentabelle!$B:$E,COLUMN(B1),0)

Formelinterpretation: vlookup erfordert insgesamt vier Parameter und die grundlegenden Das Format ist

=vlookup(Suchwert, Suchbereich, Anzahl der Spalten, genaue Suche)

=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)Erster Parameter $A2 stellt den Inhalt dar, den Sie suchen möchten. Beachten Sie, dass Sie <code>$ vor A hinzufügen müssen, um Fehler beim Ziehen nach rechts zu vermeiden.

• Der zweite Parameter Basisdatentabelle!$B:$E stellt den zu durchsuchenden Bereich dar (die zuvor im Artikel eingeführte Basisdatentabelle). erste Spalte, da sich die Codierung in der Basisdatentabelle befindet Spalte B, daher beginnt der Bereich auch in Spalte B statt in Spalte A. Dies muss beachtet werden, da viele Anfänger an dieser Stelle Fehler machen, wenn sie vlookup verwenden Der dritte Parameter stellt die Rückgabe dar. Der Inhalt ist die Spaltennummer des Suchbereichs. Da die Formel nach rechts gezogen werden muss, verwenden wir column(B1) als zurückgegebene Spaltennummer. Die Funktion von

  🎜column besteht darin, die Spaltennummer des Parameters abzurufen. Wir möchten Spalte C in der Basisdatentabelle $B:$E zurückgeben, also Spalte 2. Die Spaltennummer von Zelle B1 in der gesamten Parameter-Basisdatentabelle ist 2, also Spalte wird hier verwendet. (B1)Gibt die Anzahl der zurückzugebenden Spalten an. Wenn die Formel nach rechts gezogen wird, wird B1 zu C1 und die Spaltennummer ändert sich von 2 auf 3, wodurch der Zweck erreicht wird, eine Formel nach rechts zu ziehen, um mehrere Spaltenverweise zu vervollständigen. 🎜🎜🎜🎜Der letzte Parameter 0 bedeutet exakte Suche. 🎜

Die letzten drei Spalten der Tabelle, das Datum sowie die ein- und ausgehende Menge, können entsprechend der tatsächlichen Situation erfasst werden. Unter normalen Umständen ist dieses Flussdiagramm abgeschlossen, aber um es intelligenter zu nutzen, kann auch der Vlookup-Teil optimiert werden.

Wenn wir den Produktcode eingeben, liegt möglicherweise ein Fehler in der Eingabe vor (oder ein neuer Code, der nicht in der Basisdatentabelle enthalten ist). Zu diesem Zeitpunkt erhalten wir einige verstümmelte Codes:



Der Effekt Es sieht nicht sehr schön aus. Daher ist es notwendig, eine andere Funktion iferror zur Zusammenarbeit mit vlookup einzuladen, um dieses Problem zu lösen: =IFERROR(VLOOKUP($A2, Basisdatentabelle!$B:$ E,COLUMN(B1),0)," Bitte überprüfen Sie, ob die Kodierung falsch ist! ")=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


Für einige Freunde ist dies möglicherweise das erste Mal, dass sie die iferror-Funktion sehen. Lassen Sie mich sie kurz vorstellen:
=iferror (Formel, der Inhalt wird angezeigt, wenn das Formelergebnis falsch ist) Die Formel erfordert nur zwei Parameter, der erste Parameter ist eine Formel und der zweite Parameter ist der Inhalt, der angezeigt werden muss, wenn das Formelergebnis vorliegt ist falsch. In diesem Beispiel ist der erste Parameter vlookup. Wenn das Ergebnis von vlookup korrekt ist, wird der erforderliche Inhalt angezeigt: Codierung hat Bitte versehentlich prüfen! Hinweis: Handelt es sich bei dem anzuzeigenden Inhalt um Text, muss dieser in Anführungszeichen gesetzt werden.

3. Inventarstatistiktabelle

Die Funktion dieser Inventarstatistiktabelle besteht darin, den Inventarstatus aller Produkte anzuzeigen In Echtzeit gibt es ungefähr die folgenden Informationen: kumulierte Ausgangsmenge, kumulierte Eingangsmenge und aktuelle Lagerbestandsmenge, eine Sicherheitsbestandsmenge und ob der Lagerbestand vergriffen ist.

🎜Für diese Statistiktabelle muss kein separates Blatt erstellt werden. Sie müssen nur die soeben aufgeführten Inhalte am Ende der Basisdatentabelle hinzufügen. Das Format ist wie folgt: 🎜🎜🎜🎜Sie können sehen, dass nach der Basisdatentabelle sechs hinzugefügt werden Spalteninhalte, es müssen nur der Anfangsbestand und die Sicherheitsbestandsmenge eingegeben werden. Die kumulierte Ausgangsmenge und die kumulierte Eingangsmenge werden alle durch Formeln ermittelt. Im Folgenden finden Sie eine kurze Erläuterung dieser Felder Bestand: Man kann ihn auch als Bestandsübertrag bezeichnen. Der ursprüngliche Bestand wird erfasst, wenn diese Tabelle mit den ein- und ausgehenden Bestandsstatistiken aktiviert ist. 🎜🎜Kumulative ausgehende Menge (Spalte G): Verwenden Sie die Formel =SUMMEWENN (Tabelle für ausgehende und ausgehende Datensätze! A:A, B2, Tabelle für ausgehende und ausgehende Datensätze! F:F), um Statistiken zu erhalten: 🎜 🎜🎜🎜Formelanalyse : sumif-Funktion Drei Parameter sind erforderlich, und die Grundstruktur ist =SUMIF (Bedingungsbereich, Bedingung, Summationsbereich)🎜
• 🎜Der erste Parameter Eingehende und ausgehende Datensatztabelle! 🎜🎜<li>🎜Der zweite Parameter <code>B2 repräsentiert die Bedingung der vorherigen Bedingung Spalte sollte erfüllen (entsprechend diesem Zeilenelementcode); 🎜🎜
• 🎜Der dritte Parameter Eingehende und ausgehende Datensatztabelle: F bedeutet die Summierung der Spalten, die die Bedingungen erfüllen. 🎜🎜🎜🎜Ersetzen Sie auf die gleiche Weise den dritten Parameter Inbound and outbound record table!$F:$F durch Inbound and outbound record table!$G:$G um die gesamte eingehende Menge zu erhalten (Spalte H): 🎜🎜🎜🎜Aktuelle Lagerbestandsmenge: Anfangsbestand verwenden – kumulierte Ausgangsmenge + kumulierte Eingangsmenge 🎜🎜🎜🎜Sicherheitsbestandsmenge: In diesem Beispiel ist 50 eingestellt, was entsprechend der Situation jedes Produkts bestimmt werden kann. Dieses Element muss manuell eingegeben werden. 🎜🎜Ist es nicht vorrätig: Hier wird die IF-Funktion verwendet, die Formel lautet: =IF(I2>J2,"","Nicht vorrätig")🎜🎜🎜🎜Das Grundformat der If-Funktion ist if(condition, Established Das Ergebnis ist erforderlich, wenn es nicht festgelegt ist)
; In diesem Beispiel ist die Bedingung I2>J2, also es Es wird davon ausgegangen, dass, wenn die aktuelle Lagerbestandsmenge größer als die Sicherheitsbestandsmenge ist, ein Leerzeichen erhalten wird. Sie erhalten jedoch das Wort „nicht vorrätig“. 🎜🎜Legen Sie gleichzeitig eine bedingte Formatierung für diese Spalte fest. Verwenden Sie bei Engpässen Farbe, um einen auffälligen Effekt zu erzielen. 🎜🎜Die Einstellungsmethode besteht darin, Spalte k auszuwählen, auf [Bedingte Formatierung] → [Zellenregeln hervorheben] → [Gleich] zu klicken: 🎜🎜🎜🎜🎜Geben Sie das Wort „Nicht vorrätig“ in das Feld links ein und wählen Sie aus, was Sie benötigen rechts Nachdem der Effekt erreicht ist, bestätigen Sie. 🎜



An dieser Stelle kann eine automatische statistische Ein- und Ausstiegstabelle problemlos implementiert werden! Mit diesem Tool müssen Sie sich keine Sorgen mehr machen, dass der Lagerbestand von Tausenden von Artikeln falsch berechnet wird. Sobald Sie feststellen, dass ein Mangel vorliegt, können Sie den Einkauf anweisen, ihn zu kaufen, und die Effizienz hat sich ebenfalls verbessert!

Abschließend möchte ich erklären, dass die Designideen für ähnliche eingehende und ausgehende statistische Tabellen ungefähr gleich sind. Einige Optimierungen können basierend auf den tatsächlichen Anwendungsbedingungen vorgenommen werden. Wenn es einen Stückpreis und andere Informationen gibt, können diese hinzugefügt werden die Grunddatentabelle und dann Menge * Stückpreis verwenden, um den Betrag zu erhalten.

Es ist eine gute Idee, die Datengültigkeit zu verwenden, um die Dateneingabe zu standardisieren. Wenn beispielsweise die Kodierung eindeutig sein muss, können Sie die Gültigkeit festlegen, um eine wiederholte Eingabe zu verhindern (Sie können eine Nachricht hinterlassen, wenn Sie nicht wissen, wie das geht mach das noch nicht).

Stellen Sie den Formelschutz ein, um zu verhindern, dass Fehlbedienungen Formeln zerstören und die Genauigkeit von Daten usw. beeinträchtigen.

Verwandte Lernempfehlungen: Excel-Tutorial

Das obige ist der detaillierte Inhalt vonPraktische Excel-Kenntnisse teilen: Funktionen geschickt nutzen, um eine automatische statistische Einkaufs-, Verkaufs- und Lagertabelle zu erstellen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!