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

Вход

Регистрация

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

 

= Мир MS Excel/Вытащить ненулевые значения из таблицы в столбец - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вытащить ненулевые значения из таблицы в столбец (Формулы/Formulas)
Вытащить ненулевые значения из таблицы в столбец
tuls Дата: Четверг, 03.08.2017, 16:05 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Товарищи, добрый день!
Барахтаюсь уже много дней, пытаясь составить регистр для расчета НДФЛ. Многие вопросы уже смог решить, но еще больше не получается.
Вот один из них. Вроде простой, но никак не срастается.
Есть простая таблица. В столбце месяцы, в строках коды дохода. Естественно не в каждом месяце. Так вот значения больше нуля нужно вытянуть в один столбец, в котором каждый месяц повторяется столько раз, сколько в нем вычетов.
Дополнительные наглые требования: не использовать макросы (слишком сложно для меня) и не использовать динамические диапазоны (так как лист будет копироваться по числу сотрудников и не хочу проблем на сводном листе).
К сообщению приложен файл: 4562113.xls (21.0 Kb)

Использую только Excel 2003. Так сложились звезды.
 
Ответить
СообщениеТоварищи, добрый день!
Барахтаюсь уже много дней, пытаясь составить регистр для расчета НДФЛ. Многие вопросы уже смог решить, но еще больше не получается.
Вот один из них. Вроде простой, но никак не срастается.
Есть простая таблица. В столбце месяцы, в строках коды дохода. Естественно не в каждом месяце. Так вот значения больше нуля нужно вытянуть в один столбец, в котором каждый месяц повторяется столько раз, сколько в нем вычетов.
Дополнительные наглые требования: не использовать макросы (слишком сложно для меня) и не использовать динамические диапазоны (так как лист будет копироваться по числу сотрудников и не хочу проблем на сводном листе).
Автор - tuls
Дата добавления - 03.08.2017 в 16:05
_Boroda_ Дата: Четверг, 03.08.2017, 16:21 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно? Для 2003
Код
=ЕСЛИ(СЧЁТ(E$24:E24)+1>СЧЁТ(1/C$8:G$19);"";ИНДЕКС(A$8:A$19;НАИМЕНЬШИЙ(ЕСЛИ(C$8:G$19;A$8:A$19+СТОЛБЕЦ(C25:G25)%);СТРОКА(E1))))

Код
=ЕСЛИ(E25="";"";ИНДЕКС(A$6:G$6;ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(C$8:G$19;A$8:A$19+СТОЛБЕЦ(C25:G25)%);СТРОКА(E1));1)/1%+0,1))

Код
=ЕСЛИ(E25="";"";ИНДЕКС(C$8:G$19;E25;ПОИСКПОЗ(F25;C$6:G$6;)))

Первые две - формулы массива, вводятся одновременным нажатием Контрл Шифт Енетр
К сообщению приложен файл: 4562113_1.xls (44.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно? Для 2003
Код
=ЕСЛИ(СЧЁТ(E$24:E24)+1>СЧЁТ(1/C$8:G$19);"";ИНДЕКС(A$8:A$19;НАИМЕНЬШИЙ(ЕСЛИ(C$8:G$19;A$8:A$19+СТОЛБЕЦ(C25:G25)%);СТРОКА(E1))))

Код
=ЕСЛИ(E25="";"";ИНДЕКС(A$6:G$6;ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(C$8:G$19;A$8:A$19+СТОЛБЕЦ(C25:G25)%);СТРОКА(E1));1)/1%+0,1))

Код
=ЕСЛИ(E25="";"";ИНДЕКС(C$8:G$19;E25;ПОИСКПОЗ(F25;C$6:G$6;)))

Первые две - формулы массива, вводятся одновременным нажатием Контрл Шифт Енетр
Автор - _Boroda_
Дата добавления - 03.08.2017 в 16:21
tuls Дата: Четверг, 03.08.2017, 16:32 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Вы так быстро ответили! Особенно по сравнению с налоговой, которая минимум через месяц отвечает! :-)
Спасибо, внешне все работает и коротко.

Использую только Excel 2003. Так сложились звезды.
 
Ответить
СообщениеВы так быстро ответили! Особенно по сравнению с налоговой, которая минимум через месяц отвечает! :-)
Спасибо, внешне все работает и коротко.
Автор - tuls
Дата добавления - 03.08.2017 в 16:32
tuls Дата: Понедельник, 07.08.2017, 11:59 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Я проторопился отчитаться, что все работает.
Во-первых формула работает почему-то очень медленно. Намного дольше, чем от нее можно ожидать, даже несмотря на "массивность". Но это даже не главное.
Во-вторых работает только в сделанном вами примере.
У меня же при любом смещении результирующего диапазона начинает выдавать ошибку #ССЫЛКА. Даже, когда я полностью копирую оба диапазона на другой лист или перетаскиваю чуть в сторону.
Проблема еще в том, что я не могу понять ее логику. Пошаговая проверка "Вычислить формулу" крушит Excel. Как я понял на "+СТОЛБЕЦ(C25:G25)%". Все попытки разобраться самому через F9, Ctrl+Z ясности не добавили.
Если не трудно, просветите доступно. Я не программист, но кроме меня никто не сделает.

Использую только Excel 2003. Так сложились звезды.
 
Ответить
СообщениеЯ проторопился отчитаться, что все работает.
Во-первых формула работает почему-то очень медленно. Намного дольше, чем от нее можно ожидать, даже несмотря на "массивность". Но это даже не главное.
Во-вторых работает только в сделанном вами примере.
У меня же при любом смещении результирующего диапазона начинает выдавать ошибку #ССЫЛКА. Даже, когда я полностью копирую оба диапазона на другой лист или перетаскиваю чуть в сторону.
Проблема еще в том, что я не могу понять ее логику. Пошаговая проверка "Вычислить формулу" крушит Excel. Как я понял на "+СТОЛБЕЦ(C25:G25)%". Все попытки разобраться самому через F9, Ctrl+Z ясности не добавили.
Если не трудно, просветите доступно. Я не программист, но кроме меня никто не сделает.
Автор - tuls
Дата добавления - 07.08.2017 в 11:59
vikttur Дата: Понедельник, 07.08.2017, 12:05 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Цитата tuls, 07.08.2017 в 11:59, в сообщении № 4 ()
дольше, чем от нее можно ожидать, даже несмотря на "массивность"

Не "не смотря", а "благодаря" :)
Формулы массива зачастую выполняют много "лишних" вычислений, отсюда и тормоза.

Код
СТОЛБЕЦ(C25:G25)%

преобразование № столбца в процентный вид (деление на 100). А деление - самое медленное из четырех математических операций (+, *, -, /)
Возможно, при применении оператора % происходит не деление, а какие-то другие преобразования...


Сообщение отредактировал vikttur - Понедельник, 07.08.2017, 12:09
 
Ответить
Сообщение
Цитата tuls, 07.08.2017 в 11:59, в сообщении № 4 ()
дольше, чем от нее можно ожидать, даже несмотря на "массивность"

Не "не смотря", а "благодаря" :)
Формулы массива зачастую выполняют много "лишних" вычислений, отсюда и тормоза.

Код
СТОЛБЕЦ(C25:G25)%

преобразование № столбца в процентный вид (деление на 100). А деление - самое медленное из четырех математических операций (+, *, -, /)
Возможно, при применении оператора % происходит не деление, а какие-то другие преобразования...
Автор - vikttur
Дата добавления - 07.08.2017 в 12:05
tuls Дата: Понедельник, 07.08.2017, 12:43 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Это я понял, хотя не понял, зачем. Вероятно, что из-за изменения номера столбца и не работает формула при перемещении. Но я не понимаю какой результат это деление дает.
С куском СЧЁТ(E$24:E24)+1>СЧЁТ(1/C$8:G$19) более менее понятно, а с процентом нет.

Использую только Excel 2003. Так сложились звезды.
 
Ответить
СообщениеЭто я понял, хотя не понял, зачем. Вероятно, что из-за изменения номера столбца и не работает формула при перемещении. Но я не понимаю какой результат это деление дает.
С куском СЧЁТ(E$24:E24)+1>СЧЁТ(1/C$8:G$19) более менее понятно, а с процентом нет.
Автор - tuls
Дата добавления - 07.08.2017 в 12:43
_Boroda_ Дата: Понедельник, 07.08.2017, 13:23 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
На самом деле в первой формуле СТОЛБЕЦ не нужно, мы ж там строки выковыриваем
Код
=ЕСЛИ(СЧЁТ(E$24:E24)+1>СЧЁТ(1/C$8:G$19);"";ИНДЕКС(A$8:A$19;НАИМЕНЬШИЙ(ЕСЛИ(C$8:G$19;A$8:A$19);СТРОКА(E1))))

А вот во второй нужен
кусок
Код
ЕСЛИ(C$8:G$19;A$8:A$19+СТОЛБЕЦ(C25:G25)%)
дает массив {1,03;ЛОЖЬ;ЛОЖЬ;1,06;ЛОЖЬ:2,03;ЛОЖЬ;..., откуда мы вынимаем n-й наименьший (например, 3-й - 2,03), берем от него остаток от деления на 1 - 0,03, делим на 1%, получаем 3 - это и есть искомый столбец, который мы потом вынимаем ИНДЕКСом из массива A$6:G$6

А если в своем файле что-то не получается, то просто приложите его кусок. Словами сложно

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНа самом деле в первой формуле СТОЛБЕЦ не нужно, мы ж там строки выковыриваем
Код
=ЕСЛИ(СЧЁТ(E$24:E24)+1>СЧЁТ(1/C$8:G$19);"";ИНДЕКС(A$8:A$19;НАИМЕНЬШИЙ(ЕСЛИ(C$8:G$19;A$8:A$19);СТРОКА(E1))))

А вот во второй нужен
кусок
Код
ЕСЛИ(C$8:G$19;A$8:A$19+СТОЛБЕЦ(C25:G25)%)
дает массив {1,03;ЛОЖЬ;ЛОЖЬ;1,06;ЛОЖЬ:2,03;ЛОЖЬ;..., откуда мы вынимаем n-й наименьший (например, 3-й - 2,03), берем от него остаток от деления на 1 - 0,03, делим на 1%, получаем 3 - это и есть искомый столбец, который мы потом вынимаем ИНДЕКСом из массива A$6:G$6

А если в своем файле что-то не получается, то просто приложите его кусок. Словами сложно
Автор - _Boroda_
Дата добавления - 07.08.2017 в 13:23
vikttur Дата: Понедельник, 07.08.2017, 13:23 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Процент - для определения номера столбца, из которого нужно брать значение.

Вариант с облегченными формулами. Номера счетов обязательно в возрастающем порядке.
Отображение нулевых значений скрыто (галка в параметрах листа).

Используется доп. столбец для определения граничного числа:
Код
=СЧЁТЕСЛИ(C8:G8;">0")+H8

Месяц:
Код
=ЕСЛИ(СТРОКА(A1)>$H$5;;ИНДЕКС($A$8:$A$19;ПОИСКПОЗ(СТРОКА(A1);$H$8:$H$19)))

Счет (формула массива):
Код
=ЕСЛИ(B25;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС($C$8:$G$19;B25;)>0;$C$6:$G$6);СЧЁТЕСЛИ($B$25:B25;B25));)

Сумма:
Код
=ЕСЛИ(B25;ИНДЕКС($C$8:$G$19;B25;ПОИСКПОЗ(C25;$C$6:$G$6;));)
К сообщению приложен файл: 9957956.xls (34.0 Kb)
 
Ответить
СообщениеПроцент - для определения номера столбца, из которого нужно брать значение.

Вариант с облегченными формулами. Номера счетов обязательно в возрастающем порядке.
Отображение нулевых значений скрыто (галка в параметрах листа).

Используется доп. столбец для определения граничного числа:
Код
=СЧЁТЕСЛИ(C8:G8;">0")+H8

Месяц:
Код
=ЕСЛИ(СТРОКА(A1)>$H$5;;ИНДЕКС($A$8:$A$19;ПОИСКПОЗ(СТРОКА(A1);$H$8:$H$19)))

Счет (формула массива):
Код
=ЕСЛИ(B25;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС($C$8:$G$19;B25;)>0;$C$6:$G$6);СЧЁТЕСЛИ($B$25:B25;B25));)

Сумма:
Код
=ЕСЛИ(B25;ИНДЕКС($C$8:$G$19;B25;ПОИСКПОЗ(C25;$C$6:$G$6;));)
Автор - vikttur
Дата добавления - 07.08.2017 в 13:23
tuls Дата: Понедельник, 07.08.2017, 14:09 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Я не знаю, можно ли так делать, но вложу слегка подчищенную полную страницу расчета по сотруднику. Там много связей, и я ее пол-дня чистить буду от ошибок.
Не работает у меня «ваша» формула вставленная на красном поле - серая на том же месте, где в первом файле (пытался сохранить ссылки), и коричневая справа - куда я хотел переместить ее.
Тяжесть формулы меня смущает, так как таких листов будет около десятка плюс сводный лист, на который планирую всех вывести.
Заранее спасибо за помощь. Буду думать дальше.
К сообщению приложен файл: 1271542.xls (99.0 Kb)

Использую только Excel 2003. Так сложились звезды.
 
Ответить
СообщениеЯ не знаю, можно ли так делать, но вложу слегка подчищенную полную страницу расчета по сотруднику. Там много связей, и я ее пол-дня чистить буду от ошибок.
Не работает у меня «ваша» формула вставленная на красном поле - серая на том же месте, где в первом файле (пытался сохранить ссылки), и коричневая справа - куда я хотел переместить ее.
Тяжесть формулы меня смущает, так как таких листов будет около десятка плюс сводный лист, на который планирую всех вывести.
Заранее спасибо за помощь. Буду думать дальше.
Автор - tuls
Дата добавления - 07.08.2017 в 14:09
_Boroda_ Дата: Понедельник, 07.08.2017, 14:23 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Или так
Код
=ЕСЛИ(Q24="";"";ИНДЕКС(A$5:S$5;ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(O$7:S$18;M$7:M$18+СТОЛБЕЦ(O24:S24)%);СТРОКА(AD1));1)/1%+0,1))

или так
Код
=ЕСЛИ(Q24="";"";ИНДЕКС(O$5:S$5;ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(O$7:S$18;M$7:M$18+СТОЛБЕЦ(O24:S24)%-СТОЛБЕЦ($N24)%);СТРОКА(AD1));1)/1%+0,1))


Если диапазоны реально большие, а не как в файле - 12*5, то конечно будет тормозить. Виктор об этом выше уже писал
К сообщению приложен файл: 1271542_1.xls (92.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеИли так
Код
=ЕСЛИ(Q24="";"";ИНДЕКС(A$5:S$5;ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(O$7:S$18;M$7:M$18+СТОЛБЕЦ(O24:S24)%);СТРОКА(AD1));1)/1%+0,1))

или так
Код
=ЕСЛИ(Q24="";"";ИНДЕКС(O$5:S$5;ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(O$7:S$18;M$7:M$18+СТОЛБЕЦ(O24:S24)%-СТОЛБЕЦ($N24)%);СТРОКА(AD1));1)/1%+0,1))


Если диапазоны реально большие, а не как в файле - 12*5, то конечно будет тормозить. Виктор об этом выше уже писал
Автор - _Boroda_
Дата добавления - 07.08.2017 в 14:23
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вытащить ненулевые значения из таблицы в столбец (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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