EJEMPLO PRÁCTICO · CONTABLES Y AUDITORES
Limpiar los datos de gasto de un ayuntamiento, resumirlos y buscar duplicados
Un auditor interno descargó el gasto publicado de más de 100 £ del Ayuntamiento de Telford & Wrekin para diciembre de 2025: 3161 pagos en una hoja, una fila de total al final y espacios sobrantes en los nombres de proveedor. ordenó la hoja sin perder una fila, añadió una hoja de resumen con fórmulas y un gráfico que coinciden con el total del ayuntamiento al céntimo, y marcó seis posibles pagos duplicados. La hoja de resumen requirió una segunda petición, y los comentarios de Excel en las filas marcadas no se crearon.
1Lee el archivo subido: 3161 pagos y una fila de total al final
- Ni una fila perdida3161 pagos conservados en su orden, notas de crédito incluidas. Los importes siguen sumando el total del propio ayuntamiento, 20 034 704,27 £.
- Resúmenes como fórmulas, no números pegadosEl gasto por área y los diez mayores proveedores son SUMAR.SI y CONTAR.SI sobre la hoja de datos, con un gráfico real de Excel. Haz clic en una celda y rastréala hasta su origen.
- Pruebas de auditoría sobre el mismo libroLa prueba de duplicados es una columna de la hoja de datos. Los importes, la hoja de resumen y el gráfico quedan como estaban.
01 · SOLICITUD 1
ordenó la hoja y puso el resumen en el lugar equivocado
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.
Leyó la hoja y encontró sus tres trampas
La fila 3163 es un SUBTOTAL sin etiqueta de toda la columna, así que una suma ingenua cuenta el mes dos veces. Veinte nombres de proveedor terminan en un espacio, de modo que «British Gas Trading Ltd» aparece como dos proveedores. Y la columna de fecha lleva una hora del día.
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Área, proveedor, fecha e importe de algunas filas (otras columnas ocultas). La última fila amarilla es el total sin etiqueta. Eliminó la fila de total, recortó los nombres y añadió una columna Fecha
Los 3161 pagos permanecen en el orden de la descarga, con las 33 notas de crédito incluidas. Al recortar los espacios, 616 nombres de proveedor pasan a 610. La columna G contiene =INT(F2) y así hasta el final de la hoja: la fecha sin la hora.
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 ReportLa hoja limpia: columna Date G nueva en verde; los datos terminan ahora en la fila 3162. Calculó el gasto por área, pero lo entregó como CSV en lugar de una hoja de resumen
Los totales eran correctos. Llegaron como un archivo aparte junto al libro, no como la hoja que pedía la solicitud, y la respuesta dijo que la hoja de resumen no estaba hecha.
- hoja «Transparency Report»ordenado: fila de total eliminada, nombres recortados, columna Fecha añadida
- Hoja de resumenno creados; llegó en su lugar un CSV del gasto por área
Comprobado con la descarga
Comparamos la hoja entregada con el archivo del ayuntamiento, fila por fila.
3161 filas en el mismo orden que la descarga, ningún nombre de proveedor con un espacio sobrante, 33 importes negativos.
Los importes suman 20 034 704,27 £, el propio subtotal del ayuntamiento.
02 · SEGUIMIENTO
Al decirle que faltaba la hoja de resumen, la añadió como fórmulas y un gráfico
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.
Se añadió una hoja de resumen con las 11 áreas, de mayor a menor
Cada total es una función SUMAR.SI sobre la hoja de datos, con un total general debajo. Debajo, los diez mayores proveedores por gasto, cada uno con un total SUMAR.SI y un recuento de transacciones CONTAR.SI. Los números verdes son fórmulas; haz clic en uno para verla.
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 ReportSummarynuevoLa hoja de resumen tal como se entregó. Dibujó un gráfico de barras real de Excel con el gasto por área
El gráfico lee la tabla de resumen, así que se mueve con las fórmulas. Aquí se dibuja de nuevo a partir de los datos que hay dentro del archivo entregado.
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 CommunicationsEl gráfico de la hoja de resumen. Comprobado con la descarga
Recalculamos los totales por área y los diez primeros a partir del archivo del ayuntamiento y comparamos.
Las once áreas y los diez proveedores coinciden al céntimo; el total general es 20 034 704,27 £.
Los recuentos de transacciones son correctos: Bowmer & Kirkland 2, PAVE-AWAYS 9, Veolia 1.
03 · SOLICITUD 2
Al pedirle una prueba de pagos duplicados, marcó seis pagos y omitió los comentarios
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.
Se añadió una columna Posible duplicado y se marcaron tres pares
A-STAR TRANSPORT MIDLANDS el 22 de diciembre, Acorn Education & Care el 16 de diciembre (filas 405 y 420, no contiguas) y Virgin Media Business el 9 de diciembre: mismo proveedor, mismo importe, misma fecha, 5000 £ o más.
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 ReportSummaryProveedor, fecha, importe y columna I nueva de las seis filas marcadas (amarillas), con la fila 190 y el abono Virgin Media de la fila 853 como contraste. No cambió ningún importe y dejó dos partes de la petición sin hacer
La columna sigue sumando 20 034 704,27 £ y la hoja de resumen está como estaba. Los siete comentarios de Excel y la tabla de filas marcadas en la hoja de resumen no se crearon, y la respuesta lo dijo.
- Columna Posible duplicadoseis filas marcadas
- Importes, hoja de resumen, gráficosin cambios
- Siete comentarios de Excelno creados
- Filas marcadas en la hoja de resumenno creados
Comprobado ejecutando nosotros mismos la misma prueba
Mismo proveedor, importe y fecha, 5000 £ o más, sobre el archivo del ayuntamiento.
Nuestra prueba encuentra exactamente las filas 191, 192, 405, 420, 838 y 840, las seis que están marcadas.
Ningún importe cambió. El archivo no tiene comentarios, y la hoja de resumen sigue terminando en la fila 30.
Los archivos
De dónde proviene el archivo: Telford & Wrekin Council, «Gastos superiores a £100», diciembre de 2025, publicado según el Código de Transparencia Local. Contiene información del sector público bajo Open Government Licence v3.0. El ayuntamiento no ha revisado ni avalado este ejemplo.
Trae tu propia hoja de cálculo
La mayoría de las herramientas de IA te dan números para volver a pegar. trabaja dentro de tu libro: limpia los datos sin perder una fila, crea resúmenes como fórmulas que puedes rastrear hasta los pagos y marca las transacciones que merecen una consulta.
SIGUE LEYENDO
Lee otro ejemplo práctico
Contadores y auditores
50 páginas de facturas escaneadas convertidas en un libro mayor de cuentas por pagar verificado
Banqueros y consultores
PDF de resultados financieros a Excel, con fórmulas interanuales y un gráfico
Banqueros y consultores
Estimar el mercado estadounidense de operación y mantenimiento solar con datos de la EIA, en el mismo libro
Ver los 19 ejemplos prácticos →Todos los ejemplos para Contadores y auditores →