3 марта 2015 г.

Фокусы в 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))
    Чтобы понять последнее, придется сильно постараться, а представьте себе если Вы решили отказаться от отдано и получено, формула увеличивается в два раза!!!!

Комментариев нет:

Отправка комментария