Excel で大量の製品データを編集するための 10 の数式

Excel スプレッドシートを使用して製品データを修正したことがある人なら誰でも、特に e コマース マネージャーは、製品を一括編集する作業がいかに迅速に圧倒されるかを知っています。 製品が大量にあるため、どこから始めればよいかわかりません。 セルの値を見落としたり、間違った内容を入力したりするのは簡単です。

ただし、それぞれの問題に適切な数式を使用すると、Excel スプレッドシートでの管理と編集がはるかに簡単になります。

このブログでは、Excel で大量の製品データを編集するために使用できるいくつかの魔法の数式について説明します。

それでは、始めましょう。

式 1: 製品名と説明の先頭のゼロを削除する

Excel で製品番号に先行ゼロを使用している場合は、それらを削除する必要があります。 残念ながら、Excel では、製品の名前と説明から先頭のゼロを簡単に削除する方法は提供されていません。

たとえば、製品のリストが 00123450123456 で、最初の 2 つのゼロを削除して 123450123456 にするとします。

先行ゼロを取り除くために必要なのは、セルから既存の書式設定を削除することだけです。

最初のステップ: ゼロで始まる整数を含むセルを選択します。

2 番目のステップ: [ホーム] タブをクリックします。

3 番目のステップ: [数値] グループの [数値形式] ドロップダウンをクリックします。

4 番目のステップ: [一般] をクリックします。

計算式 2: 単価 (合計金額/数量) と単価 (合計金額/数量) を計算する

あなたはビジネスを営んでおり、さまざまな価格、サイズ、数量の製品を持っています。 このシナリオで製品の総コストを計算したいと考えています。

Excel には、各製品の単価と単価を計算するオプションが用意されています。 単価は、測定単位あたりの総コストです。 これは、総コストをその製品の数量で割ることによって計算できます。 これにより、単価が計算されます。 単価は、単価にその製品の数量を掛けたものです。

単位コスト式 = 総コスト / 生産または購入された総単位

単価計算式 = 合計金額/生産された単位数。

小売業者とベンダーの価格を設定する方法を確認してください

数式 3: コンマ、コロン、スラッシュ、ダッシュ、またはその他の区切り文字でセル値を分割する

Excel でデータを操作しようとしている場合、データは 1 つの列に格納されることが多く、複数の列で使用できるようにする必要があります。 たとえば、列 A に保存されている顧客名のリストがありますが、列 B と C には顧客の電話番号が必要です。

または、Excel ワークシートに単語と数字の文字列があり、それを 2 つの列に分割したいとします。 目標は、文字列の前半をダッシュ​​で区切り、それらを別々のセルに分割することです。

たとえば、次の単語と数字の文字列があるとします: Shoes-123. 'Shoes' と '123' の 2 つの列になります。

構文: =SPLIT(A2,”-”)

ただし、Red-long-frog のように、文字列に複数のダッシュが付いている場合に問題が発生します。 この場合、単純な分割関数を使用しても機能しません。 代わりに、次に示す右、左、または中央の分割関数を使用する必要があります。

=LEFT(B1,2)

また

=右(B2,4)

また
=ミドル(B2,5,2)

数式 4: VLOOKUP 関数を使用して値を検索する

1 つの属性を持つすべての属性を検索したいということはありませんか。 たとえば、誰かのメール アドレス、自宅の住所、年齢、性別が必要です。 1 つの VLOOKUP 式でそれを行うことができます。 VLOOKUP 関数を使用して、名前に基づいて顧客の電話番号を取得できます。 これは、すぐにアクセスする必要がある情報がたくさんある場合に最適です。

VLOOKUP は「Vertical Lookup」の略です。 これは、同じ行の別の列から値を返すために、列 (いわゆるテーブル配列) で特定の値を探すように Excel に指示する関数です。

VLOOKUP 関数は、次の 4 つの部分で構成されています。

• 探している値。
• 探している値と、探している範囲。
• 選択した範囲内の戻り値を持つ列の番号。
• 探している値と完全に一致させるには、0 または FALSE を使用します。 近似一致の場合は、1 または TRUE を使用します。

構文: VLOOKUP([値], [範囲], [列番号], [偽または真])

数式 5: 数式を使用して空白の値を除外する

最も時間のかかるタスクの 1 つは、e コマース ストアの製品データをクリーンアップすることです。 場合によっては、適切に行わないと、売上の損失につながることさえあります。

このセクションでは、数式を使用してバルク製品データの空白値を除外する方法を学習します

「式」列は、値の存在をチェックする簡単な式を計算します。 値が存在する場合は、それを返します。 そうでない場合は、空白を返します。 このようにして、空白の値がゼロに変換されるフィルターとして数式列を使用できます。

「式」列は、値の存在をチェックする簡単な式を計算します。 値が存在する場合は、それを返します。 そうでない場合は、ゼロを返します。

構文: =FILTER(data,(rng1<>””)*(rng2<>””)*(rng3<>””))

式 6: 式を使用して関心のある行のみを選択する

大量の製品データに対して Excel で関心のある行のみを選択することは、最も一般的な Excel タスクの 1 つです。 数式を使用して、Excel で目的の行のみを選択する方法を紹介します。

製品のリストがあり、各製品の在庫数を知る必要があるとします。 製品ごとに新しい列を追加する必要はありません。 代わりに、数式を使用して、在庫切れの商品を除くすべての商品を選択できます。

マウスまたは Ctrl + Shift + 下矢印キーの組み合わせを使用して、すべての製品の周りにセルを描画することから始めます。

その後、IF 関数を適用できます。

=IF(AND(A2=”スコア”,A2>=90),1,””)

– ここで、A2 はスコアと =AND(A2=”Score”,A2>=90) のリストを含む列です – セル値をチェックし、パターン「スコア」がセルに表示される場合はtrueを返します。セルの値が 90 を超えています。

数式 7: 数式を使用して日付をテキストまたは数値に変更する

大量のデータ入力を伴うあらゆる種類の作業に Excel を使用している場合は、数式を使用して日付をテキストまたは数値に変更する方法を知っておく必要があります。 これが必要になる最も一般的なシナリオは、CSV ファイルから製品データをインポートする場合です。

たとえば、製品データをインポートすると、日付を含むセルがいくつかあることがよくあります。 何が起こるかというと、Excel が「このタイプの値を変換できません」のようなエラーを表示します。 これを回避するには、次の数式のいずれかを使用して日付をテキストまたは数値に変更し、Excel でこれらの CSV ファイルをインポートする際に問題が発生しないようにします。

構文: =VALUE(テキスト)

構文: =DATEVALUE(日付テキスト)

数式 8: 数式を使用して一括商品データの重複を見つける

ビジネス データに重複したレコードが含まれることは珍しくありません。これは、レポート作成時に問題になる可能性があります。 Excel シートに重複するレコードがあるかどうかを確認するには、数式ツールを使用できます。 Microsoft Excel のフィルター機能を使用して重複を削除する方法も学習できます。

ただし、数式を使用すると、一括商品データの重複を簡単に見つけることができます。 Excel スプレッドシートで COUNTIF 式を使用して、'TRUE' または 'FALSE' 応答を取得し、重複値を検出できます。

True = 重複値

False = 一意の値

数式の「A」を、重複を探しているセル列の文字に変更するだけです.

構文: =COUNTIF(A:A,A1)>1

数式 9: 数式を使用して、特定の属性を持つ製品の数をカウントする

特定の商品属性を持つ商品の数を集計して表示したい。 たとえば、「割引あり」という属性を 1 つ作成し、この属性を持つ商品の数を出力したいとします。

この Excel 式は、特定の属性に一致する製品の総数を計算するのに役立ちます。

特定の属性に一致する製品の総数をカウントするには、Excel で次の数式を使用できます。

たとえば、価格が $10 ～ $20 で青色の製品の総数を知りたいとします。 この場合、テーブルは次のようになります。

「色」の列を選択して適用

=COUNTIF(B2:B8,”青”,C2:C8,”)

式 10: 式を使用して製品名を製品 URL に変換する

エクセルで商品一覧を作っています。 次に、各製品名を製品 URL にします。 出来ますか？ はい、そうです！ これは動的セル参照/参照として知られており、INDEX 関数と MATCH 関数を使用して実現できます。

Excel では、単純な数式を使用して、製品名から URL を生成できます。 通常、このアプローチは、製品名をリンク テキストとして使用して製品へのリンクを作成する場合に便利で、手動で作成されたリンクを追跡する必要はありません。

これは 2 ステップのプロセスであり、次の 2 つの式が必要です。

ステップ 1: 名前からすべての大文字を削除し、すべてのスペースを「-」に置き換えます

構文: =LOWER(SUBSTITUTE(B2,””,”-”))

ステップ 2: 製品名からすべての特殊文字を削除する

構文: =REGEXREPLACE(B2,”[^A-Za-z1-9-]+”,””)

PIM を使用して製品データを簡単に一括編集する

次のような状況に陥ったことはありませんか?

1. Excel シートに多数の製品データがあります。

2. ただし、データは完全ではなく、他のソースからの情報で更新する必要があります。

3. 大量のデータを 1 つずつ編集する必要があり、時間がかかります。 または、いくつかの製品を忘れてしまい、それらをもう一度見つけて、不足しているすべての情報を入力する必要があります。

4.商品データを更新する場合、VBAコードを使ってExcelファイルを更新する必要がありますが、すぐに行うのは難しいです。

Shopifyで、CSVファイルなどのExcelスプレッドシートから商品を追加する場合、手動で追加した商品データは、正しい形式でない場合、翻訳で失われます。 たとえば、製品を追加するときに間違ったフィールド名または構造を使用すると、製品情報が失われ、最初からやり直す必要があります。

この苛立たしく脅迫的な問題をきっぱりと取り除くことができると言ったらどうしますか? 上記の状況に直面する必要はないと言ったらどうしますか？ まあ、方法はあります。

Apimio は製品情報管理ソリューションであり、e コマース マネージャーが製品を一括で編集および管理し、フォーマットやデータ損失の問題を解決するのに役立ちます。

実際、スプレッドシートを PIM に残す理由はたくさんあります。 幸いなことに、それについてはすでに説明しました。 詳細については、こちらをお読みください。

PIM のためにスプレッドシートを残す 7 つの理由については、ブログをご覧ください。

そして、なぜそこにいないのでしょうか？ 結局のところ、PIM はスプレッドシートに代わるものとして普及しています。 すべての情報管理を容易にするために存在します。

結論

現在、Shopify ビジネスを担当していますか、または e コマース会社で e コマース マネージャーとして働いていますか? おそらく、会社の方向性に影響を与えるために製品情報管理システムを採用することを検討したことがあります。 おそらく、e コマースの世界に慣れる方法として、Shopify への切り替えを検討しているでしょう。

ケースが何であるかに違いはありません。 サーフィンが途切れ途切れになったとき、PIM は信頼性が高く、コントロールを維持するための非常に便利なツールになる可能性があります。 PIM-Shopify 統合の強力なスーパーパワーを最大限に活用してください。

この投稿が、Excel で大量のデータをすばやく簡単に編集する方法を示すのに役立つことを願っています。 独自のヒントがある場合は、下のコメント セクションに残してください。