작업 예시 · 회계사·감사인용
의회 지출 데이터를 정리하고 요약해 중복 여부를 테스트하기
내부 감사인이 텔퍼드 앤드 레킨(Telford & Wrekin) 의회가 공개한 2025년 12월 £100 초과 지출을 다운로드했습니다. 한 시트에 결제 3,161건, 맨 아래 합계 행, 공급업체명에 섞인 불필요한 공백이 있었습니다. 는 한 행도 잃지 않고 시트를 정리했고, 의회 합계와 1페니까지 일치하는 수식 기반 Summary 시트와 차트를 추가했으며, 중복 가능성이 있는 결제 6건을 표시했습니다. Summary 시트는 두 번째 요청에서 만들어졌고, 표시한 행에 다는 Excel 메모는 만들어지지 않았습니다.
1업로드 파일 읽기: 결제 3,161건과 맨 아래 합계 행
- 한 행도 잃지 않음결제 3,161건이 원래 순서대로 유지되며 대변 전표도 포함됩니다. 금액 합계는 이 의회 자체 합계인 £20,034,704.27과 여전히 일치합니다.
- 붙여 넣은 숫자가 아닌 수식으로 된 요약분야별 지출과 상위 10개 공급업체는 데이터 시트에 대한 SUMIF와 COUNTIF이며, 진짜 Excel 차트가 있습니다. 셀을 클릭하면 출처를 따라갈 수 있습니다.
- 같은 통합 문서에서의 감사 테스트중복 테스트는 데이터 시트의 한 열입니다. 금액, 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.
시트를 읽고 세 가지 함정을 찾았습니다
3163행은 열 전체에 대한 이름 없는 SUBTOTAL이라, 단순 합계는 그 달을 두 번 셉니다. 공급업체명 스무 개는 끝에 공백이 있어 'British Gas Trading Ltd'가 두 개의 공급업체로 나타납니다. 또한 날짜 열에는 시각이 포함되어 있습니다.
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가 제공됨
다운로드한 파일과 대조했습니다
전달된 시트를 의회 파일과 행 단위로 비교했습니다.
다운로드한 파일과 같은 순서의 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.
'중복 가능성' 열을 추가하고 세 쌍을 표시했습니다
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 대변 전표도 함께 보여 줍니다. 금액은 바꾸지 않았지만 요청 중 두 가지를 하지 않았습니다
이 열의 합계는 여전히 £20,034,704.27이고 Summary 시트는 그대로입니다. Excel 메모 7개와 Summary 시트의 표시된 행 표는 만들어지지 않았으며, 답변에서도 그렇게 밝혔습니다.
- 중복 가능성 열6행 표시됨
- 금액, Summary 시트, 차트변경 없음
- Excel 메모 7개만들지 않음
- Summary 시트에서 표시된 행만들지 않음
같은 테스트를 직접 실행해 확인했습니다
같은 공급업체, 금액, 날짜, £5,000 이상, 의회 파일 전체 대상.
이 테스트로 찾은 것은 191, 192, 405, 420, 838, 840행으로 정확히 여섯 건이며, 표시된 행과 같습니다.
금액은 바뀌지 않았습니다. 파일에 메모는 없고, Summary 시트는 여전히 30행에서 끝납니다.
파일
파일 출처: Telford & Wrekin Council, “£100 초과 지출”, 2025년 12월, 지방정부 투명성 규정에 따라 공개. Open Government Licence v3.0에 따라 사용이 허가된 공공부문 정보를 포함합니다. 지방자치단체는 이 사례를 검토하거나 승인하지 않았습니다.
내 스프레드시트로 해 보기
대부분의 AI 도구는 다시 붙여 넣을 숫자를 줍니다. 는 통합 문서 안에서 작업합니다. 한 행도 잃지 않고 데이터를 정리하고, 결제 내역까지 추적할 수 있는 수식으로 요약을 만들며, 문의할 만한 거래를 표시합니다.
계속 읽기
