How to advance Excel data analysis with AI? Preparation and verification procedures before aggregation

When requesting AI to analyze data in Excel, first decide what each row represents'' and what to aggregate.'' Even if you can create a beautiful graph, if there are overlaps or mixed tax categories, the conclusion will change. We leave the AI ​​to suggest aggregation methods and formulas, and then check the final numbers against the original data.

What should be done before analyzing Excel with AI?

Sales and deposits are different metrics. First, specify whether you want to compare by sales date or check funds by deposit date.

How to check AI aggregate results?

Practice sales CSV includes sales of 10,000 yen and 20,000 yen for product A, and 15,000 yen of sales for product B and minus 5,000 yen for returns. The total is 40,000 yen, product A is 30,000 yen, and product B is 10,000 yen. This is fictitious data that aggregates all 4 items and is not actual results.

What to checkValues ​​calculated from the original dataComparison with AI results
Sales of product A10,000 yen + 20,000 yen = 30,000 yenAre both 2 details included?
Sales of product B15,000 yen - 5,000 yen = 10,000 yenAre returns treated as negative numbers?
Overall sales30,000 yen + 10,000 yen = 40,000 yenDoes the total by product match the overall total

First, manually calculate the total of the 4 items and check if it matches the AI ​​result. If you exclude returns or change a negative value to a positive value, the numbers will be different, so have them explain the processing conditions.

If you want to check using Excel, open the CSV and make sure that "Product" is in column B and "Amount (excluding tax/yen)" is in column C. If you enter =SUM(C2:C5) in an empty cell, the total will be 40,000 yen. By product, =SUMIF(B2:B5,"Product A",C2:C5) costs 30,000 yen, and =SUMIF(B2:B5,"B product",C2:C5) costs 10,000 yen. If the AI ​​results are different, first match the target line, return handling, and unit of amount.

How do you ask AI to perform analysis?

One line is the sales details. The amount is in yen excluding tax, and returned goods are negative numbers.
I would like to calculate the total sales by product for all rows.
Please check column types, blank fields, and duplicate item IDs before aggregation.
Please do not delete duplicate candidates arbitrarily, but indicate the target line.
Please include the number of rows included, excluded rows and reason, and grand total in the results.
Please also explain the aggregation procedure that can be reproduced in Excel.

When uploading files to external AI, use company-approved materials and environments. It is also important not to include information that is unnecessary for analysis, such as customer names.

Where can I check the AI ​​analysis results?

Number of records compares the number of input rows with the number of processed rows.Amount checks whether the sum of the totals for each category matches the total.Individual rows extracts extremely large values, returns, and blank fields and returns to the original transaction.

If you join multiple tables, check whether the number of rows or total amount has increased before and after the join. Connecting one-to-many data can result in counting the same sales multiple times.

What should I check before creating a graph?

Even if sales have increased from the previous month, a simple comparison cannot be made because the number of business days and product mix are different. Instead of letting the AI ​​freely guess "why the increase occurred," we divide it into factors that can be confirmed, such as the number of cases, unit price, and target period. If the cause is unconfirmed, leave it as a hypothesis.

FAQ

Can I use formulas created by AI as is?

Check the reference range, handling of blank fields, and changes when copying. Try it with a small amount of known data to see if it works as expected.

What if you do the same work every month?

Fixing the input format and verification conditions, and designing error notifications during updates will make it easier to continue.

Before consulting ZETTAi

Please prepare the target Excel file, column definitions, and what you want to know after aggregation. Regarding ZETTAi's AI system development/contracted development, you can consult with us regarding the scope of verification and processing automation before aggregation. The scope of individual support will be confirmed at the time of consultation.

Articles you may also want to read