» Excel FAQ (часть 5)

приветствую
задача такая

в ячейке 1:1 число
в ячейке 1:2 формула =ЕСЛИ(RC[-1]<3340;RC[-1]+500;ЕСЛИ(И(RC[-1]>=3340;RC[-1]<=5000);RC[-1]*1,15;ЕСЛИ(И(RC[-1]>5000);RC[-1]*1,1)))
в ячейке 1:3 формула =ОКРВВЕРХ(RC[-1];10)

как свести в одну формулу формулы из ячеек 1:2 и 1:3

спасибо

NEW_MAKC
Извиняюсь, сам не люблю, когда кто-то пишет свой вопрос, пока ответа нет на предыдущий. В это йтепе вроде это не мешает. Да и вопрос мой тоже подобный Вашему.
У меня такая формула:
=ЕСЛИ((M241=13); ДАТА(ГОД(I241);МЕСЯЦ(I241)+M241-1;ДЕНЬ(I241)+2); ДАТА(ГОД(I241);МЕСЯЦ(I241)+M241;ДЕНЬ(I241)))

Все вроде устраивает. Но, с февралем появляется желание добавить еще условие.
Т.е. при получающихся датах 29, 30, 31, когда в феврале 28 и датах 30, 31, когда в феврале 29, в том числе и 1.03.хх - показывалась дата 1.03.хх.

Что-то кризис что ли мешает, умные люди перестали посещать? Я думал тут же ответят.
Уже тему закопали. Я не думаю, что это не по силам сделать. Наверно нужно яснее изложить? Нужно изменить формулу таких ячеек:
=ЕСЛИ((M241=13); ДАТА(ГОД(I241);МЕСЯЦ(I241)+M241-1;ДЕНЬ(I241)+2); ДАТА(ГОД(I241);МЕСЯЦ(I241)+M241;ДЕНЬ(I241)))

Все вроде устраивает. Но, с февралем появляется желание добавить еще условие.
Т.е. при получающихся датах 29, 30, 31, когда в феврале 28 и датах 30, 31, когда в феврале 29, в том числе и 1.03.хх - показывалась дата 1.03.хх.
В представленной формуле, в ячейках как I241 - определенная дата например, 29.01.2014,
в ячейках как M241 - целые числа, обозначающие какой месяц с даты 29.01.2014. И в третьих ячейках, где пишутся такая формула - должна показываться дата, которая получается - 29.05.2014, если в M241 будет 4 ре. Имеется если, чтобы когда будет 13, дата не получалась аналогичной, но следующего месяца, а только была на 2 дня больше чем при 12 ти.

Вот пока расписывал, сам же и засомневался, может действительно вводить второе желаемое если расписать не получается? Если так, я бы хотел, чтобы это было не догадка, а выказано, указано знающим форумчаниным! И так.
Второе если,
- это получаемое дата, совпадало бы с плавающими "датами февраля" (моя условное название), то показывалась бы только 1 марта, а не больше. Вдруг это и то что в самом начале изложено, не совсем понятно, попробую второй вриант:
- когда дата в I241 при истечении определенных месяцев дают не следующую дату после отсутствующего в феврале дней, то всегда нужно получать первого марта, а не первого, второго и третьего, потому что, не все они будут следующем днем отсутствующего числа февраля.

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

RemikR

Цитата:

как можно в формуле объединить текст

использовать функцию объединения текста:

ALeXkRU спасибо, то что надо.
Но теперь другая проблема образовалась.
Как заставить ячейку отображать буквально то что набрано с клавиатуры ни как не форматируя введённые символы?
Например есть ячейка в которой вводится с клавиатуры "15.02.2015". Ячейка автоматом принимает формат "дата" и отображаемое значение соответствует введённому. Но если поставить любой другой формат (в том числе и "общий", "текстовый") выводится какой то набор цифр (например для "15.02.2015" - "42050"). И, как результат, если я вставляю эту ячейку в формулу постом выше (например: =СЦЕПИТЬ("Стоимость на ";U3)), то на выходе получаю: "Стоимость на 42050". И ни какой формат ячейки уже не помогает.
Как побороть?

RemikR
нужно сначала для ячейки задать формат "текстовый", а потом уже вводить туда значение с датой. Тогда введённая дата не будет заменяться, и всё будет работать правильно..
Текстовый формат в ячейке никак не обрабатывается, используется точно в том виде, как введено

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

ALeXkRU
спасибо.
Бред какой то. Если сначала выставить формат ячейки "текстовый" - введённый текст не обрабатывается. А если сначала ввести текст, а потом уже менять формат ячейки, то уже ничего не помогает! Ни "текстовый", ни какой другой. Текст всё равно обрабатывается. Никогда бы не догадался.

RemikR
почему же бред? Это нормально: после ввода даты, она автоматом заменяется (что-то вроде автозамены), поэтому изменение формата только отображает значение в ячейке - то самое "42050".. Превратиться обратно в дату оно не может: теперь это текстовая ячейка, значит - не обрабатывается, показывает то, что записано.

Используй апостроф в начале...

ALeXkRU
ну может вы и правы.... Но написано то 15.02.2015 а не 42050. Ну да ладно, проехали .....
С апострофом очень удобно, спасибо.
И ещё вопрос. Как заставить часть выводимого формулой быть другого цвета?
Например формула: =СЦЕПИТЬ("Стоимость на ";U3)
Получить на выходе: "Стоимость на 15.02.2015", или "Стоимость на 15.02.2015".

RemikR
=C5&"по состоянию..."

Цитата:

в ячейке 1:1 число
в ячейке 1:2 формула =ЕСЛИ(RC[-1]<3340;RC[-1]+500;ЕСЛИ(И(RC[-1]>=3340;RC[-1]<=5000);RC[-1]*1,15;ЕСЛИ(И(RC[-1]>5000);RC[-1]*1,1)))
в ячейке 1:3 формула =ОКРВВЕРХ(RC[-1];10)

как свести в одну формулу формулы из ячеек 1:2 и 1:3

так никто и не подскажет?

StaS_1979

Цитата:

=C5&"по состоянию..."

не понял к чему это

RemikR
к
Цитата:

То есть необходимо в ячейке выводить какой то стандартный текст и значение какой то ячейки.

RemikR

Цитата:

Цитата: =C5&"по состоянию..."

не понял к чему это

StaS_1979
спасибо, но с этим уже разобрались. Теперь бы с [more=цветом]И ещё вопрос. Как заставить часть выводимого формулой быть другого цвета?
Например формула: =СЦЕПИТЬ("Стоимость на ";U3)
Получить на выходе: "Стоимость на 15.02.2015", или "Стоимость на 15.02.2015".[/more] в тексте (формуле) разобраться ....

Добавлено:
ALeXkRU да, спасибо. Я в справке по формуле прочитал про знак "&"

Цитата:

Но написано то 15.02.2015 а не 42050

первое - это только представление даты в формате dd.mm.yyyy, а второе - это стандартное представление даты в Excel, в виде числа дней, прошедших с 1 января 1900 года (в таком виде даты хранятся, и вычисляются в формулах)

Добавлено:
RemikR
про цвет - если именно часть текста, только через макросы/VBA Формулами это сделать нельзя.
Вот пара примеров: раз (см. и комментарии), два

ALeXkRU
но ввожу то я именно 15.02.2015 а не 42050. Было бы логично при выборе формата ячейки "текстовый" отображать символы введённые с клавиатуры.
Я понимаю что если за ранее не задан формат ячейки "текстовый" введённый текст автоматически форматируется в число дней прошедших с 1 января 1900 года и уже это число хранится в ячейке. (Просто было бы логично запоминать первоначально введённый текст ).
Но тогда возникает другой вопрос. Я не хочу что бы введённый текст автоматически форматировался в ячейке. Как этого добиться? Или как сделать так что бы формат ячеек по умолчанию был "текстовый" а не "общий"?

Цитата:

Или как сделать так что бы формат ячеек по умолчанию был "текстовый" а не "общий"?

Если выделить сколько диапазон, какой Вам нужен и формат ячеек, установить текстовый, не получиться желаемое?

Aktaf
так то наверно да. Но имелось введу иметь это по умолчанию.

RemikR

Цитата:

но ввожу то я именно 15.02.2015 а не 42050. Было бы логично при выборе формата ячейки "текстовый" отображать символы введённые с клавиатуры

Нет. Не логично. По умолчанию, ячейки имеют формат "общий". Excel, при вводе значений в эти ячейки, их обрабатывает, пытаясь угадать, что введено (по формату вводимого), и использует автозамену на правильное значение (по умолчанию). Даты, время, числа, деньги - правятся. И после этого не существует того, что ты ввёл - оно преобразовано в нужный вид, в котором и хранится.
При использовании формул используется ещё одна фича: называется приведение типов.. Если прибавляешь к числу значение ячейки, оно считается числом. Если сцепляешь строки, считается строкой.. Прибавляешь к дате - будет датой (кстати, в этом случае 42050 будет таки правильной датой и будет правильно вычисляться)
А вот, если ты после этого просто меняешь формат ячейки, её содержание уже не изменится, меняется только представление/отображение этого значения: то, как ты его видишь (в виде даты, времени, денежной суммы..)


Цитата:

было бы логично запоминать первоначально введённый текст

нее.. ты-то ввёл дату, оно и запомнило дату.. А дата - это именно 42050... В конце-концов, Excel - электронная таблица (для вычислений предназначен), а не текстовый редактор. (Согласись, вычислять даты в таком виде гораздо удобнее, чем, скажем, из 15.02.2015 вычесть 6 мар 12)
Чтобы указать, что вводится именно текст, есть решение - апостроф перед строкой. Или предварительное указание, что будет вводиться текст - изменение формата ячейки.


Цитата:

как сделать так что бы формат ячеек по умолчанию был "текстовый" а не "общий"?

Задать формат ячеек "текстовый". Тогда - после этого - он будет текстовый..
Тут главное - помнить об этом, а то могут сюрпризы встретиться при вычислениях формул Excel далеко не всегда сможет правильно привести тип текста к другому значению, нужному в отдельных формулах..
Всё-таки для работы с текстами больше Word подходит, Excel - для вычислений

Добавлено:
RemikR

Цитата:

имелось введу иметь это по умолчанию

по умолчанию при создании нового файла? Без проблем: создай и сохрани шаблон файла с нужными настройками.

ALeXkRU
понятно. Спасибо

Добавлено:
Господа. Вот файл. Почему в ячейке С5 при вводе элементарной формулы "=G1" так и выводится - "=G1", а не так как во всех остальных ячейках (значение ячейки G1)?

RemikR
Не понял, что вы для этой ячейки прилепили.
Сделал для неё очистить всё, заново ввёл формулу, всё отображается верно.

RemikR

Цитата:

Почему в ячейке С5 при вводе элементарной формулы "=G1" так и выводится - "=G1"

Потому, что там не формула введена, а текст А текст показыватся точно так, как вводится...
И ведь я предупреждал: с текстовым форматом поаккуратнее надо
Цитата:

главное - помнить об этом, а то могут сюрпризы встретиться при вычислениях формул: Excel далеко не всегда сможет правильно привести тип текста к другому значению

ALeXkRU

Цитата:

Потому, что там не формула введена, а текст

так и есть. Но теперь возник вопрос: как же это получилось? Ведь я не задавал явно формат для ячейки С5 как и для других ячеек, а вывод отличается. То есть как повторить ситуацию в соседних ячейках?
Оказалось очень просто. Если удалить содержимое в соседних ячейках и заново ввести формулу "=G1", то уже тоже выводится просто "=G1".
То есть при первоначальном вводе формулы ячейка С5 имела формат "общий". После ввода формулы и нажатия enter ячейка С5 вывела значение ячейки G1 и приняла её формат, то есть "текстовый". Потом я видно удалял содержимое ячейки С5 и повторно вводил формулу. А все последующие удаления содержимого (кнопка Del) не меняют формат ячейки, она уже остаётся "текстовой". Поэтому при повторном вводе формулы уже получал на выходе просто текст "=G1".
Что бы сбросить ячейку в исходное состояние необходимо выполнить Правка > Очистить > Всё. (спасибо arturrc).

ALeXkRU, RemikR
Извините, вопрос в тему. А можно ли как-то определить по готовому файлу последовательность действий (первым действием форматирование ячейки или ввод данных). В выложенном примере у всех ячеек с данными текстовый формат.
И второй вопрос. Очистка форматов для ячейки не спасает ситуацию (Excel 2007). Помогает только очистить всё. Почему?
RemikR
Полтергейст какой-то. Вчера смотрел ваш файл - очистка форматов для ячейки не срабатывала, помогало только очистить всё. Сегодня всё работает.

arturrc

Цитата:

А можно ли как-то определить по готовому файлу последовательность действий

я думаю вряд ли.
Тут главное знать как вернуть ячейку в первоначальное состояние по умолчанию (Правка > Очистить > Всё) если что то выводится не так как надо

Цитата:

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

Потому что они согласно формуле ("=G1") приняли её (G1) формат.

Цитата:

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

Спасает. Excel 2003, но не думаю что это важно.
Сначала очищаете формат (Правка - Очистить - Форматы), затем вводите заново формулу (например "=G1") и жмёте enter. Ячейка выводит значение G1 и принимает её формат (в данном случае "текстовый"). И так по кругу. Перед повторным вводом необходимо задать формат "общий" или сделать очистку всё\форматы (Правка - Очистить - Всё\Форматы). Иначе при повторном вводе формула будет выводиться как текст "=G1".

Добавлено:
А, вообще конечно, для меня стало главным открытие что задание формата ячейки до ввода и после - две большие разницы.
Спасибо ALeXkRU

arturrc

Цитата:

Очистка форматов для ячейки не спасает ситуацию (Excel 2007). Помогает только очистить всё. Почему?

хмм.. не очень понятно, что требуется?
Если по файлу-примеру, нужно чтобы заработала формула "=G1", то достаточно:
- изменить формат ячейки на "общий" с "текстовый"
- обновить формулу (или двойной клик по ячейке и Enter, или клик по строке ввода формул и Enter), т.е. ввести формулу снова

Вроде уже во всю обсудили, мой предыдущий закопали, видимо не "по зубам".
Новый вопрос, задачка:
Люди работают с огромными таблицами Ексел, хотелось как то уменьшить нагрузку.
Таблицы состоят из множеств групп, которые по разному дублируют другие части.
Порой есть необходимость сослаться на ячейки аналогичных строк, но разные строки по разному "отклоняются" друг от друга. Нельзя ли это как то автоматизировать.
Может на примерах будет легче. Примеру так:
=a1    =b1    =f1    =r1 =h1     1
=a15    =b15    =f15    =r15 =h15     15
=a7    =b7    =f7    =r7 =h7     7
=a95    =b95    =f95    =r95 =h95     95
Ниже или в стороне подобные строки, возможны с другими столбиками и т.д.
как то нельзя ли указать зависимость например от последнего столбика?
Если столбика нет, его можно и создать, не важно где угодно, спереди, как порядковый номер и пр.
Можно как то написать a1 a15 a7 a95 - это сами ячейки, в них естественно какие то значения, и куда эти обозначения будет выводиться их копии, с которыми в этой части таблицы свои "метаморфозы" будут происходит. Но, кч ои это возможно, уже изменять нужно будет только последний столбик и это строчки будут соответственно изменяться, то что нужно. Чтобы не вписывать в ручную по всем строчкам и по всей таблице.
Т.е. при изменении цифр последнего столбца, сслки бы изменились на ячейки соответствующих строк.

Aktaf
Один раз пропишите все формулы для нужных ячеек =имя ячейки, где вы будете вводить данные. Расположить вводимые ячейки вы можете хоть на отдельном листе. Защитите ячейки с формулами от изменений паролем. И всё.
Понятно, что если таких ячеек очень много, то ломает делать такую работу. Но, сделать-то надо один раз, а потом только наслаждаться. Как-то так, если я правильно всё понял.
Для убыстрения процесса могу только посоветовать при вводе формулы использовать кнопку F4 (нажимая её несколько раз увидите как меняется формула) и сохранить формулу с двумя знаками $$. Потом можно будет просто скопировать формулу в ячейки, которые привязаны к одному значению.