Фокусы в Excel

1. Автоматическая перенумерация в столбце при добавлении строк:
       =ИНДЕКС($1:$65539;СТРОКА()-1;СТОЛБЕЦ())+1
   ИНДЕКС возвращает значение или ссылку на ячейку, СТРОКА() и СТОЛБЕЦ() возвращают номер текущего, СТРОКА()-1, соответственно номер строки выше. Ну и +1 добавляет к значению 1.
   
2. Динамический именованный диапазон.
       =СМЕЩ($B$1;;;СЧЁТЗ($B$1:$B$65539);1)
   Функция возвращает ссылку или значение, если ее использовать в диспетчере имен, то она вернет диапазон. Первый параметр определяет начало, второй и третий смещение по строкам/столбцам, четвертый и пятый ширину/высоту. Данная формула дает ссылку диапазон шириной один столбец, с высотой равной количеству не пустых ячеек во всем столбце.
   
   Такие диапазоны удобно использовать в больших таблицах например с суммесли (см.далее)
3. Сумма по условию
   Порой необходимо посчитать сумму значений только тех ячеек, где выполняется условие, например есть таблица:
   

   		Получил	Отдал
   25.10.2015	Петров	10 000,00р.	8 952,00р.
   26.10.2015	Сидоров	5 434,00р.	778,00р.
   27.10.2015	Овечкин	14 351,00р.	4 545,00р.
   28.10.2015	Сидоренков	54 564,00р.	8 734,00р.
   29.10.2015	Петров	454,00р.	421,00р.
   30.10.2015	Сидоров	54 564,00р.	4 588,00р.
   31.10.2015	Петров	4 786,00р.	453,00р.
   01.11.2015	Сидоренков	87 961,00р.	4 121,00р.
   02.11.2015	Овечкин	84 668,00р.	48 648,00р.
   03.11.2015	Сидоров	42 348,00р.	4 578,00р.
   04.11.2015	Овечкин	456,45р.	241,00р.
   05.11.2015	Сидоренков	486 498,00р.	454 578,00р.

   Хотелось бы узнать кто сколько получил, отдал и должен.
   Я создал именованный динамический диапазон по столбцам (имя,отдал,получил)
       =СМЕЩ(Лист1!$B$1;1;;СЧЁТЗ(Лист1!$B$1:$B$65570);1)
   Обратите внимание на смещение на одну строку, первая строка - шапка таблицы.
   

   	Задолженность
   	Петров	Сидоров	Овечкин	Сидоренков
   Итог получено	15 240,00р.	102 346,00р.	99 475,45р.	629 023,00р.
   Итог отдано	9 826,00р.	9 944,00р.	53 434,00р.	467 433,00р.
   Итог должен	5 414,00р.	92 402,00р.	46 041,45р.	161 590,00р.

   Для подсчета я использовал формулу:
       =СУММЕСЛИ(имя;H10;получил)
   Где "имя" - дипазон поиска соответствия,H10 - ссылка на ячейку с критерием (Петров),получил - диапазон суммирования.  Задолженность можно вычислить разностью отдано и получено, но можно вообще опустить эти строки и написать:
                                          =СУММЕСЛИ(имя;H10;получил)-СУММЕСЛИ(имя;H10;Отдал)
   В итоге у нас красивая таблица.
   
   
   Использование именованных диапазонов упрощает чтение формул, для примера:
       =СУММЕСЛИ(имя;H10;получил)
   выглядит так без динамического диапазона:
       =СУММЕСЛИ(B2:B13;H10;C2:C13)
   И так с динамическим диапазоном:     =СУММЕСЛИ(СМЕЩ(Лист1!$B$1;1;;СЧЁТЗ(Лист1!$B$1:$B$65570);1);H10;СМЕЩ(Лист1!$C$1;1;;СЧЁТЗ(Лист1!$C$1:$C$65570);1))
   Чтобы понять последнее, придется сильно постараться, а представьте себе если Вы решили отказаться от отдано и получено, формула увеличивается в два раза!!!!