» Excel FAQ (часть 3)

Collapse Troll
В третьем столбце записать формулу
=ЕСЛИ(ЕНД(ПОИСКПОЗ(Столбец2;Столбец1;0));Столбец2;""), а потом отфильтровать непустые значения

Такой вопрос: Есть таблица из 20 строк, и нужно сделать суммирование значений этой таблицы в зависимости от нужного кол-ва строк.

Т.е. есть отдельная ячейка, которая указывает какое кол-во строк суммировать. Но вот как сделать что-бы суммировалось нужное кол-во (5, 10, все 20 и т.п.) - я не знаю. Помогите !

zzzolegzzz
Функция ПромежуточныеИтоги не поможет?

zzzolegzzz
=СУММ(НачалоТаблицы:ИНДЕКС(Таблица;КолСтрок))

panda3, спасибо, то что надо!
ZORRO2005, спасибо, тоже вариант.

p.s.: в варианте ZORRO2005 нужно писать в третьем столбце такую формулу:

Цитата:

=СЧЁТЕСЛИ($A$1:$A$5;B1)=0

и затем отфильтровать по значению "истина" в третьем столбце.

Всем привет!
Есть лист Excel, необходимо закрепить область (поднимаю 4 строки вверх и оставляю 5-ю самой первой). При закреплении блокируется весь лист, т.е. вверх-вниз курсор не перемещается (ни с клавиатуры, ни мышью), при этом на бегунке вертикальной прокрутки перемещение отображается, а сам лист остается на месте. При этом влево-враво курсор перемещается нормально, все функции работают.
На других листах в этой книге области закрепляются нормально.
Буду благодарен, если кто-то подскажет в чем может быть проблема.

Результат закрепления зависит от выделения. Если выделена ячейка, то закрепляется верх и левая область от нее.
Если выделена строка, то закрепляется верх. Если столбец - левая область.

panda3

Цитата:

=СУММ(НачалоТаблицы:ИНДЕКС(Таблица;КолСтрок))

Супер.
-----------------------------------------
Есть два массива A1:A20 и B1:B20
Делаю конкатенацию в С1:C20

Код: {=A1:A20&B1:B20}

ZORRO2005
СЧЁТЕСЛИ хочет диапазон, а ты ей подсовываешь вычисленный массив.
А если так:
{=СЧЁТ(ЕСЛИ(A1:A20&B1:B20=A1&B1;1;""))}

panda3
В другом форуме была задача:

Цитата:

Например, есть четыре клиента. Два из них покупали весь год в основном разные болты, а два других например в основном разные гайки. Надо этих четырех клиентов разбить в две группы - "болтовиков" и "гаечников".

Я предложил решение http://slil.ru/25445871
но остались вопросы по связыванию диапазонов.

Подскажите пожалуйста, вот к примеру у меня в графе по строкам идут всякие цифры:
123
12345
321
ну и так далее несколько тысяч позиций, а нужно чтобы было вот так:
Х-123
Х-12345
Х-321
Можно ли как-нибудь добавить префикс по всем позициям?

SFX2008
Конечно. ="X-"&A1
А потом (если нужно) заменить формулу на значения.

Подскажите пожалуйста, вот к примеру у меня в
a1="число"
a2="число"
a3="число"
и т.д, до а30 (всего 30 ячеек) необходимо, чтобы в ячеике в1 отобразилось последнее "а" столбца "число"

пример 1:
a1=1
a2=8
a3=6, результат: в1=6
остальные ячеики до а30 пустые

пример 2:
a1=1
a2=8
a3=6
a4=4, результат: в1=4
остальные ячеики до а30 пустые

пример 3:
a1=0
a2=0
a3=0
a4=4
a5=8
а6=21, результат: в1=21
остальные ячеики до а30 пустые

Или так, если а1=0 то анализируем а2 (т.е а2>0 или нет), если а1>0 то в в1=а1 и т. д. до а30, т.е необходимо, чтобы в в1 отображалось последнее число которое>0 из области а1-а30, вот так, может быть корректнее будет заддан вопрос

ZORRO2005

Цитата:

Я предложил решение http://slil.ru/25445871

Ну, я бы написал покороче:
Вариант3 (для второй строки, формула массива)
{=ВЫБОР(2+ЗНАК(СЧЁТ(ЕСЛИ((A$2:A$20=A2)*(B$2:B$20="Болты");1))-СЧЁТ(ЕСЛИ((A$2:A$20=A2)*(B$2:B$20="Гайки");1)));"Гайки";"Одинаково";"Болты")}
и растянуть вниз.
Может еще можно упростить, лень думать.
Не понял про связывание диапазонов...
P.S. А вообще, для подобного анализа есть сводная таблица...

Добавлено:
virginijus
Формула массива:
{=ИНДЕКС(A1:A30;МАКС(ЕСЛИ(A1:A30>0;СТРОКА(A1:A30);0)))}

panda3
Спасибо, красиво

Цитата:

Формула массива:
{=ИНДЕКС(A1:A30;МАКС(ЕСЛИ(A1:A30>0;СТРОКА(A1:A30);0)))}

Жаль но она мне не подходит, потому, что мне необходимо не максимальное а последнее значение в этом масиве а оно может быть и минимальное

virginijus

Цитата:

Жаль но она мне не подходит, потому, что мне необходимо не максимальное а последнее значение в этом масиве а оно может быть и минимальное

вы шутите или издеваетесь?! Проверьте!
Это найдёт максимально большую заполненную строчку!! А число там может быть и самое маленькое, лишь бы положительное!!!! (>0)
хотя я бы сделал <> 0, но это уже зависит от ваших данных...
{=ИНДЕКС(A1:A30;МАКС(ЕСЛИ(A1:A30<>0;СТРОКА(A1:A30);0)))}

Добавлено:
а ещё лучше так:
{=ИНДЕКС(A1:A30;МАКС(ЕСЛИ(ЕПУСТО(A1:A30);0;СТРОКА(A1:A30))))}

Цитата:

вы шутите или издеваетесь?! Проверьте!

Упаси бог, не издеваюсь, и деже не думал, простите, обибеть не хотел, ппробовал - выдает "#VALUE!". Буду дальше пробовать. Спасибо SERGE_BLIZNUK, panda3

SERGE_BLIZNUK
Ну он вроде в постановке задачи написал, что нужно именно > 0
virginijus
Как это может выдавать "#VALUE!" я вообще не понял. Там ни одной функции нет, которая может вернуть ошибку!
Если только "#VALUE! есть в самом диапазоне, и оно просто копируется оттуда в результат.

Простите, тормознул, забыл про {} т.е. CTRL+SHIFT+ENTER, теперь выдает 0, а когда появляетьса значение в S32 и до S47 выдает #REF! вот как выгледит в оргинале моя : {=INDEX(S17:S47;MAX(IF(S17:S47>0;ROW(S17:S47);0)))} , если пишу{=INDEX(S17:S47;MAX(IF(S17:S47>0;ROWS(S17:S47);0)))} а так скорее всего и должно быть то результат - S47, если пишу {=INDEX(S17:S47;MAX(IF(S17:S47>0;COLUMS(S17:S47);0)))} результат - S17

как можно переделать формат дата =22.02.2007 (39135) в формат число 20070222? стандартными формулами.

vasiliy74
Т.е. у тебя в ячейке 39135, а ты хочешь, чтобы стало 20070222?

vasiliy74
Если да, то вот так
=ГОД(A1)&ЕСЛИ(МЕСЯЦ(A1)<9;"0"&МЕСЯЦ(A1);МЕСЯЦ(A1))&ЕСЛИ(ДЕНЬ(A1)<9;"0"&ДЕНЬ(A1);ДЕНЬ(A1))

не получается вставить данные из html, чтобы в ячейках был не формат число(дата), а числовой или общий.

Добавлено:
не получается вставить данные из html, чтобы в ячейках был не формат число(дата), а числовой или общий.

Mushroomer
Гхм...
=ТЕКСТ(A1;"ГГГГММДД")

panda3
Согласен. Твой вариант изящнее.

Можно сделать так, чтобы в =СЦЕПИТЬ("Копия "; "верна") на экране слово "верна" оказывалось подчёркнутым?

А подскажите, как можно использовать функцию СЦЕПИТЬ, но для изменяющегося количества ячеек?
К примеру, у меня есть 5 столбцов, в которых находятся текстовые строки.
Мне их нужно объединить - это делается легко, либо через СЦЕПИТЬ, либо через &. Но это легко такую формулу прописать,когда столбцов немного. А если их 100 штук?
Как сделать так, чтобы сцепление произошло ячеек из диапазона?

Особенно это важно, когда в этом диапазоне появляются новые столбцы. Желательно, чтобы формала продолжала сразу работать, без корректировки. Ну как СУММ например.

virginijus
Случайно наткнулся на похожий вопрос на subscribe.ru

Цитата:

Если список данных находится в столбце А и этот список без пустых ячеек, то чтобы получить значение последней заполненной ячейки в этом списке: =ИНДЕКС(A:A;СЧЁТЗ(A:A))

Вот только на ноль проверки не хватает

Подскажите, excel не хочет нормально сортировать строки - первый столбец - в нем числа от 1 до 5000 вразнобой - пытаюсь отсортировать чтобы было 1, 2, 3, 4 и т.п - excel сортирует по-своему - 1, 10, 1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 101, 1010, 1011 и т.п. - ну что за ерунда - бьюсь уже второй час - ничего не получается!