実例 · 会計士・監査人向け
議会の支出データを整え、集計し、重複をテストする
社内監査人が、テルフォード・アンド・レキン（Telford & Wrekin）議会が公表した2025年12月分の£100超の支出をダウンロードしました。1枚のシートに3,161件の支払い、末尾に合計行、仕入先名には余分なスペースがありました。は1行も失わずにシートを整え、議会の合計と1ペニー単位で一致する数式のSummaryシートとグラフを追加し、重複の可能性がある支払い6件にフラグを付けました。Summaryシートは2回目の依頼で作成され、フラグを付けた行へのExcelコメントは作成されませんでした。
1アップロードを読み取り：3,161件の支払いと末尾の合計行
- 1行も失っていません3,161件の支払いを元の順序のまま残し、貸方票も含めました。金額の合計は、議会自身の合計 £20,034,704.27 と今も一致します。
- 貼り付けた数字ではなく、数式による集計分野別の支出と上位10社の仕入先は、データシートに対するSUMIFとCOUNTIFで、本物のExcelグラフも付きます。セルをクリックすれば、元までたどれます。
- 同じブックでの監査テスト重複テストは、データシート上の1列です。金額、Summaryシート、グラフは元のままです。
01 · リクエスト1
はシートを整えましたが、集計を誤った場所に置きました
This is Telford & Wrekin Council's spend data for December 2025 (sheet 'Transparency Report'). Please tidy it for analysis and add a summary, in this workbook: 1. Delete the unlabelled total row at the bottom (row 3163, a SUBTOTAL formula) so only the transactions remain. Keep the credit notes (negative amounts). 2. Trim leading/trailing spaces in Supplier Name - some suppliers show up twice because of a trailing space, e.g. 'British Gas Trading Ltd '. 3. Add a 'Date' column next to Trans Date holding just the date (Trans Date also carries a time). 4. Add a 'Summary' sheet with: a. Total spend by Service Delivery Area, largest first b. Top 10 suppliers by total spend, with their number of transactions c. A bar chart of spend by Service Delivery Area Use formulas on the Summary sheet where you can, and keep the original row order.
シートを読み、3つの落とし穴を見つけました
3163行目は、列全体の見出しのないSUBTOTALなので、単純に合計すると同じ月が二重に数えられます。仕入先名のうち20件は末尾にスペースがあり、「British Gas Trading Ltd」が2社として現れます。さらに日付列には時刻が含まれています。
A1Service Delivery AreaA E F G 1 Service Delivery Area Supplier Name Trans Date Amount 2 Adult Social Care AFC Telford Holdings Limited 17/12/2025 12:27 400.00 3 Adult Social Care AFC Telford Holdings Limited 17/12/2025 12:27 675.00 … 17 Adult Social Care British Gas Trading Ltd 08/12/2025 17:05 174.11 18 Adult Social Care British Gas Trading Ltd 08/12/2025 17:05 375.93 … 3161 Prosperity & Investment Steel Street Ltd 12/12/2025 12:09 1,520.00 3162 Prosperity & Investment PAVE-AWAYS LTD 18/12/2025 10:25 160,055.42 3163 =SUBTOTAL(9,G2:G3162) Transparency Report数行の分野・仕入先・日付・金額（他列は非表示）。黄色の最終行はラベルなしの合計。 合計行を削除し、名前をトリミングして、日付列を追加しました
3,161件の支払いはすべてダウンロード時の順序のままで、貸方票33件も含まれます。トリミングにより、仕入先名は616から610に減りました。G列には =INT(F2) が入り、以下同様に続きます。時刻を除いた日付です。
A1Service Delivery AreaA E F G H 1 Service Delivery Area Supplier Name Trans Date Date Amount 2 Adult Social Care AFC Telford Holdings Limited 17/12/2025 12:27 17/12/2025 400.00 3 Adult Social Care AFC Telford Holdings Limited 17/12/2025 12:27 17/12/2025 675.00 … 17 Adult Social Care British Gas Trading Ltd 08/12/2025 17:05 08/12/2025 174.11 18 Adult Social Care British Gas Trading Ltd 08/12/2025 17:05 08/12/2025 375.93 … 3161 Prosperity & Investment Steel Street Ltd 12/12/2025 12:09 12/12/2025 1,520.00 3162 Prosperity & Investment PAVE-AWAYS LTD 18/12/2025 10:25 18/12/2025 160,055.42 Transparency Report整理後のシート：新しいDate列Gは緑色、データは3162行目まで。 分野別の支出は算出されたが、Summaryシートではなくカンマ区切りのCSVで納品された
合計は正しいものでした。ただし、依頼されたシートではなく、ブックの隣に別ファイルとして届き、返信でもSummaryシートは未完了とされていました。
- Transparency Reportシート整理済み：合計行を削除、名前をトリミング、日付列を追加
- Summaryシート未作成。代わりに分野別支出のCSVが届きました
ダウンロードしたファイルと照合しました
納品されたシートを議会のファイルと1行ずつ比較しました。
3,161行がダウンロード時と同じ順序で並び、仕入先名に余分なスペースはなく、マイナスの金額は33件です。
金額の合計は £20,034,704.27 で、議会自身の小計と一致します。
02 · 追加依頼
Summaryシートがないと指摘され、は数式とグラフで追加しました
The Summary sheet is missing. Please add it to this workbook as I asked: total spend by Service Delivery Area (largest first), the top 10 suppliers with their number of transactions, and a bar chart of spend by area. Use formulas where you can.
11の分野を金額の大きい順に並べたSummaryシートを追加しました
各合計はデータシートに対するSUMIFで、その下に総合計があります。さらに下には、支出上位10社の仕入先が並び、それぞれSUMIFの合計とCOUNTIFの取引件数があります。緑の数字は数式です。クリックすると中身が見られます。
A1Telford & Wrekin Council — December 2025 spend: SummaryA B C D 1 Telford & Wrekin Council — December 2025 spend: Summary 2 Covering 3161 transactions, 1-31 December 2025 3 4 Total spend by Service Delivery Area (largest first) 5 Area Total spend 6 Prosperity & Investment 5,744,335.15 7 Neighbourhood & Enforcement Services 4,948,876.56 8 Education & Skills 4,609,122.15 9 Corporate Items 1,734,016.14 10 Housing, Commercial & Customer Services 1,254,821.61 11 Health & Wellbeing 971,198.15 12 Finance, People & IDT 263,360.71 13 Children's Safeguarding & Family Support 258,890.99 14 Adult Social Care 208,305.53 15 Policy & Governance 40,802.28 16 Corporate Communications 975.00 17 Grand total 20,034,704.27 18 19 Top 10 suppliers by total spend 20 Rank Supplier Total spend Transactions 21 1 Bowmer & Kirkland Ltd 1,884,772.10 2 22 2 PAVE-AWAYS LTD 1,661,965.98 9 23 3 Veolia ES T&W Ltd 1,482,145.55 1 24 4 PYRAMID SCHOOLS (HADLEY) LTD 1,462,179.11 5 25 5 Balfour Beatty Civils Limited 1,376,066.91 1 26 6 Id verde 1,067,695.39 6 27 7 SHROPSHIRE COMMUNITY HEALTH NHS TRUST 838,577.00 3 28 8 WORCESTERSHIRE COUNTY COUNCIL 756,693.52 1 29 9 Hawk Incentives Ltd (Crown Commercial Services) 631,235.42 6 30 10 SHROPSHIRE COUNCIL 538,607.76 3 Transparency ReportSummary新規納品されたSummaryシート。 分野別の支出を、本物のExcel棒グラフで描きました
グラフはSummaryの表を読み取るので、数式に合わせて動きます。ここでは、納品ファイル内のデータから描き直しています。
Spend by Service Delivery Area — December 2025
Prosperity & InvestmentNeighbourhood & Enforcement ServicesEducation & SkillsCorporate ItemsHousing, Commercial & Customer ServicesHealth & WellbeingFinance, People & IDTChildren's Safeguarding & Family SupportAdult Social CarePolicy & GovernanceCorporate CommunicationsSummaryシートのグラフ。 ダウンロードしたファイルと照合しました
議会のファイルから分野別の合計と上位10社を再計算し、比較しました。
11の分野と10社の仕入先の金額がすべて1ペニー単位まで一致し、総合計は £20,034,704.27 です。
取引件数は正しい：Bowmer & Kirkland は2、PAVE-AWAYS は9、Veolia は1。
03 · リクエスト2
重複支払いのテストを依頼すると、は6件にフラグを付け、コメントは省略しました
Now run a duplicate-payment test on the same workbook: 1. Add a 'Possible duplicate' column to the data sheet that flags rows where Supplier Name, Amount and Date all match another row and the amount is £5,000 or more. 2. Add an Excel comment on each flagged row: 'Query AP: possible duplicate - obtain invoice nos.' 3. Virgin Media Business also has a credit of -13,500.00 on 23/12/2025. Add a comment on that row: 'Credit same month as flagged Virgin Media duplicates - check if it reverses one of them.' 4. List the flagged rows in a small table on the Summary sheet. Don't change any amounts, and keep the Summary tables and chart from before.
「重複の可能性」列を追加し、3組にフラグを付けました
A-STAR TRANSPORT MIDLANDS（12月22日）、Acorn Education & Care（12月16日、405行目と420行目で隣り合っていません）、Virgin Media Business（12月9日）：同じ仕入先、同じ金額、同じ日付で、£5,000以上。
E1Supplier NameE G H I 1 Supplier Name Date Amount Possible duplicate … 190 A-STAR TRANSPORT MIDLANDS LTD 22/12/2025 5,100.00 191 A-STAR TRANSPORT MIDLANDS LTD 22/12/2025 5,600.00 Yes 192 A-STAR TRANSPORT MIDLANDS LTD 22/12/2025 5,600.00 Yes … 405 Acorn Education & Care Ltd 16/12/2025 32,547.95 Yes … 420 Acorn Education & Care Ltd 16/12/2025 32,547.95 Yes … 838 VIRGIN MEDIA BUSINESS 09/12/2025 12,894.84 Yes … 840 VIRGIN MEDIA BUSINESS 09/12/2025 12,894.84 Yes … 853 VIRGIN MEDIA BUSINESS 23/12/2025 -13,500.00 Transparency ReportSummaryマークした6行（黄色）の仕入先、日付、金額と新しい列I。比較のため、190行目と853行目のVirgin Mediaの貸方伝票も表示しています。 金額は変えませんでしたが、依頼のうち2点が未実施です
この列の合計は今も £20,034,704.27 で、Summaryシートは元のままです。7件のExcelコメントと、Summaryシート上のフラグ付きの行の表は作成されておらず、返信でもそう伝えていました。
- 重複の可能性の列6行にフラグ
- 金額、Summaryシート、グラフ変更なし
- Excelコメント7件未作成
- Summaryシート上のフラグ付きの行未作成
同じテストを自分たちで実行して確認しました
同じ仕入先・金額・日付で£5,000以上。議会のファイル全体が対象です。
このテストで見つかるのは191、192、405、420、838、840行のちょうど6件で、フラグが付いた行と一致します。
金額は変わっていません。ファイルにコメントはなく、Summaryシートは今も30行目で終わっています。
ファイル
ファイルの出典： Telford & Wrekin Council, 「£100を超える支出」、2025年12月、地方政府透明性規定に基づく公開。Open Government Licence v3.0で許諾された公共部門情報を含みます。自治体はこの例を審査・推奨していません。
自分のスプレッドシートで試す
多くのAIツールは、貼り戻すための数字を渡してきます。はあなたのブックの中で作業します。1行も失わずにデータを整え、支払いまでたどれる数式で集計を作り、問い合わせる価値のある取引にフラグを付けます。
続きを読む
