» Excel FAQ (часть 5)

Подскажите пожалуйста такую вещь:
Есть Диапазон ячеек на листе "Пикет-расстояние", типа
285    60
286    60
287    60
288    60
289    60
290    60
291    60

на другом листе "АДС итоговая" из этого диапазона идет выборка формулой =ВПР(АДС!D7;Карьер1;2;ЛОЖЬ), где Карьер1 - тот самый диапазон и подставляется соответствующее значение из второго столбца диапазона в столбец I . Все работает нормально с одним значением. Но нужно сделать так, что бы АДС!D7 в одной ячейке можно было указать несколько значений через разделитель (например 285; 286; 288), а в столбец I выводилась бы сумма этих аргументов из второго столбца диапазона. Можно ли это сделать как то формулами, или только макрос писать?

Файл тут

surgutfred
Формулой то можно, например такой:
=СУММПРОИЗВ(СУММЕСЛИ('Пикет-расстояние'!A$3:J$100;--ПСТР(ПОДСТАВИТЬ(D7;";";" ");(СТРОКА($1:$10)-1)*20+1;20);'Пикет-расстояние'!B$3:K$100))

Дело в том, что файл у Вас и так не маленький. И если Вы нагрузите еще процессор формулами массива, на не маленьких диапазонах... Ну вобщем сами решайте.
P.S. В желтых ячейках решение.
http://zalil.ru/31573738

kimtan
Что то не то считает, или вы не поняли задачу. если допустим выбрать только 285 пикет, то в итоге должно получится 60 км, а по вашей формуле выходит 123. Соответственно выбираем 2 пикета, должно получиться 120, а по формуле выдает 246.

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

Формула захватывает все 4 столбца с листа "Пикет-расстояние", я подумал что так нужно. Если нужно брать данные только с первого столбца - поправить диапазоны:
=СУММПРОИЗВ(СУММЕСЛИ('Пикет-расстояние'!A$3:A$100;--ПСТР(ПОДСТАВИТЬ(D7;";";" ");(СТРОКА($1:$10)-1)*20+1;20);'Пикет-расстояние'!B$3:B$100))

Большое спасибо, то что надо. Эх учиться и учиться мне еще....

Имеется ну о-о-очень длинный список, отсортированный в алфавитном порядке, кстати, зашищённый от модификации. По ходу работы нужно часто перемещаться между разными строками, то в начало, то в середину... Как это быстрее сделать?
В любом современном файловом менеджере под Windows (включая штатный Explorer, Total Commander и т.д.) поиск и переход между файлами реализован так: наибираешь в отдельном окошке или прямо на панели поисковый запрос - и по мере ввода список файлов автоматически прокручивается к нужной строке (и даже подсвечивает последний совпадающий вариант). Нельзя ли реализовать в MS Excel 2003 что-то хотя бы отдалённо похожее?

З.Ы
Думал над использованием закладок в качестве промежуточного решения, но это тоже не слишком удобно, да и не изящно как-то. Может, есть более удачные решения?

oshizelly
можно в сторону фильтров данных посмотреть (см. меню Данные/Фильтр) и в справку по слову фильтр стоит глянуть (например, там, там)

asbo 18:15 27-07-2011
Цитата:

Думаю, что можно попытаться проверить (как я писал выше - "а как же различать тогда одинаково названные макросы в разных книгах?") на предмет совпадения имен функций и макросов. Месторасположение его проверить - стандартное или нет.

А какое у него стандартное место жительства? Если C:\Documents and Settings\%user_name%\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLS - то тогда стандартное.
Версия про совпалдение имен функций и макросов тоже не подтверждается. Это легко проверить: открываем новый документ и жмем Alt+F8. Поскольку в новом документе никаких макросов нет, но, стало быть, все макросы - из Personal.xls.

oshizelly
Ну, тады не знаю, что еще предположить... Место, вроде бы, стандартное.

ALeXkRU 16:34 19-08-2011
Цитата:

можно в сторону фильтров данных посмотреть (см. меню Данные/Фильтр)

Поигрался с автофильтром... Или я чего-то не понимаю, или это совсем не про то. При помощи ручной прокрутки можно быстрее перейти к нужной строке, чем совершать все эти промежуточные шаги: открыть выпадающий список - выбрать нужную букву - перейти к строке - открыть выпадающий список - выбрать "All" - прокрутить руками до нужной строки...
Пробовал вводить несколько букв, например, чтобы перейти к строке "Белов" ввести "Б-Е-Л...", но так не получается: при нажатии на "Б" список прокручивается до "Баранов", а при нажатии следующей "Е" прыгает на "Емельянов".
Неужели в Экселе нет более подходящего инструмента быстрой прокрутки?

oshizelly
Как вариант с дружественного форума - http://www.planetaexcel.ru/tip.php?aid=262 ...

oshizelly

Цитата:

Поигрался с автофильтром...

хмм.. ни разу про автофильтр не упоминал я, вроде как, в основном на расширенные намекал

Подскажите Как сделать, чтобы боковой столбец с ячейками (к примеру от А1 до А131) "полз" при скроллинге страницы вправо и влево? Т.е. столбец при прокрутке окна оставался постоянно на виду. Это к тому что верхняя строка (к примеру от А1 до Z1) получается длинная и при скролинге в правую сторону, столбец с ячейками от А1 до А131 остается "за кадром" его не видно и приходится постоянно прокручивать снова влево чтобы сориентироваться по нужной ячейке в этом столбце.

Добавлено:
ой забыл уточнить, что у меня Excel 2003, потому что я знаю что в 2007 делается такое через "Вкладка>> Вид>> закрепить области"

ddsmx

Цитата:

Т.е. столбец при прокрутке окна оставался постоянно на виду

Активизируете ячейку B1, Окно->Закрепить области.

ddsmx
Вот здесь

и здесь

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

А чтобы верхнюю строку закрепить какую ячейку нужно активировать?


Добавлено:
Все понял A2 . Спасибо разобрался, очень помогли!

есть столбец, сверху - ячейка "бюджет", ниже - второй строкой столбца "остаток", а начиная с третей строки столбца я добавляю новые цифры

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

остаток = бюджет - всё что ниже третьей строки столбца С

в общем вопрос сводится к этому - есть ли выражение описывающие это
всё что ниже третьей строки столбца

anynamer
A1-СУММ(A3:A65000)

Подскажите плиз есть столбец с наименованиями. Нужно подсчитать количество. Используем СЧЕТЗ, но в некоторых ячейках есть формулы, которые смотрят данные в другой книге и при определенном значении подставляют в эту ячейку значение, в противоположном случае ячейка остается пустой. Но СЧЕТЗ считает даже визуально пустую ячейку если в ней есть формула. Короче надо подсчитать только видимые значения, пропуская ячейки с формулами,которые не выдают ничего.

aidomars
спасибо за помощь!
я догадывался что это можно сделать указав макс кол-во, а нет ли выражения условия со знаком ">", т.е. больше(дальше) третей ячейки?

surgutfred
Может использовать СЧЕТЗ -СЧИТАТЬПУСТОТЫ если количество наименований неизменно и подсчет не по всему столбцу.
anynamer
Шото я не пойму вопроса. Данные в одном столбце? Визуально нарисуйте тут таблицу с пятком строк.

aidomars

Цитата:

Шото я не пойму вопроса. Данные в одном столбце?

да в одном (изображение будет мало информативно - там только цифры), но я уже разобрался - ваш совет помог, меня только интересует можно ли указать вместо от 3 строки до 65000, выражение >3 и до конца (т.е. если есть аналог этому в excel)?

Цитата:

да в одном (изображение будет мало информативно - там только цифры), но я уже разобрался - ваш совет помог, меня только интересует можно ли указать вместо от 3 строки до 65000, выражение >3 и до конца (т.е. если есть аналог этому в excel)?

Держи =A1-СУММ(ДВССЫЛ("A3:A" & СЧЁТ(A3:A65000)+2))

Понимаю, что такие просьбы не приветствуются, но, буду благодарен за любую информацию.
Ситуация следующая – есть список телефонов, который находится в первой колонке. Во второй колонке есть куча объявлений (ячейки смешанного содержания, т.е. текст и номера телефонов). Причем сложность в том, что данные из одной колонки частично совпадают с данными из второй. Пример – телефон в первой колонке 1111111. Телефон во второй (ХХХ) 1111111.
Нужен макрос, который будет брать данные из первой колонки, и сверять со всем данным из второй, и даже при частичном совпадении выделять ячейку (или само значение частичного совпадения) красным цветом. Дальше цикл повторяется, т.е, данные из первой колонки второй строки …. и та же процедура.
Как аналог, без макроса это делается прекрасно при помощи Ctrl+f – найти и заменить – вводим в поле «Найти» (например 1111111), а в поле «Заменить на» - 1111111 и изменить формат ячейки на такой же но закрашенный красным. Клацаем «Заменить всё» и берет все данные, даже частично совпадающие со значением «Найти»

aidomars

Цитата:

Держи =A1-СУММ(ДВССЫЛ("A3:A" & СЧЁТ(A3:A65000)+2))

спасибо! буду разбираться что это за шифр

Цитата:

surgutfred
Может использовать СЧЕТЗ -СЧИТАТЬПУСТОТЫ если количество наименований неизменно и подсчет не по всему столбцу.

Был такой вариант, но там пустот слишком много. Т.е.

Данные
Пустота
Пустота
Формула с пустотой
Данные

Примерно так, и в итоге отрицательный результат

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

surgutfred
Формула:
=СЧЁТЕСЛИ(диапазон;"") подсчитает количество ячеек в диапазоне, содержащих текстовую строку нулевой длины - "" и абсолютно пустые ячейки.
Как подсчитать количество ячеек, содержащих значения, не трудно догадаться:
Общее количество ячеек в диапазоне минус СЧЁТЕСЛИ(диапазон;"")

Итоговая формула:
=ЧСТРОК(диапазон)-СЧЁТЕСЛИ(диапазон;"")
P.S. При условии что диапазон вертикальный...

Цитата:

Итоговая формула:
=ЧСТРОК(диапазон)-СЧЁТЕСЛИ(диапазон;"")

Огромное спасибо, то что надо!

Excel 2007 открываю файлы двойным щелчком мыши. Появляется excel без этого документа, вообще без документа. Если нажимаю предварительный просмотр или во весь экран документ виден и распечатывается. Буквально вчера открывались нормально. На другом компьютере открывается нормально. Не понимаю эту ситуацию, что можно сделать?

7615790
Тут поспрошай.