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

Вход

Регистрация

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

 

= Мир MS Excel/Автоопределение конечного диапазона форулы - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Автоопределение конечного диапазона форулы (Формулы/Formulas)
Автоопределение конечного диапазона форулы
Wrascon Дата: Среда, 22.06.2016, 20:52 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Есть таблица для хранения ключей к играм. Столбец А - названия игр, B - ключи, С - подсчёт не ключей, в котором я добавляю знак + напротив активированного.
В ячейках C2, С9, С12, С18 лежит формула =ТЕКСТ (СЧИТАТЬПУСТОТЫ (C3:C8);"0;;+") действует на диапазоны C3:C8, C10:C11, C13:C17, C19:C21 и выдаёт количество не активированных ключей или + если они активированы все (чтобы при сортировке не выводить эту игру в список). Нужно сделать автоматическую формулу, которая будет работать с первой строчки ниже формулы, до конца границы первой игры или до следующей формулы, чувствительная к добавлению новых строк. Границы для каждой игры в диапазонах A2:C8, A9:C11, A12:C17, A18:C21.
К сообщению приложен файл: _Steam__.xlsx(27Kb)


Сообщение отредактировал Wrascon - Среда, 22.06.2016, 20:54
 
Ответить
СообщениеЕсть таблица для хранения ключей к играм. Столбец А - названия игр, B - ключи, С - подсчёт не ключей, в котором я добавляю знак + напротив активированного.
В ячейках C2, С9, С12, С18 лежит формула =ТЕКСТ (СЧИТАТЬПУСТОТЫ (C3:C8);"0;;+") действует на диапазоны C3:C8, C10:C11, C13:C17, C19:C21 и выдаёт количество не активированных ключей или + если они активированы все (чтобы при сортировке не выводить эту игру в список). Нужно сделать автоматическую формулу, которая будет работать с первой строчки ниже формулы, до конца границы первой игры или до следующей формулы, чувствительная к добавлению новых строк. Границы для каждой игры в диапазонах A2:C8, A9:C11, A12:C17, A18:C21.

Автор - Wrascon
Дата добавления - 22.06.2016 в 20:52
_Boroda_ Дата: Среда, 22.06.2016, 21:03 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 9369
Репутация: 3942 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=СЧЁТЕСЛИ(C3:ИНДЕКС(C3:C99;ЕСЛИОШИБКА(ПОИСКПОЗ("*";A3:A99;);СЧЁТЗ(B3:B99)));"")

И формат ячейки
0;;+
Если в одной игре может быть больше 99 ключей, то все 99 измените на большее число
К сообщению приложен файл: _Steam_1.xlsx(29Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=СЧЁТЕСЛИ(C3:ИНДЕКС(C3:C99;ЕСЛИОШИБКА(ПОИСКПОЗ("*";A3:A99;);СЧЁТЗ(B3:B99)));"")

И формат ячейки
0;;+
Если в одной игре может быть больше 99 ключей, то все 99 измените на большее число

Автор - _Boroda_
Дата добавления - 22.06.2016 в 21:03
Wrascon Дата: Среда, 22.06.2016, 21:11 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Работает правильно, но проверять на наличие символа нужно только столбец С
...хотя только что проверил вариант с тем, что в остальных ячейках пусто, то функция реагирует только после того, как пропадёт название следующей игры.


Сообщение отредактировал Wrascon - Среда, 22.06.2016, 21:16
 
Ответить
СообщениеРаботает правильно, но проверять на наличие символа нужно только столбец С
...хотя только что проверил вариант с тем, что в остальных ячейках пусто, то функция реагирует только после того, как пропадёт название следующей игры.

Автор - Wrascon
Дата добавления - 22.06.2016 в 21:11
Wrascon Дата: Среда, 22.06.2016, 21:19 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Только что удалил и поставил игру в другое место, тогда диапазон считает вместе со строкой для другой игры, а она не должна учитываться при проверке. Считает на одну строку больше, но этого не было, пока в той ячейке была функция
В первой таблице для последней игры считает 3 пустые строки вне границы игры A18:C21
[moder]Так покажите уже все то, что наудаляли и навставляли[/moder]


Сообщение отредактировал _Boroda_ - Среда, 22.06.2016, 22:02
 
Ответить
СообщениеТолько что удалил и поставил игру в другое место, тогда диапазон считает вместе со строкой для другой игры, а она не должна учитываться при проверке. Считает на одну строку больше, но этого не было, пока в той ячейке была функция
В первой таблице для последней игры считает 3 пустые строки вне границы игры A18:C21
[moder]Так покажите уже все то, что наудаляли и навставляли[/moder]

Автор - Wrascon
Дата добавления - 22.06.2016 в 21:19
Wrascon Дата: Среда, 22.06.2016, 22:27 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Внёс изменения, оставил комментарии к ячейкам какие считают не правильно
К сообщению приложен файл: 0461818.xlsx(28Kb)


Сообщение отредактировал Wrascon - Среда, 22.06.2016, 23:27
 
Ответить
СообщениеВнёс изменения, оставил комментарии к ячейкам какие считают не правильно

Автор - Wrascon
Дата добавления - 22.06.2016 в 22:27
_Boroda_ Дата: Четверг, 23.06.2016, 01:03 | Сообщение № 6
Группа: Модераторы
Ранг: Экселист
Сообщений: 9369
Репутация: 3942 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Да, согласен, неверно считает
Вот такой вариант формулой массива (вводится одновременным нажатием Контрл Шифт Ентер)
Код
=СЧЁТЕСЛИ(C3:ИНДЕКС(C3:C99;ПОИСКПОЗ("1";B3:B99&1;)-1);"")
К сообщению приложен файл: 0461818_1.xlsx(30Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДа, согласен, неверно считает
Вот такой вариант формулой массива (вводится одновременным нажатием Контрл Шифт Ентер)
Код
=СЧЁТЕСЛИ(C3:ИНДЕКС(C3:C99;ПОИСКПОЗ("1";B3:B99&1;)-1);"")

Автор - _Boroda_
Дата добавления - 23.06.2016 в 01:03
Wrascon Дата: Четверг, 23.06.2016, 03:01 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Эта уже более правильно выглядит. Спасибо что помогли с формулами :D
 
Ответить
СообщениеЭта уже более правильно выглядит. Спасибо что помогли с формулами :D

Автор - Wrascon
Дата добавления - 23.06.2016 в 03:01
Wrascon Дата: Четверг, 23.06.2016, 16:26 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Что-то я снова столкнулся с проблемами в функции. В том же последнем приложенном файле добавляю комментарий в пустые строки B2, B9, B12, B18 и функция одной игры при наличии символов (во всех 3 ячейках следующей игры) начинает считать ячейки проверок ключей следующей игры, что даже другая функция не останавливает её. Нужно определиться как найти тот самый параметр, обозначающий конец списка ключей первой игры и начала следующей. Для меня, визуально, это название следующей игры, а конкретно 3 обведённые границей строчки.

В ячейках A2:C2, A9:C9, A12:C12, A18:C18 у меня название игры, комментарий к ней (Может даже другая функция, тоже как дополнение информации пускай даже в этой ячейке будет общее количество ключей данной игры, лишь бы это дало понять следующей функции на сколько строк вниз считать. Тогда эта функция будет специально зависеть от этой строчке и сама таблица примет более заполненный вид), и функция подсчёта не активированных ключей (проще говоря "В наличии" или "Доступно"). Делал это для того, чтобы при сворачивании её сгруппированных ячеек выводилась вся информация об игре. Но дело в том, что такая группировка собьётся, потому что она у меня смещена на строчку вниз и группирует лишь её содержимое, при сворачивании остаётся одна эта строка (как в сводной) о игре, но она не будет привязана к своему списку, что при сортировке приведёт к катастрофе. А именно названия игр и отдельные сгруппированные строки их ключей будут жить своей жизнью, что даже не отменишь повторной сортировкой. (Именно такая проблема у меня и произошла с 1000 строками, где 100 были названия игр, а как развернул, то загружать файл было уже поздно) Проблема группировки в том, что я не знаю как сделать её без сводной таблицы (при группировки ячеек вплотную, они объединяются и после этого список сворачивает не одну, а все игры), мне удобней продолжать работать в одной и пока с одной автоматизированной функцией.
Не думаю что мне будет удобно работать в 2 таблицах такого масштаба. Но это уже вопрос для другой темы
 
Ответить
СообщениеЧто-то я снова столкнулся с проблемами в функции. В том же последнем приложенном файле добавляю комментарий в пустые строки B2, B9, B12, B18 и функция одной игры при наличии символов (во всех 3 ячейках следующей игры) начинает считать ячейки проверок ключей следующей игры, что даже другая функция не останавливает её. Нужно определиться как найти тот самый параметр, обозначающий конец списка ключей первой игры и начала следующей. Для меня, визуально, это название следующей игры, а конкретно 3 обведённые границей строчки.

В ячейках A2:C2, A9:C9, A12:C12, A18:C18 у меня название игры, комментарий к ней (Может даже другая функция, тоже как дополнение информации пускай даже в этой ячейке будет общее количество ключей данной игры, лишь бы это дало понять следующей функции на сколько строк вниз считать. Тогда эта функция будет специально зависеть от этой строчке и сама таблица примет более заполненный вид), и функция подсчёта не активированных ключей (проще говоря "В наличии" или "Доступно"). Делал это для того, чтобы при сворачивании её сгруппированных ячеек выводилась вся информация об игре. Но дело в том, что такая группировка собьётся, потому что она у меня смещена на строчку вниз и группирует лишь её содержимое, при сворачивании остаётся одна эта строка (как в сводной) о игре, но она не будет привязана к своему списку, что при сортировке приведёт к катастрофе. А именно названия игр и отдельные сгруппированные строки их ключей будут жить своей жизнью, что даже не отменишь повторной сортировкой. (Именно такая проблема у меня и произошла с 1000 строками, где 100 были названия игр, а как развернул, то загружать файл было уже поздно) Проблема группировки в том, что я не знаю как сделать её без сводной таблицы (при группировки ячеек вплотную, они объединяются и после этого список сворачивает не одну, а все игры), мне удобней продолжать работать в одной и пока с одной автоматизированной функцией.
Не думаю что мне будет удобно работать в 2 таблицах такого масштаба. Но это уже вопрос для другой темы

Автор - Wrascon
Дата добавления - 23.06.2016 в 16:26
Wrascon Дата: Четверг, 23.06.2016, 17:16 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Добавил изменения в пример, расписал условия функций. Заполнил ячейки B2, B9, B12, B18 функцией подсчёта всех ключей одной игры
Код
=СЧЁТЕСЛИ(B3:B8;"*")

P.S. Похоже подсчёт количества всех ключей для каждой игры также придётся автоматизировать, если для неё появятся новые ключи, от чего в конце списка произойдёт смещение строк и даже привязка к ячейке через $ не поможет...
К сообщению приложен файл: 2466513.xlsx(29Kb)


Сообщение отредактировал Wrascon - Четверг, 23.06.2016, 17:32
 
Ответить
СообщениеДобавил изменения в пример, расписал условия функций. Заполнил ячейки B2, B9, B12, B18 функцией подсчёта всех ключей одной игры
Код
=СЧЁТЕСЛИ(B3:B8;"*")

P.S. Похоже подсчёт количества всех ключей для каждой игры также придётся автоматизировать, если для неё появятся новые ключи, от чего в конце списка произойдёт смещение строк и даже привязка к ячейке через $ не поможет...

Автор - Wrascon
Дата добавления - 23.06.2016 в 17:16
Wrascon Дата: Среда, 29.06.2016, 12:56 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Ап
К сообщению приложен файл: 7588495.xlsx(29Kb)


Сообщение отредактировал Wrascon - Среда, 29.06.2016, 13:00
 
Ответить
СообщениеАп

Автор - Wrascon
Дата добавления - 29.06.2016 в 12:56
Wrascon Дата: Понедельник, 11.07.2016, 07:01 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
В общем доделал я оформление таблицы. Осталось добавить автоматизации формулам при добавлении строк и проверку на 17 символов и лучше делать это по 2 варианту вместе с сортировкой.
1. Серые названия игр заполнять от "Игра итоги" по формуле поиска текста (если такая есть) минус " итоги" и применять на весь диапазон названий, чтобы при добавлении новой строчки сразу появлялись в пустой ячейке.
2. Промежуточные итоги подсчёта должны сами определять диапазон ключей (голубой) каждой игры по отдельности и реагировать на появление новых, а также смещение, если ключи будут добавлены в другую игру. Скорее всего это растягивающийся массив на весь столбец B2:B28.
3. Тоже самое с доступностью ключей C2:C28.
Группировка работает для меня вверх ногами, нужно реализовать это через разворачивающийся список как в сводной таблице (не знаю есть ли такой без добавления ещё одной). Сделал вариант таблицы на втором листе, где показал как должно сворачиваться на итогах и оставлять видимыми итоги общие и по каждой игре.
Проблема заключается в том, что таблицу теперь невозможно сделать "умной", а при сортировке по А-Я названию игры удаляются промежуточные итоги.
К сообщению приложен файл: Steam__.xlsx(15Kb)


Сообщение отредактировал Wrascon - Понедельник, 11.07.2016, 07:04
 
Ответить
СообщениеВ общем доделал я оформление таблицы. Осталось добавить автоматизации формулам при добавлении строк и проверку на 17 символов и лучше делать это по 2 варианту вместе с сортировкой.
1. Серые названия игр заполнять от "Игра итоги" по формуле поиска текста (если такая есть) минус " итоги" и применять на весь диапазон названий, чтобы при добавлении новой строчки сразу появлялись в пустой ячейке.
2. Промежуточные итоги подсчёта должны сами определять диапазон ключей (голубой) каждой игры по отдельности и реагировать на появление новых, а также смещение, если ключи будут добавлены в другую игру. Скорее всего это растягивающийся массив на весь столбец B2:B28.
3. Тоже самое с доступностью ключей C2:C28.
Группировка работает для меня вверх ногами, нужно реализовать это через разворачивающийся список как в сводной таблице (не знаю есть ли такой без добавления ещё одной). Сделал вариант таблицы на втором листе, где показал как должно сворачиваться на итогах и оставлять видимыми итоги общие и по каждой игре.
Проблема заключается в том, что таблицу теперь невозможно сделать "умной", а при сортировке по А-Я названию игры удаляются промежуточные итоги.

Автор - Wrascon
Дата добавления - 11.07.2016 в 07:01
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Автоопределение конечного диапазона форулы (Формулы/Formulas)
Страница 1 из 11
Поиск:

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