Цей епізод показує три способи зробити загальні підсумки.
Загальна сума - це для переліку числових значень сума значень від першого рядка до рядка поточної загальної суми. Загальне використання поточної суми - це реєстр чекової книжки або бухгалтерський звіт. Існує безліч способів створити загальну роботу, два з яких описані нижче.
Найпростіший прийом полягає у додаванні в кожному рядку загальної суми з верхнього рядка до значення в рядку. Отже, перша формула в рядку 2:
=SUM(D1,C2)
Причиною того, що ми використовуємо функцію SUM, є те, що в першому рядку ми розглядаємо заголовок у рядку вище. Якщо ми використовуємо простішу, інтуїтивнішу формулу, =D1+C2тоді буде генеровано помилку, оскільки значення заголовка - текст у порівнянні з числовим. Магія полягає в тому, що функція SUM ігнорує текстові значення, які додаються як нульові значення. Коли формула копіюється до всіх рядків, у яких бажаний загальний підсумок, посилання на клітинки коригуються відповідно:
В іншому методі також використовується функція SUM, але кожна формула підсумовує всі значення від першого рядка до рядка, що відображає поточний підсумок. У цьому випадку ми використовуємо знак долара ($), щоб зробити першу клітинку посилання абсолютним посиланням, що означає, що він не коригується при копіюванні:
На обидва прийоми не впливає сортування та видалення рядків, але, вставляючи рядки, формулу потрібно скопіювати в нові рядки.
Excel 2007 представив таблицю, яка є повторною реалізацією списку в Excel 2003. Таблиці представили ряд дуже корисних функцій для таблиць даних, таких як форматування, сортування та фільтрування. З введенням таблиць ми також отримали новий спосіб посилання на частини таблиці. Цей новий стиль посилання називається структурованим посиланням.
Щоб перетворити наведений вище приклад у Таблицю, ми вибираємо дані, які ми хочемо включити в Таблицю, і натискаємо Ctrl + T. Після відображення запиту про підтвердження діапазону Таблиці та чи існують заголовки, Excel перетворює дані у відформатовану таблицю:
Зауважте, що формули, які ми ввели раніше, залишаються незмінними.
Однією з корисних функцій, яку пропонує Таблиця, є автоматичне форматування та ведення формул, коли рядки додаються, видаляються, сортуються та фільтруються. Особливо на підтримці формули ми зосередимось і може бути проблематичною. Щоб таблиці працювали, поки ними маніпулюють, Excel використовує обчислені стовпці, які є стовпцями з формулами, такими як стовпець D у наведеному вище прикладі. Коли нові вкладені рядки додаються внизу, Excel автоматично заповнює нові рядки формулою “за замовчуванням” для цього стовпця. Проблема з наведеним прикладом полягає в тому, що Excel плутається зі стандартними формулами і не завжди обробляє їх правильно. Це стає очевидним, коли нові рядки додаються внизу Таблиці (вибравши нижню праву комірку в Таблиці та натиснувши TAB):
Цей недолік усувається за допомогою новіших структурованих посилань. Структуроване посилання усуває необхідність посилатися на певні комірки, використовуючи стиль посилання A1 або R1C1, і замість цього використовує імена стовпців та інші ключові слова для ідентифікації та посилання на частини таблиці. Наприклад, для створення тієї самої формули загального запуску, що використовувалася вище, але з використанням структурованого посилання, ми маємо:
=SUM(INDEX((Sales),1):(@Sales))
У цьому прикладі ми маємо посилання на назву стовпця „Продажі”, а також знак at (@) для посилання на рядок у стовпці, в якому знаходиться формула, який також відомий як поточний рядок.
Щоб реалізувати перший приклад вище, де ми додали загальне значення в попередньому рядку до суми продажів у поточному рядку, ви можете використовувати функцію OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Якщо суми, що використовуються для обчислення поточної суми, знаходяться у двох стовпцях, наприклад, одна для "Дебети" та одна для "Кредитів", то формула така:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Тут ми використовуємо функцію INDEX для пошуку клітинок і дебету клітинок першого рядка, а також підсумовуємо весь стовпець до значень поточного рядка включно. Загальна сума - це сума всіх кредитів до поточного рядка включно, за вирахуванням суми всіх дебетів до поточного рядка, включаючи.
Для отримання додаткової інформації щодо структурованих посилань зокрема та таблиць загалом, ми рекомендуємо книгу Excel Tables: Повне керівництво по створенню, використанню та автоматизації списків та таблиць Зака Барресса та Кевіна Джонса.
Коли я попросив читачів проголосувати за їхні улюблені поради, таблиці були популярними. Дякую Пітеру Альберту, Снорре Айкеленд, Ненсі Федеріс, Коліну Майклу, Джеймсу Е. Моеду, Кейуру Пателю та Полу Петону за те, що запропонували цю функцію. Пітер Альберт написав бонусну підказку Readable References. Зак Баррес написав бонусну підказку "Бігаючі підсумки". Четверо читачів запропонували використовувати OFFSET для створення діапазонів, що розширюються для динамічних діаграм: Чарлі Баак, Дон Ноулз, Френсіс Логан та Сеселія Ріб. Зараз таблиці в більшості випадків роблять те саме.
Переглянути відео
- Цей епізод показує три способи зробити загальні підсумки
- Перший метод має іншу формулу в рядку 2, ніж усі інші рядки
- Перший метод = = Ліворуч у рядку 2 та = Ліворуч + Вгору у рядках від 3 до N
- Якщо ви спробуєте використати ту саму формулу, ви отримаєте помилку #Value з = Total + Number
- Спосіб 2 використовує
=SUM(Up,Left)або=SUM(Previous Total,This Row Amount) - SUM ігнорує текст, щоб ви не отримали значення VALUE
- Метод 3 використовує розширення діапазону:
=SUM(B$2:B2) - Діапазони, що розширюються, круті, але повільні
- Прочитайте довідку Чарльза Вільямса про швидкість формули Excel
- Третій метод - це проблема, коли ви використовуєте Ctrl + T і додаєте нові рядки
- Excel не може зрозуміти, як написати формулу
- Обхідні шляхи вимагають певних знань щодо структурованих посилань у таблицях
- Обхідний шлях 1 - повільний
=SUM(INDEX((Qty),1):(@Qty)) - Рішення 2 - це нестабільність
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) відноситься до кількості в цьому рядку
- (Qty) відноситься до всіх значень Qty
Стенограма відео
Дізнайтеся Excel для подкасту, епізод 2004 - Виконання підсумків
Я підкастую всю цю книгу. Клацніть, що я у верхньому правому куті, щоб підписатися.
Привіт, ласкаво просимо назад до містичної мережі клітин. Я Білл Джелен. Тепер цю тему в книзі мені зробив мій друг Зак Періз. Якщо говорити про таблиці Excel, Зак є світовим експертом з таблиць Excel. Він написав книгу про таблиці Excel, але спершу давайте поговоримо про загальні підсумки не в таблицях.
Отже, коли я думаю про загальні підсумки, існує три різні способи зробити загальні підсумки, і спосіб, з якого я завжди починав, - це перший рядок, про який ви просто говорите, перевести значення. Тож дорівнюй тому, що зліва від мене. Добре, отже, цей формат тут просто = B2. Це все текст формули тут, у правому куті, отже, ви бачите, що ми використовуємо, а далі, далі, це проста маленька формула, яка дорівнює попередньому значенню плюс поточне значення праворуч і скопіюйте це вниз , але ви знаєте зараз, у нас є така проблема, що вона вимагала двох різних формул, і ви знаєте, що в ідеальній ситуації у вас точно така ж формула аж донизу, і причиною того, що ми маємо іншу формулу там, у першому рядку, є що при спробі додати рівне 7 плюс слово total це помилка значення,але крутий працівник тут - це не просто використовувати лівий плюс вгору, а використовувати = (SUM) попереднього значення плюс кількість у цьому рядку, і бачити, що деяких досить далеко, щоб ігнорувати тексти. Правильно, що дозволяє таку ж формулу. аж донизу.
Добре, так було, коли я починав використовувати Excel, я використовував це, а потім виявив розширюється діапазон, розширюється діапазон говорить, що ми будемо робити L $ 2: L2, і що відбувається, це завжди починається з рядка 2, але потім він опускається до поточного рядка. Отже, коли ви дивитесь, як це працює, коли воно копіюється, ми завжди починали рядок 2, але опускаємось до поточного рядка, і це стало моїм улюбленим методом. Я був схожий на, о, це набагато складніше, і коли ми переходимо до параметрів Excel, переходимо на вкладку Формули та вибираємо R1C1 у Стилі посилання. Добре бачимо, R1C1, усі ці формули абсолютно однакові аж донизу. Я не знаю, чи розумієте ви R1C1, просто добре знати, що у нас однакові формули R1C1 аж донизу.
Повернемось назад. Отже, цей метод - ось той метод, який мені сподобався, до тих пір, поки Чарльз Вільямс, Excel MBP з Англії, який має дивовижний документ про швидкість формул, швидкість формули Excel, повністю не розвінчав цей метод. Цей метод, припустимо, у вас 10 000 рядків, кожна окрема формула розглядає два посилання. Отже, ви дивитесь на 20 000 посилань, але цей, це дивиться на два, це дивиться на три, це дивиться на чотири, це дивиться на п’ять, а останній переглядає 10 000 посилань, і це жахливо повільніше і тому я перестав користуватися цим методом.
Потім я продовжую читати Зака в книзі Кевіна Джонса про таблиці Excel і виявляю ще одну проблему з цим методом. Тож однією з корисних функцій, яку пропонують таблиці, є "автоматичне форматування та рядки обслуговування формул додаються, видаляються, сортуються та фільтруються". Добре, це цитата з його книги. А щоб додати рядок до таблиці, просто перейдіть до останньої комірки таблиці та натисніть вкладку. Тож тут все працює. Ми опустилися до 70, це чудово, а потім A104, і я тут поставлю 100. Гаразд, так що 70 має змінитися на 170, і це відбувається, але ці 70 не повинні були змінюватися взагалі. Гаразд, 68 + 2 - це не 170. Я зроблю це ще раз. А 104 і поставити ще сотню в останню - правильно. Ці двоє не праві. Гаразд, у нас така дивна ситуація, що якщо виповторно використовуючи цю формулу, і при перетворенні в таблицю ви починаєте додавати рядки, загальна сума не буде працювати. Наскільки це погано?
Добре, тому Зак пропонує два напрямки роботи, і обидва вони потребують трохи знань про те, як працюють посилання на структуру. У нас просто буде новий стовпець тут, і якщо я хотів би зробити кількість, рівну кількість, так, так що = (@ Qty) говорить кількість у цьому рядку. О круто, ну є ще один тип посилання, де ми використовуємо Qty без @. Перевір це. Отже = SUM (INDEX ((Qty), 1: (@ Qty)) означає всі величини, і ми збираємось сказати, що хочемо СУММАТИ з першої кількості, тому (INDEX ((Qty), 1 говорить перше значення тут, до поточної кількості рядків, і тут використовується дійсно спеціальна версія індексу, коли за індексом слідує двокрапка, воно фактично змінюється на посилання на клітинку. миВам доведеться переглянути кожну окрему посилання, і тому, коли ви отримаєте 10000 рядків, це буде йти дуже, дуже повільно.
У Зака є ще одне обхідне рішення, яке не порушує проблему Чарльза Вільямса, але він використовує страшний OFFSET. OFFSET - це нестабільна функція, тому кожного разу, коли ви щось обчислюєте, OFFSET збирається перерахувати, а все, що знаходиться в рядку OFFSET, перерахує. Це просто чудовий спосіб повністю, повністю зіпсувати свої формули, і що це робить, це означає, що ми беремо загальну суму з цього рядка, піднімаючись на один рядок, над нульовими стовпцями, і ось що це робить: візьміть загальну суму з попереднього рядка, а потім ми додамо до неї кількість із цього рядка. Добре, отже, зараз це все дивиться на два посилання кожного разу, але, на жаль, OFFSET вводить мінливі функції.
Ну, ось у вас це більше, ніж ви коли-небудь хотіли знати про Running Totals. Думаю, моя остаточна думка полягає у використанні цього методу, оскільки він виглядає лише двома. Однакова формула аж донизу, і ваші посилання на структуровані таблиці працюватимуть.
Для цього дослідження та 39 інших справді хороших порад перегляньте цю книгу XL, 40 найкращих підказок Excel усіх часів.
Recap for this episode we talked about three ways to do running totals. The first method has a different formula, row 2, than all the other rows. It's equal left in row 2 and then equal left plus up in rows 3 through N, but if you try and just use that same formula, equal left plus up, all the way down, how you're going to get a #Value Error. So =SUM(Up,Left), which is previous total, plus this roadmap, that works great, no Value Errors and then the expanding range which I use to love. They're cool, but until I read Charles Williams white paper on Excel form of speed. Then I started to hate these expanding references. It also has a problem when you use CTRL T and add new rows. Excel can't figure out how to expand that formula, how to add new rows. I love this tip go to the very last cell in the table and press Tab, that will add a new row and then we talked about some structured referencing, where we're using quantity in this row and then all quantities. =SUM(OFFSET((@Total),-1,00,(@Qty)).
Гаразд, я хочу подякувати Заку за його підтримку. Я хочу подякувати вам, що завітали. Ми побачимось наступного разу для чергової трансляції від.
Завантажити файл
Завантажте зразок файлу тут: Podcast2004.xlsx
