» Excel FAQ (часть 4)

elle_by
Вот Ваш файлик без пляшущих человечков:
http://webfile.ru/4117628

Доброго времени суток. Имеется следующая проблема:
Есть ячейка, содержащая текст. Необходимо сделать так, чтобы слова в тексте автоматом разделялись запятыми, а в конце него ставилась точка.
Содержание самой ячейки формируется с помощью функции сцепить, которая, пробегая по диапазону, собирает из его ячеек слова и пробелы (пустые ячейки). Лишние пробелы убираются функцией сжпроблел. Проблема в том, что в зависимости от определенных условий содержание диапазона меняется, и соответственно пробелы (т.е. пустые ячейки в диапазоне) могут появляться в разных местах, иногда по нескольку подряд, поэтому тупо добавление запятых в СЦЕПИТЬ приводит к результату, когда в итоговой ячейке стоит несколько запятых подряд, что совсем не устраивает.
Надеюсь, не очень путано...
Если у кого есть какие мысли на этот счет, подскажите пожалуйста.

ALEX20062021
Имхо смотреть надо в сторону макроса или пользовательской функции. С этим нужно обратиться в топик по Excel VBA. Ссылка в шапке.
Можно также подождать, что скажут другие участники топика.

ALEX20062021
Покажите (напечатайте) здесь пример текста который получается и который бы хотелось получить.

kimtan
Что делаю:

Что получаю:

Что хотелось бы получить:

ALEX20062021
Можно, но хлопотно, например для столбика из 7 значений будет выглядеть так:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(СЦЕПИТЬ(ПОДСТАВИТЬ(D1;" ";"@")&" ";ПОДСТАВИТЬ(D2;" ";"@")&" ";ПОДСТАВИТЬ(D3;" ";"@")&" ";ПОДСТАВИТЬ(D4;" ";"@")&" ";ПОДСТАВИТЬ(D5;" ";"@")&" ";ПОДСТАВИТЬ(D6;" ";"@")&" ";ПОДСТАВИТЬ(D7;" ";"@")&"."));" ";", ");"@";" ")
Может все-таки VBA?

kimtan
Спасибо.
Интересный вариант, только не понятно, как он работает.
Если не сложно, опишите в двух словах. Как я понимаю, внутренние "подставить", добаляют к тексту @ (кстати, что это?). Потом второе "подставить" вместо @ ставит запятые, а третье - " ".

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

Окончательная формула построена на основе следующей:

=ЕСЛИ(H1="";"";H1&", ")&ЕСЛИ(H2="";"";H2&", ")&ЕСЛИ(H3="";"";H3&", ")&ЕСЛИ(H4="";"";H4&", ")&ЕСЛИ(H5="";"";H5&", ")&ЕСЛИ(H6="";"";H6&", ")&ЕСЛИ(H7="";"";H7)

Борьба за правильное окончание приводит к её сильному разрастанию (((

=ЕСЛИ(H7="";ЛЕВСИМВ(ЕСЛИ(H1="";"";H1&", ")&ЕСЛИ(H2="";"";H2&", ")&ЕСЛИ(H3="";"";H3&", ")&ЕСЛИ(H4="";"";H4&", ")&ЕСЛИ(H5="";"";H5&", ")&ЕСЛИ(H6="";"";H6&", ")&ЕСЛИ(H7="";"";H7);ДЛСТР(ЕСЛИ(H1="";"";H1&", ")&ЕСЛИ(H2="";"";H2&", ")&ЕСЛИ(H3="";"";H3&", ")&ЕСЛИ(H4="";"";H4&", ")&ЕСЛИ(H5="";"";H5&", ")&ЕСЛИ(H6="";"";H6&", ")&ЕСЛИ(H7="";"";H7))-2);ЕСЛИ(H1="";"";H1&", ")&ЕСЛИ(H2="";"";H2&", ")&ЕСЛИ(H3="";"";H3&", ")&ЕСЛИ(H4="";"";H4&", ")&ЕСЛИ(H5="";"";H5&", ")&ЕСЛИ(H6="";"";H6&", ")&ЕСЛИ(H7="";"";H7))&"."

Предлагаю всё-таки использовать VBA: Пользовательская функция

Да немного не учел, каюсь.
Вот так будет работать при любом раскладе:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(СЦЕПИТЬ(ПОДСТАВИТЬ(D1;" ";"@")&" ";ПОДСТАВИТЬ(D2;" ";"@")&" ";ПОДСТАВИТЬ(D3;" ";"@")&" ";ПОДСТАВИТЬ(D4;" ";"@")&" ";ПОДСТАВИТЬ(D5;" ";"@")&" ";ПОДСТАВИТЬ(D6;" ";"@")&" ";ПОДСТАВИТЬ(D7;" ";"@")));" ";", ");"@";" ")&"."
Символ "@" или другой редкий символ, например "|" используется в формуле для временной замены пробела, дабы исключить пробелы внутри фразы из расчета. Например Значение 1 заменяется на Значение@1, последняя Подставить() снова заменяет эти символы на пробелы.

vlth
kimtan
Спасибо огромное, очень помогли!

vlth
Прошел по ссылке, там написано, что файл удален с сервиса.
Что там было-то?
Если функция, реализованная в VBA, прошу залить еще куда-нибудь.
Буду с нее начинать знакомство с этим чудом.

ALEX20062021

Да, действительно, ссылка была неверной, что странно...
Теперь линк правильный.

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

vlth
Теперь линк рабочий. Все получилось.
Смотрю теперь и тихо радуюсь.
В данном случае пользовательская функция действительно более подходящее решение.
Большое спасибо за то, что заморочились с моей проблемкой.

Ребята, подскажите. Очнь глючит мастер диаграм в excel 2007. Недавно поставил, до этого был 2003. Там было все ок. А теперь, при выводе графика по точкам (их количество около 20000 < 32000) программа жутко тормозит и вываливается с ошибкой. Office interprise оригинальный MSDN. Может это известный глюк. Сервис пак не установился, выскакивает ошибка "Обновление не может быть установлено". Перечитал все статьи мелкомягких по этому поводу. Не помогло.
Придется переустанавливать office 2003

Добрый день!

Возникла проблема. Имеется Excel 2003 SP3, принтер HP2015. При печати, excel путает ориентацию листа, т.е. книжный печатает альбомным, а альбомный соответственно книжным. Проблема только в Excel. Word печатает отлично. Проблема не в принтере - подключал принтер к другому компьютеру, все печатает отлично.

Что самое интересное, при печати на такой же принтер, но по сети - печатает все отлично.

Куда копать?

Scream_Err
Попробуй скопировать все содержимое в новый файл и правильно выставить там формат страницы.

Насколько я помню принтер HP2015 у него есть как минимум 2 типа драйверов. PCL 5e и PS. Желательно убедиться в том, что на этих принтерах установлены одинаковые драйвера.

Цитата:

Попробуй скопировать все содержимое в новый файл и правильно выставить там формат страницы.

Не зависит от файла. Чистый лист открываешь, текст вбиваешь, поля выставляешь, отправляешь на печать. А он все наоборот печатает.


Цитата:

Насколько я помню принтер HP2015 у него есть как минимум 2 типа драйверов. PCL 5e и PS. Желательно убедиться в том, что на этих принтерах установлены одинаковые драйвера.

Стоит PCL5e на всех принтерах 2015 (у меня 7). Проблем не было.

Еще мысли есть?

Здравствуйте! У меня такая ситуация: есть колонка с ценами, и рядом есть колонка, где эти цены должны отображаться на 30% больше. Подскажите плз, как это сделать?

One_and_only
Если просто, то например:
Если в ячейке А1 цена, то формула в ячейке В1 "=А1*1,3", можно конечно сделать изящнее, если нужно

Scream_Err
Цитата:

Еще мысли есть?

Есть. Создай нового пользователя и попробуй воспроизвести эту проблему под его учетной записью.

PSVRF
Спасибо большое!) не так-то всё и сложно)

Господа,
Есть такая проблема: на веб-сайте выложены данные по погоде (температуры по дням на протяжении месяца), необходимо их обработать в Excel. Копирую таблицу - вставляю, а данные по температурам Excel воспринимает как даты, т.е. столбцы с данными типа 14.1, 10.7 и т.д. при вставке в лист получают вид типа 14.Янв, 10.Июл и т.п. Как с это побороть и заставить сабж корректно вставлять данные, а не воспринимать их как даты?
Использую Excel 2007.

Если вставить как текст, а предвариетльно ячейки сделать текстового формата - проблем быть не должно.
А мб ещё и в автоконвертации покапаться можно.
Вообще это известная проблема.

Понадобилась функция "Сумма прописью".
Попробовал то, что предложенно в шапке ... Ну, не понравилось ... например тем, что ... сумма копеек тоже прописывается словами. Требования к прописыванию суммы словами позволяют писать копейки цифрами.

Нашел, на мой взгляд, лучшее решение задачи тут.

Возможно, в шапку стоит добавить и этот вариант?!
Он действительно удобный! (:

Перезалил файл со вложенной инструкцией сюда.

KChernov
Если предварительно сделать ячейки числового формата - то получается как раз то, что надо.
Спасибо за идею, вроде бы просто, но сам не догадался. Конечно муторно довольно, но все же - решение проблемы.

Привет всем! помогите в екселе. Когда делаю суму результата выводит на 1 копейку больше чем надо, при этом в самих ячейках считает правильно

спасибо за инфу. Давно искал

sasha77
Сделай или округление до сотых всех чисел, участвующих в сумме, либо увеличь разрядность отображения цифр. Скорее всего у тебя где-то есть числа вида 3,784. А показывается 3,78. Несколько таких чисел могут дать лишнюю копейку.
Либо выкладывай пример. (только цифры).

Приветик всем! Ну а если числовой формат везде стоит и после запятой два знака тоже, а разрядность не катит, может что-то еще можна сделать посоветуйте.

sasha77
Клавиша F1, в поле для поиска введите "задание точности округления", и тогда вопросы отпадут, там в статье справки еще есть ссылка на функцию ОКРУГЛ (в конце статьи).

А если 1 копейки не хватает, тоже самое?