Велика проблема в маленькій електронній таблиці

Джефф Вір

Привіт люди. Джефф тут. Нещодавно я провів презентацію щодо ефективності 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. Там є набагато більше їжі для роздумів, і навіть більше в коментарях, тож загляньте, і, будь ласка, залиште там коментар зі своїми думками.

Хоча цей бізнес-кейс обертається на внутрішній корпоративній навчальній програмі, ще один чудовий спосіб зменшити ці альтернативні витрати - це курси, такі як власна школа Excel від Chandoo.org, класи VBA та інші курси Chandoo.

Не кажучи вже про інші фантастичні курси, які ви знайдете рекламованими в Інтернеті, якщо подивитись.

І ще одне - це взаємодія в таких місцях, як форум Ченду, де ви знайдете армію ніндзя з більшим колективним досвідом, ніж Борг із Зоряних Походів. Розум вулика, який є форумом, не має собі рівних.

І звичайно, ви знайдете велику кількість інформації саме в цьому блозі, у таких статтях, як я вже говорив, ваша електронна таблиця - це СУЩЕ, а не справжній PHAT!

Джефф Вейр - місцевий житель Північної Галактики там, у Вітряному Веллінгтоні, Нова Зеландія - більш мінливий, ніж НЕПРЯМИЙ, і більш випадковий, ніж РАНД. Насправді його душевний стан можна в значній мірі підсумувати таким чином:

Правильно, чистий #VALUE!

Дізнайтеся більше на http: www.heavydutydecisions.co.nz

Поділіться цією порадою з колегами

Отримайте БЕЗКОШТОВНІ підказки Excel + Power BI

Прості, веселі та корисні електронні листи раз на тиждень.

Вчіться і будьте чудовими.

45 коментарів
Задайте питання або скажіть щось. Категорія: Інструкції Excel, хакі, Дізнайтеся Excel, Повідомлення Джеффа

Ласкаво просимо на Chandoo.org

Щиро дякую за відвідування. Моя мета - зробити ви чудові в Excel & Power BI. Я роблю це, обмінюючись відео, порадами, прикладами та завантаженнями на цьому веб-сайті. Тут є понад 1000 сторінок із усіма речами Excel, Power BI, інформаційними панелями та VBA. Вперед і витратьте кілька хвилин, щоб бути ДИВНИМ. Прочитайте мою історію • БЕЗКОШТОВНА книга порад Excel

Від простого до складного - існує формула для кожного випадку. Перевірте список зараз.

Календарі, рахунки-фактури, трекери та багато іншого. Все безкоштовно, весело та фантастично.

Power Query, модель даних, DAX, фільтри, слайсери, умовні формати та красиві діаграми. Це все тут.

Все ще на заборі щодо Power BI? У цьому посібнику з початку роботи дізнайтеся, що таке Power BI, як його отримати та як створити перший звіт з нуля.

Пошук зображень - Як показати динамічне зображення в комірці [Хитрість Excel]

Чи хочете ви коли-небудь шукати зображення або зображення в Excel? Щось на зразок зображення вище.

У цій статті дізнайтеся, як налаштувати пошук зображень за допомогою Excel. Ви можете використовувати це для відображення деталей про персонал, зображень виробів, деталей машин тощо.

9 Сітка рамки для відображення талантів - HR для Excel - Шаблон та пояснення

6 Потрібно знати варіації лінійних діаграм для аналізу даних

Дворівнева перевірка даних [фокус Excel]

Формула Excel для перетворення формату календаря в таблицю

Excel для початківців
Розширені навички Excel
Інформаційні панелі Excel
Повний посібник зі зведених таблиць
10 найкращих формул Excel
Ярлики Excel
# Чудовий бюджет проти фактичної таблиці
40+ прикладів VBA

Пов’язані поради

Пошук зображень - Як показати динамічне зображення в комірці [Хитрість Excel]

9 Сітка рамки для відображення талантів - HR для Excel - Шаблон та пояснення

Дворівнева перевірка даних [фокус Excel]

Формула Excel для перетворення формату календаря в таблицю

План проекту - діаграма Ганта з деталізацією [шаблони]

Ці фокуси зведеної таблиці масово економить ваш час

45 відповідей на "Великі проблеми в маленькій електронній таблиці"

Нещодавно я стикався з цією самою проблемою зі своїми власними делегатами. ДВОЙЧИЙ з двома окремими делегатами в різних місцях.

Погляньте на моє власне повідомлення в блозі на одному з них, де я описую своє рішення зведеної таблиці: http://excelmaster.co під заголовком "Вам потрібні опорні точки"

Погляньте на мою власну публікацію в блозі на одному з них, де я описую своє рішення зведеної таблиці: excelmaster dot co під заголовком "вам потрібні опорні точки"

Дійсно цікаво, я завжди думав, що це пов'язано з моїми дуже складними аркушами і повільним комп'ютером (AMD + 4 Гб оперативної пам'яті). Але трохи подумавши, я вилучив цілу купу форматування у своїх таблицях, і тепер все працює трохи швидше!

Я залишаю свої електронні таблиці в ручному режимі calc, але це тому, що я постійно працюю з електронними таблицями, які містять від 20 000 до 100 000 рядків, іноді до 600 000, загалом близько 20-30 стовпців (до додавання будь-яких обчислених полів). Можливо, я виняток через обсяг даних, з якими я працюю, але я викину це там і подивлюсь, чи не втрачаю можливості для ефективності.

У мене є робоча книга, яка аналізує рахунки для визначення пріоритету дзвінка (для збору простроченого AR), врахування їх ATB (застарілий пробний баланс, скільки вони заборгували, скільки є поточного, 1-30 днів простроченого, 31-60 днів минулого до сплати тощо), ADP (середня кількість днів до сплати), відсоток використаного кредитного ліміту, чи є у них NSF (повідомлення про недостатню кількість коштів, в основному їх чек відхилений), якщо на їх рахунку є незастосована готівка (наприклад, невикористаний кредит які можуть збалансувати належну суму, якщо ми зателефонуємо і отримаємо їх дозвіл використовувати її для цієї мети), умови оплати (скільки часу вони мають від рахунку-фактури до сплати) та фактори у всіх цих бітах даних, щоб отримати загальний зважений бал, щоб побачити, які рахунки повинні бути у верхній частині списку викликів, який потім розподіляється між колекторами.

Там взагалі навколо

20 000 рахунків у списку. Дані надходять із 5 різних звітів SAP. Спочатку я мав різну книгу для кожного звіту і посилався на них із таблиці ранжирування/розрахунку, але трохи пришвидшив, помістивши всі дані на вкладках (у порядку обчислення) в одну книгу зліва направо, ведучи до аркуш розрахунку в кінці.

Дотепер я знайшов речі, які допомагають скоротити час обчислення:

Очистіть та відсортуйте вихідні дані, перш ніж вносити їх до книги обчислень. Це може здатися очевидним або блюзнірським на основі вашого походження. Раніше я працював у цифровій ілюстрації, і було дуже важливо завжди мати оригінальні, недоторкані необроблені дані, зафіксовані ззаду, з усіма коригуваннями, нанесеними над ними, щоб ви ніколи не втрачали жодного джерела, тому моя початкова практика полягала в тому, щоб завжди будувати мої аркуші, щоб використовувати вихідні дані саме так, як вони надходять із SAP. Однак час обчислення, який заощаджується, коли ви виконуєте лише невелику роботу, щоб переконатися, що дані розташовані впорядковано, є значним. Щоразу, коли ви торкаєтесь даних, є ще одна можливість помилки, тому це щось, що потрібно збалансувати.

Коли вам потрібно видалити певні рядки з таблиці на основі критеріїв, спочатку сортуйте таблицю за цим критерієм, і це різко скорочує час, необхідний для видалення всіх рядків із відповідними критеріями.

Якщо у вас є кілька стовпців, які посилаються на одну і ту ж вихідну таблицю, не використовуйте index & match у кожному з цих стовпців. Використовуйте збіг в одному стовпці, щоб знайти, який рядок вихідної таблиці слід подивитися, а потім просто використовуйте індекс для інших стовпців та посилання на номер рядка, повернутий із стовпця збігу. Наприклад, остання частина моєї робочої книги - це аркуш, який складає робочий список для окремого колекціонера. Ви вводите номер колектора вгорі, і він порівнює його із загальною кількістю колекторів і складає список. Якщо у вас 7 колекціонерів, тоді це дивиться на рейтинг і перераховує кожен 7-й рахунок зверху вниз. Один стовпець генерує номери рядків цих облікових записів за допомогою збігу, а решта використовує індекс для витягування даних.

Я збираюся знову змінити світи, цього разу на бухгалтерський облік, тому я трохи прибираю будинки, щоб переконатися, що всі мої СОП в порядку, а електронні таблиці, якими я користуюся, максимально чисті та ефективні. Якщо хтось бачить ваду в тому, що я описав, або можливість пришвидшити це (бажано без використання VBA, я залишаю роботу людям, яким знадобиться багато тренінгів, аби лише зрозуміти індекс та відповідність, тому VBA є для них це буде лише глибшою таємницею) Я був би радий це почути.

Я б також розглянув можливість використання Power Pivot, оскільки я вважаю, що він може поєднувати дані з різних джерел