Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Разность последних заполненных ячеек - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Разность последних заполненных ячеек (Формулы/Formulas)
Разность последних заполненных ячеек
Kashimirush Дата: Пятница, 24.01.2020, 14:07 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 0% ±

Excel 2010
Всем привет, туплю. Не могу понять откуда подойти.
Задача такая календарь куда заполняю данные по счетчику, в конце сумма по разниые последних 2х заполненных месяцев, как записать формулу, чтобы она автоматом определяла последние заполненные ячейки.
Пример

Работа, работа, перейди на Федота...
 
Ответить
СообщениеВсем привет, туплю. Не могу понять откуда подойти.
Задача такая календарь куда заполняю данные по счетчику, в конце сумма по разниые последних 2х заполненных месяцев, как записать формулу, чтобы она автоматом определяла последние заполненные ячейки.
Пример
Автор - Kashimirush
Дата добавления - 24.01.2020 в 14:07
Gustav Дата: Пятница, 24.01.2020, 14:36 | Сообщение № 2
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Если показания по месяцам монотонно возрастают (по заполненным ячейкам, пустые игнорируем), то самый простой способ, наверное, такой:
Код
=LARGE(A2:K2;1)-LARGE(A2:K2;2)

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеЕсли показания по месяцам монотонно возрастают (по заполненным ячейкам, пустые игнорируем), то самый простой способ, наверное, такой:
Код
=LARGE(A2:K2;1)-LARGE(A2:K2;2)
Автор - Gustav
Дата добавления - 24.01.2020 в 14:36
Kashimirush Дата: Пятница, 24.01.2020, 15:15 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 0% ±

Excel 2010
Gustav, Работает при оговоренном условии.
попробовал сделать более универсальную формулу, чтобы были и отрицательные значения
Получился вот такой уродец:
Код
=(index(C5:H5;1;Large(FILTER(ArrayFormula(COLUMN(C5:H5)-2);ArrayFormula(len(C5:H5)-2)>0);1))-index(C5:H5;1;Large(FILTER(ArrayFormula(COLUMN(C5:H5)-2);ArrayFormula(len(C5:H5)-2)>0);2)))

Цитата
ArrayFormula(COLUMN(C5:H5)-2

Минус 2 - это если данные допустим идут со столбца C, а не с A как в примере, см. лист 2

Работа, работа, перейди на Федота...
 
Ответить
СообщениеGustav, Работает при оговоренном условии.
попробовал сделать более универсальную формулу, чтобы были и отрицательные значения
Получился вот такой уродец:
Код
=(index(C5:H5;1;Large(FILTER(ArrayFormula(COLUMN(C5:H5)-2);ArrayFormula(len(C5:H5)-2)>0);1))-index(C5:H5;1;Large(FILTER(ArrayFormula(COLUMN(C5:H5)-2);ArrayFormula(len(C5:H5)-2)>0);2)))

Цитата
ArrayFormula(COLUMN(C5:H5)-2

Минус 2 - это если данные допустим идут со столбца C, а не с A как в примере, см. лист 2
Автор - Kashimirush
Дата добавления - 24.01.2020 в 15:15
Gustav Дата: Пятница, 24.01.2020, 15:34 | Сообщение № 4
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Я тоже попробовал при любых значениях, получилась вон какая красота - просто образец для лабораторной работы по функциям:
[vba]
Код
=SUMPRODUCT(ARRAY_CONSTRAIN(INDEX(SORT(TRANSPOSE(FILTER({COLUMN(A2:K2);A2:K2};A2:K2));1;FALSE);;2);2;1)*{1;-1})
[/vba]
В процессе разворачиваю массив вертикально (TRANSPOSE) и сортирую по убыванию номера столбца (SORT), оставив только непустые суммы (FILTER):
[vba]
Код
Столбец    Сумма
5    6400
3    6300
2    6233
1    6132
[/vba]Дальше беру весь второй столбец (INDEX):
[vba]
Код
6400
6300
6233
6132
[/vba]Дальше оставляю две первые строки (ARRAY_CONSTRAIN) и приставляю к ним множители {1;-1} во втором столбце:
[vba]
Код
Сумма    Множитель
6400    1
6300    -1
[/vba]Дальше суммирую получающиеся построчные произведения (SUMPRODUCT):
[vba]
Код
6400
-6300
[/vba]

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеЯ тоже попробовал при любых значениях, получилась вон какая красота - просто образец для лабораторной работы по функциям:
[vba]
Код
=SUMPRODUCT(ARRAY_CONSTRAIN(INDEX(SORT(TRANSPOSE(FILTER({COLUMN(A2:K2);A2:K2};A2:K2));1;FALSE);;2);2;1)*{1;-1})
[/vba]
В процессе разворачиваю массив вертикально (TRANSPOSE) и сортирую по убыванию номера столбца (SORT), оставив только непустые суммы (FILTER):
[vba]
Код
Столбец    Сумма
5    6400
3    6300
2    6233
1    6132
[/vba]Дальше беру весь второй столбец (INDEX):
[vba]
Код
6400
6300
6233
6132
[/vba]Дальше оставляю две первые строки (ARRAY_CONSTRAIN) и приставляю к ним множители {1;-1} во втором столбце:
[vba]
Код
Сумма    Множитель
6400    1
6300    -1
[/vba]Дальше суммирую получающиеся построчные произведения (SUMPRODUCT):
[vba]
Код
6400
-6300
[/vba]
Автор - Gustav
Дата добавления - 24.01.2020 в 15:34
Kashimirush Дата: Пятница, 24.01.2020, 15:48 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 0% ±

Excel 2010
Gustav, Поразительно, а ведь на первый взгляд, задача кажется через чур простой.
Кстати почему
Код
{COLUMN(A2:K2);A2:K2}

Без применения фильтра или формулы массива - выдает ошибку?

Работа, работа, перейди на Федота...
 
Ответить
СообщениеGustav, Поразительно, а ведь на первый взгляд, задача кажется через чур простой.
Кстати почему
Код
{COLUMN(A2:K2);A2:K2}

Без применения фильтра или формулы массива - выдает ошибку?
Автор - Kashimirush
Дата добавления - 24.01.2020 в 15:48
Gustav Дата: Пятница, 24.01.2020, 16:12 | Сообщение № 6
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Потому что COLUMN(...) без формулы массива выдает только ОДНО значение, а ссылка A2:K2 - 11 значений. Фактически ArrayFormula нужна здесь именно для COLUMN:
[vba]
Код
={ ArrayFormula(COLUMN(A2:K2));  A2:K2 }
[/vba]

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеПотому что COLUMN(...) без формулы массива выдает только ОДНО значение, а ссылка A2:K2 - 11 значений. Фактически ArrayFormula нужна здесь именно для COLUMN:
[vba]
Код
={ ArrayFormula(COLUMN(A2:K2));  A2:K2 }
[/vba]
Автор - Gustav
Дата добавления - 24.01.2020 в 16:12
Kashimirush Дата: Среда, 05.02.2020, 15:49 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 0% ±

Excel 2010
Цитата Kashimirush, 24.01.2020 в 15:48, в сообщении № 5 ()
Потому что COLUMN(...) без формулы массива выдает только ОДНО значение

Это я понимаю, но у вас в формуле
Цитата Gustav, 24.01.2020 в 15:34, в сообщении № 4 ()
=SUMPRODUCT(ARRAY_CONSTRAIN(INDEX(SORT(TRANSPOSE(FILTER({COLUMN(A2:K2);A2:K2};A2:K2));1;FALSE);;2);2;1)*{1;-1})

ArrayFormula отсутствует в принципе, а функция FILTER стоит за фигурными скобками, по "моей логике" FILTER тоже должен давать ошибку, т.к. {COLUMN(A2:K2);A2:K2} сама по себе ошибочна без обработки формулой массива.

Работа, работа, перейди на Федота...
 
Ответить
Сообщение
Цитата Kashimirush, 24.01.2020 в 15:48, в сообщении № 5 ()
Потому что COLUMN(...) без формулы массива выдает только ОДНО значение

Это я понимаю, но у вас в формуле
Цитата Gustav, 24.01.2020 в 15:34, в сообщении № 4 ()
=SUMPRODUCT(ARRAY_CONSTRAIN(INDEX(SORT(TRANSPOSE(FILTER({COLUMN(A2:K2);A2:K2};A2:K2));1;FALSE);;2);2;1)*{1;-1})

ArrayFormula отсутствует в принципе, а функция FILTER стоит за фигурными скобками, по "моей логике" FILTER тоже должен давать ошибку, т.к. {COLUMN(A2:K2);A2:K2} сама по себе ошибочна без обработки формулой массива.
Автор - Kashimirush
Дата добавления - 05.02.2020 в 15:49
Gustav Дата: Среда, 05.02.2020, 16:36 | Сообщение № 8
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Цитата Kashimirush, 05.02.2020 в 15:49, в сообщении № 7 ()
ArrayFormula отсутствует в принципе, а функция FILTER стоит за ...

FILTER сама по себе "массивовая" функция. Она содержит неявное ArrayFormula() как бы внутри себя и применяет его к своим аргументам.

А вообще по жизни я поступаю проще: сначала пробую без ArrayFormula(), а потом, если "без" как надо не "поехало", то уже добавляю в явном виде. И не парюсь на эту тему ;)

МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Цитата Kashimirush, 05.02.2020 в 15:49, в сообщении № 7 ()
ArrayFormula отсутствует в принципе, а функция FILTER стоит за ...

FILTER сама по себе "массивовая" функция. Она содержит неявное ArrayFormula() как бы внутри себя и применяет его к своим аргументам.

А вообще по жизни я поступаю проще: сначала пробую без ArrayFormula(), а потом, если "без" как надо не "поехало", то уже добавляю в явном виде. И не парюсь на эту тему ;)
Автор - Gustav
Дата добавления - 05.02.2020 в 16:36
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Разность последних заполненных ячеек (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!