Note: The other languages of the website are Google-translated. Back to English

Як рахувати рядки, поки в Excel не буде досягнуто певного значення суми?

Наприклад, існує ряд даних, стовпець A включає номер товару, а стовпець B - кількість замовлення, і у вас є певне значення, таке як 80, що є загальною сумою перших n-их чисел у порядку, як ви можете порахувати, скільки рядки задіяні для підсумовування до 80, як показано на знімку екрана нижче?
розраховувати документи, поки сума не досягне 1

Підраховуйте рядки до досягнення певного підсумованого значення за формулою


стрілка синя права міхур Підраховуйте рядки до досягнення певного підсумованого значення за формулою

Щоб підрахувати кількість рядків, які використовуються для підсумовування до певного відповідного значення, ви можете застосувати формулу.

Виберіть порожню клітинку, наприклад E2, і введіть цю формулу =MATCH(D2,SUBTOTAL(9,OFFSET(B2,,,ROW(B2:B17)-ROW(B2))),1), натисніть Shift + Ctrl + Enter клавіша, щоб дістатися до правильного результату. Дивіться знімок екрана:
розраховувати документи, поки сума не досягне 2

Чайові: У цій формулі D2 - це певне значення, яке ви хочете досягти підсумовуванням, B2: B17 - діапазон значень, з якого ви підсумовуєте, ви можете змінювати їх, як вам потрібно.

Якщо ви хочете виключити рядок заголовка, ви можете скористатися цією формулою =MATCH(D2,SUBTOTAL(9,INDIRECT("$B$1"&":"&ADDRESS(ROW($B$1:$B$17),COLUMN($B$1)))), і натисніть кнопку  Shift + Ctrl + Enter клавіші одночасно.


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (8)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Прочитавши це рішення, я почав читати про формули масивів. Дуже цікаво та корисно. Що стосується цього рішення, воно вирішує дуже складний аспект відстеження розміру запасів матеріалу та складу. Чи можете ви допомогти мені з однією невеликою зміною... Я хочу, щоб у вашому прикладі позиція в списку загальної суми йшла знизу вгору та зверху вниз. Я спробував кілька речей, але ще не зламав, в основному через неповне розуміння синтаксису формули. Будь-які пропозиції? Особливо мене цікавить версія заголовка виключення або версія, яка не включає жодних міркувань заголовка, оскільки я хочу обчислити це нове для кожного рядка (тобто день.)
Цей коментар був мінімізований модератором на сайті
Не зважай. Я знайшов простий спосіб без масивів зробити те, що я хотів.
Цей коментар був мінімізований модератором на сайті
привіт, яка може бути формула, крім підрахунку стовпців?
Цей коментар був мінімізований модератором на сайті
Привіт, Діано, просто змініть РЯДОК на СТОБЕЦЬ у формулі, як-от =СПІВНИК(A2,ПІДСУМОГО(9,ЗМІЩЕННЯ(A1,,,COLUMN(A1:E1)-COLUMN(B2))),1). Дивіться знімок екрана:
Цей коментар був мінімізований модератором на сайті
Це не працює - я думаю, що це тому, що проміжний підсумок працює лише по вертикалі?
Цей коментар був мінімізований модератором на сайті
MATCH(D2,SUBTOTAL(9,OFFSET(B2,,,ROW(B2:B17)-ROW(B2))),1)ця формула не працює в таблицях Google, будь-яка інформація про це
Цей коментар був мінімізований модератором на сайті
Привіт, Суреш, формула вище працює лише в Microsoft Excel, якщо ви хочете рахувати рядки до досягнення цільової суми на аркуші Google, вам потрібен допоміжний стовпець і функція COUNTIF. Конкретні операції, як показано на скріншоті нижче. Будь ласка, дайте мені знати, якщо це допоможе вам.
Цей коментар був мінімізований модератором на сайті
Привіт, як буде формула, якщо замість D2 я хочу, щоб це був встановлений діапазон даних?
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця