Виправлення роздутого розміру файлу та повільного обчислення в Excel
Джефф Вір
Привіт люди. Джефф тут. Нещодавно я провів презентацію щодо ефективності Excel для групи аналітиків, в якій - серед іншого - я зазначив, що якщо вам коли-небудь доведеться перевести обчислення на «Вручну», можливо, з електронною таблицею щось не так. Ось слайд:
Це спонукало одного з учасників звернутися до мене за порадою щодо реструктуризації електронної таблиці саме з цією проблемою. Цей аналітик мав файл, у якому було лише 6000 рядків даних, але розмір файлу був приблизно 35 МБ, і після кожної зміни їй доводилося чекати принаймні хвилину, щоб файл перерахувався, перш ніж вона могла зробити щось інше.
Виявляється, з її файлами було дві проблеми, які легко було вирішити.
Розгублений діапазон
По-перше, виникла проблема з використовуваним діапазоном - область на аркуші, яка, на думку Excel, містить усі ваші роботи та дані. Ви можете дізнатись, що це за кожна електронна таблиця, натиснувши [Ctrl] + [End] і побачивши, до якої комірки це перенесе вас. Сподіваємось, це перенесе вас до самої нижньої, самої правої комірки, яку ви насправді використовували в аркуші:
Але іноді ви побачите, що це може занести вас далеко, далеко під ту клітинку. Можливо, аж до самого дна сітки:
Це погано. Чому? Оскільки, коли Excel зберігає файл, він включає інформацію про такі речі, як тип форматування комірок, який використовується в межах використовуваного діапазону. Якщо використовуваний діапазон включає мільйони комірок, які навіть не використовуються, інформація, яку Excel зберігає щодо цих комірок, може справді зменшити розмір файлу. Це саме те, що сталося у випадку з відповідною електронною таблицею. Після того, як ми скинули використаний діапазон, розмір файлів впав із 35 МБ приблизно до 2 МБ.
Часто ви можете скинути Використаний діапазон, просто вибравши всі порожні рядки під вашими даними, а потім видаливши їх. Для цього виділіть цілий рядок безпосередньо під вашими даними, потім натисніть [Ctrl] + [Стрілка вниз], щоб розширити виділення внизу аркуша, потім клацніть правою кнопкою миші та виберіть Видалити:
Зверніть увагу, що вам потрібно скористатися правою кнопкою миші> ВИДАЛИТИ, а НЕ клавішею Delete на клавіатурі. Натискання цієї клавіші Delete не скинути використаний діапазон. Насправді, саме тому часто використовуваний діапазон неправильний ... він все ще відображає деякі дані, які раніше були в аркуші, але які користувач згодом видалив за допомогою клавіатури.
Закінчивши це, натисніть знову [Ctrl] + [End] і подивіться, куди ви потрапили - сподіваюся у нижньому правому куті ваших даних.
Іноді це не вирішує проблему, і ви все одно опиняєтесь значно нижче своїх даних. У цьому випадку зазвичай достатньо трохи VBA. Я б радив помістити наведений нижче код у вашу особисту книгу з макрокоманд на такі випадки:
Занадто багато SUMIF
Друга проблема полягає в тому, що кожен файл містив щось на зразок 60 000 формул SUMIF. І кожна з цих формул посилалася на два цілі стовпці, а не лише на 2500 рядків, які насправді містили дані. Насправді легко зрозуміти, наскільки великою у вас може бути проблема, просто виконавши функцію «Знайти все» для назви певної функції, яку ви шукаєте:
Ви можете передати в Excel 60000 операторів VLOOKUPS або IF або інші загальнодоступні функції, і він навіть не блиматиме. Але 60 000 таких ресурсоємних функцій, як "SUMIF", "SUMPRODUCT", "COUNTIF" і т.д.
Це тому, що ці функції схожі на функції Ferrari ... дуже потужні, але дуже дорогі. Один SUMIF збирається дуже швидко їхати шосе. Кілька сотень СУМІФ на тій самій ділянці все ще збираються досить швидко. Десятки тисяч з них просто збираються врізатися один в одного:
(Малюнок вище виходить із статті New York Times, де детально описується вражаючий рух транспорту в Японії в 2011 році, який залишив шосе, вкрите розбитими уламками восьми Ferrari, Lamborghini і трьох спортивних автомобілів Mercedes. Ніхто серйозно не постраждав, крім важко поранених гордість та помітне збільшення страхових внесків наступного року.)
Часто ви можете використовувати зведену таблицю, щоб виконувати те саме, що і ціла купа функцій, таких як SUMIF, COUNTIF, SUMPRODUCT тощо. Зведені таблиці - це засоби природного агрегування та фільтрації. У цьому випадку я могла використовуйте лише одну зведену таблицю, щоб замінити ці 60000 SUMIF, і час перерахунку зменшився з хвилин до мілісекунд. Зараз звітування про цей бізнес-процес не вимагає зусиль.
Одна електронна таблиця, дві моралі
У мене є дві моралі щодо цього.
Перший - не зводити очі з-за ознак неприємностей у електронних таблицях. Подумайте про FileSize і Recalculation Time як про лічильник обертів вашого автомобіля ... якщо він все далі і далі стає червоним, то зупиніться і перевірте під капотом.
Другий - і я не можу цього недостатньо підкреслити - це важливість для організацій навчання всіх користувачів про те, як розпізнавати симптоми неефективності. Не всі вони повинні знати, як це лікувати (хоча це було б добре), а просто як діагностувати це. Оскільки, якщо вона не виявляється, неефективність, якої можна уникнути, породжує значні, постійні та цілком реальні альтернативні витрати. Реальна доларова сума.
Підвищення обізнаності про ознаки небезпеки - це, можливо, найбільший приріст ефективності та можливість зниження ризику, яку може запропонувати будь-яка навчальна ініціатива, за найменших витрат. Це чейнджер.
Дві моралі, кілька засобів правового захисту.
У блозі "Щоденна доза Excel" нещодавно я опублікував фіктивний бізнес-кейс, присвячений корпоративним інвестиціям у навчальну програму Excel. Там є набагато більше їжі для роздумів, і навіть більше в коментарях, тож загляньте, і, будь ласка, залиште там коментар зі своїми думками.