» Excel FAQ (часть 2)

ZORRO2005
Цитата:

Посмотрел Вашу табличку.
Эту задачу лучше решать через сводные таблицы.

Я всё равно ен доганяю, как эту задачку решать и про сводные таблички ничего мне не понятно, тебя не затруднит сделать конкретный пример решения в Excel ?

Какие формулы в каких ячейках тоже не понятно

Народ с моим вопросом поможете?

Как найти адрес последней числовой ячейки в строке? В строке кроме числовых ячеек находятся еще ячейки с различными символами и пустые ячейки. Данные в ячейках никак между собой не связаны.

seva1
Если файл - это типа книга Эксель, а вкладки - типа листы (внизу ярлыки), то тыркать двойным кликом на ярлык и переименовывать, ну или меню на правой кнопке - переименовать. В чем вопрос? Вот на мой вопрос уж точно ответа не будет кажись.

Vitus_Bering

Цитата:

Возможно ли с помощью формулы решить след. задачку:
надо посчитать миним. значение в строке, но большее нуля.

Попробуй еще так (вроде бы тоже работает):
=ЕСЛИ(СЧЁТЕСЛИ(A2:A21;"<=0")>0;НАИМЕНЬШИЙ(A2:A21;СЧЁТЕСЛИ(A2:A21;"<=0")+1);МИН(A2:A21))

AuthorR
Цитата:

Цитата: Возможно ли с помощью формулы решить след. задачку:
надо посчитать миним. значение в строке, но большее нуля.

Попробуй еще так (вроде бы тоже работает):
=ЕСЛИ(СЧЁТЕСЛИ(A2:A21;"<=0")>0;НАИМЕНЬШИЙ(A2:A21;СЧЁТЕСЛИ(A2:A21;"<=0")+1);МИН(A2:A21))

AuthorR и SERGE_BLIZNUK

Спасибо, поясните как работает функция НАИМЕНЬШИЙ.

Еще вопрос: как перенести значение ячейки с формулой в другую ячейку, чтобы в этой другой ячейки было только значение.

Vitus_Bering
Цитата:

Еще вопрос: как перенести значение ячейки с формулой в другую ячейку, чтобы в этой другой ячейки было только значение.

Скопировать. А вставлять через специальную вставку: меню Правка -> Специальная вставка -> Значения.

Mushroomer
Надо сделать без внешних манипуляций.

Vitus_Bering
Просьба пояснить понятие "внешние манипуляции". Я указал путь по меню Excel или по действию контекстной (правой) кнопки мышки опять же в Excel.

Vitus_Bering
Цитата:

Надо сделать без внешних манипуляций.

тогда никак! Только или ручками, или через макрос (модуль) на VBA

НАИМЕНЬШИЙ из стандартного хелп'а [more]
Возвращает k-ое наименьшее значение в множестве данных. Эта функция используется для определения значения, занимающего определенное относительное положение в множестве данных.

Синтаксис

НАИМЕНЬШИЙ(массив;k)

Массив — массив или диапазон числовых данных, для которого определяется k-ое наименьшее значение.

k — позиция (начиная с наименьшей) в массиве или интервале ячеек данных.

Заметки

Если массив пуст, то функция НАИМЕНЬШИЙ возвращает значение ошибки #ЧИСЛО!.
Если k &#8804; 0 или превышает число точек данных, то функция НАИМЕНЬШИЙ возвращает значение ошибки #ЧИСЛО!.
Если n — количество точек данных в аргументе массив, то НАИМЕНЬШИЙ(массив;1) равняется наименьшему значению, а НАИМЕНЬШИЙ(массив;n) равняется наибольшему значению.
[/more]

SERGE_BLIZNUK
Mushroomer
Спасибо, вопрос закрыт.

Цитата:

ZORRO2005
Цитата:
Посмотрел Вашу табличку.
Эту задачу лучше решать через сводные таблицы.

Я всё равно не доганяю, как эту задачку решать и про сводные таблички ничего мне не понятно, тебя не затруднит сделать конкретный пример решения в Excel ?

Какие формулы в каких ячейках тоже не понятно

Мож кто ответит?

gyurza2000
Мож кто в PM посмотрит
Личный Ящик на что?

Цитата:

Как найти адрес последней числовой ячейки в строке? В строке кроме числовых ячеек находятся еще ячейки с различными символами и пустые ячейки. Данные в ячейках никак между собой не связаны.

У меня тут возникла идея: рядом с ячейками с информацией добавить ячейки, в которых, если в соседней ячейки появляется число, то выставляется число. Затем эти числа в проверочных ячейках увеличеваются. Например, в ячейке А1 число, значит в проверочной ячейке А2 число 1. В ячейке А3 находятся не числовые данные, значит в проверочной ячейке А4 ничего нет. В ячейке А5 - число, значит в проверочной ячейке А6 - число 3 и т.д. Затем по функции АДРЕС(СТРОКА();ПОИСКПОЗ(1E+306;A1:A100;1)-1) определить адрес последней информационной числовой ячейки в строке.

У кого-нибудь есть предложения, как решить эту задачу без создания дополнительных проверочных ячеек?

Jampire

Цитата:

У кого-нибудь есть предложения как решить эту задачу без создания дополнительных проверочных ячеек?

Хорошая идея!

Вот эта формула работает как формула массива (ввод с Ctrl-SHIFT):

Код: =ADDRESS(MAX(IF(ISNUMBER(A1:A99),ROW(A1:A99),0)),COLUMN(A1:A99))

Yuk
Спасибо.
Но возникла другая проблема. Поиск последней числовой ячейки - это всего лишь часть кода функции в ячейке. Весь код ввести не получается, т.к. в Екселе стоит ограничение на 7 вложенных функций. Возможно ли как-то обойти это ограничение?

Jampire
Для последнего значения в строке можно использовать

Код: {=ИНДЕКС(1:1;МАКС(СТОЛБЕЦ(1:1)*(1:1<>"")))}

Jampire
А ты уверен, что тебе нужен этот адрес. Может можно всю формулу по-другому сформировать. Давай всю задачу.
Другие варианты - либо дополнитеные ячейки, либо пользовательская функция на ВБА.

Мне нужно чтобы число которое я вбиваю в ячейку автоматически умножалось на 25. Какую формулу использовать??? Хочу обойтись без ссылок на др.ячейки

Jampire
Вот что у меня получилось
2-ая строка проверочная

Никак не могу догадаться как сделать в одну формулу

Добавлено:
mikim5
=ЧИСЛО*25
Вместо "ЧИСЛО" пишите свое число ))
Но если серьезно думаю только через VBA

Yuk

Цитата:

А ты уверен, что тебе нужен этот адрес. Может можно всю формулу по-другому сформировать. Давай всю задачу.

Таблица служит для подсчета очков спортсменов на этапах чемпионата. Очки начисляются в зависимости от времени прибытия спортсмена на финиш. Предусмотрен штраф. В нем вся проблема. Штраф начисляется на текущем этапе по числу пропущенных предыдущих этапов. На последующие этапы штраф не распространяется - он отработан на текущем. Пример:

1 этап 2 этап 3 этап 4 этап 5 этап 6 этап
98 н.у. 97 н.у. н.у. 100


Примечание: н.у. - этап спортсмен пропустил (не учавствовал).

Согласно этой таблице пилот должен получить штраф -5 очков на 3 этапе и -10 очков на 6. Далее спортсмен, если не будет пропускать этапы, штрафоваться не будет.
Нам нужно подсчитать число фраз "н.у." между последним проведенном спортсменом этапом и текущем этапом.



В ячейку АС19 после находящейся там формулы я ввожу

Код: -5*СЧЁТЕСЛИ(АДРЕС(СТРОКА();МАКС(ЕСЛИ(ЕЧИСЛО(E19:АДРЕС(СТРОКА();СТОЛБЕЦ()-5));СТОЛБЕЦ(E19:АДРЕС(СТРОКА();СТОЛБЕЦ()-5));0))):АДРЕС(СТРОКА();СТОЛБЕЦ()-5);"н.у.")

Jampire
Сколько максимум Этапов?

Добавлено:
Я думаю эту задачу надо решать намного проще.
Как очки считаются?Со штрафами понятно

ZORRO2005
Всего 10 этапов.
На скриншоте выделена ячейка с формулой подсчета очков. Лучшему времени этапа присваивается 100 баллов, остальным участникам присваивается процент его времени относительно лучшего времени

Jampire

Код: =IF(TODAY()>=_5_etap,IF(AB19="--","nu",
IF(AB19=MIN(AB$5:AB$31),100,MIN(AB$5:AB$31)/AB19*100)-
(COLUMN(X19)-MAX(IF(ISNUMBER($E19:X19),COLUMN($E19:X19),4)))),
"--")

Yuk

Цитата:

_5_этап насколько я понимаю именнованная ячейка. То есть при переходе к следующему этапу придется это менять во всех формулах, что не есть гуд. Может можно назвать ячейку типа _последний_этап? Тогда достаточно будет просто либо сменить ячейку, либо ввести в нее новую дату.

Да, это именнованная ячейка. Но даты этапов вводятся заранее и все сразу. Таблица должна показывать результаты спортсменов на любом этапе в любой момент времени. А если каждый раз менять дату, то каждый из 10 этапов будет являться текущим. Что значит:
Цитата:

Тогда достаточно будет просто либо сменить ячейку

? Не понял.

А что если номер этапа брать из шапки этапа (на скрине ячейки красного цвета)? Например, _ЯЧЕЙКА("содержимое";АДРЕС(3;СТОЛБЕЦ()-4))?

Jampire

Цитата:

А если каждый раз менять дату, то каждый из 10 этапов будет являться текущим.

Каюсь, тормознул.

Цитата:

А что если номер этапа брать из шапки этапа

Угу:

Код: =IF(TODAY()>=INDIRECT("_"&SUBSTITUTE(Y$3," ","_")),...

Помогите пожалуйста такая проблема делаю таблицу. Есть 3 категории год ввода, прирост, объем производства. Требуется заполнить эту таблицу например если в в каком либо году есть ввод то прирост начинается со следующего года если ввод в 06, то в 07-30%, 08-60%, 09-100% от планируемой цифры в 06. В объемах такая ситуация если объем был до этого то (05 году) от он остается без изменений если нет ввода, а если планируется ввод то к этому объему прибавляется прирост. Это была бы плевая задача если года вода были в одном году а они плавают по всему диапазону годов 06-15г.г. (где то есть а где то нет), отсюда куча ошибок и при заполнении прям тупишь по полной, прям крик души кто нибудь делал подобное, я тут пытался через логические оперторы ЕСЛИ но что то не как не додумаю.
И еще прирост должен быть по сумме биться с объемом например объем 05 + прирост 10 равен объему 10.
Пжжжжжжжалуйстааааааааааааа помогите если сможите.

kolinich
Может быть такая ситуация: в 07 году прирост от предыдущего года и в этом же году планируется ввод?


Добавлено:
Yuk
Спасибо, все работает, вы мне очень помогли .
Еще 2 вопроса по другой теме, но в Екселе.
1. Я защищаю лист, ставлю в настройках разрешить выделение заблокированных ячеек, выделение незаблокированных ячеек и сортировку. После защиты выделять ячейки Ексель разрешает, но сортировку нет! Пишет, что сортировка невозможна, лист защищен, снимите защиту с листа. В чем тут дело?
2. Как с клавиатуры переключаться между листами книги?

Jampire

Цитата:

2. Как с клавиатуры переключаться между листами книги?

Ctrl+PageDown/PageUp