Create Spilled Data From Filtered Table in Excel
Автор: Josh_Excel
Загружено: 2025-02-15
Просмотров: 275
Описание:
This is one method to show only visible filtered data in a spilled array to allow creating formulas for visible data only based on Slicer selections.
Steps:
1. Put data in Table format using Ctrl+t or Ctrl+l
2. Copy Table Header to Spilled Header
3. Insert formula link to table column
=Table1[Employee ID]
4. Modify Formula with INDIRECT Function to allow incorporating the header name into the formula
=INDIRECT("Table1[" & "Employee ID" & "]" ↩
↪ =INDIRECT("Table1[" & F13 & "]"
5. Create Formula with BYROW and SUBTOTAL COUNTA to be used in FILTER function as the inclusion argument
=BYROW(Table1,LAMBDA(x,SUBTOTAL(3,x)))
6. Use BYROW formula with Filter Function
=FILTER(INDIRECT("Table1[" & F13 & "]"),BYROW(Table1,LAMBDA(x,SUBTOTAL(3,x))))
7. Add Slicer
8. Add Additional Formulas as Needed
↠ COUNTIF
↠ SUMIFS
↠ XLOOKUP
↠ etc.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please checkout my book, Power Query M Language Basics Kindle Edition
https://www.amazon.com/dp/B0DM73H3RV
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Повторяем попытку...
Доступные форматы для скачивания:
Скачать видео
-
Информация по загрузке:
