» Excel FAQ (часть 5)

MisHel64
Спасибо большое.
Точно так. Я уже подумал не получается и использовал еще одну ячейку и оттуда со ссылкой на предыдущее ячейку с понижением на 1 месяц (выкрутился).
А как у Вас это картинка!
Даже не соображу как тут считывать - понять почему так получается?

Там ошибка, проявится в января. Правильно:
=ДАТА(ГОД(сегодня()-9);МЕСЯЦ(сегодня()-9);9)
Все тут просто. от сегодня отнимаем 9, получаем дату на 9 дней назад.
Например:
08/04/16 - 9 получится 30/03/16.
09/04/16 - 9 получится 31/03/16.
10/04/16 - 9 получится 01/04/16.
Так и получили нужный месяц и нужный год, которые и извлекаем.
А потом преобразовываем обратно в дату формата экселя.

Да, теперь и понятнее стало! Спасибо!!!

И к стати, сообщаю для чего такая формула подойдет.
Молодых особо наверно не колышит. А вот пенсионерам, у кого в семьи доход небольшой, инвалиды и пр.
По закону, за просрочку оплаты коммунальных услуг начисляют пени. Люди боясь от низ платят заранее.
Можете использовать такую формулу. До сих пор требование было - начиная со следующего от установленного дня оплаты. А день оплаты - до 10 числа! Вот и 9 ое число. А размер пени 1/300 от ставки рефинансирования устанавливаемая ЦБ РФ. С 1.01.16г. Ставку рефинансирования приравняли учетной ставки ЦБ. Если сатвка рефинансирования с 14 сентября 2012г. была в размере 8,25 % годовых. А вот ключевая ставка последние несколько месяцев в размере 11% годовых. Тем самым, видите как выросли пени?
Но, есть и хорошая новость. С 1.01.16 г. пени могут начислять начиная с 31 ого дня от того же установленного дня оплаты. Поэтому прибавляете еще 30 дней у той дате и получаете последний день оплаты, когда еще пени не имеют права начислять. Вот из-за этого прибавления 30 ти и эта дата теперь будет "плавать".
В апреле эта дата будет 8 числа.

Извините, за отступление от темы.

Подскажите, можно закрепить так области и как это сделать?

закреплена закреплена закреплена закреплена

d0r0fey имхо никак, Freeze (колонну и верхнюю строку) и Split (последнюю строку) в комбинации
не работают на Excel 2013, так что если это строка Total то выноси её на верх и
Freeze нужные первые колонны/строки

Есть ли какой нибудь автоматический способ для Excel 2010, чтобы сделать следующее. В столбце А идут цифры, а в столбец Б автоматом проставляются те же цифры, но они должны быть прописью. Спасибо.

tdmods
стандартной функции нет (если память не изменяет), но есть разные - по способу исполнения - решения... Формулами, или программой/макросом на VBA..
например, вот вариант, или так..
или выбрать подходящий

Подскажите пожалуйста, возможно ли с помощью формулы преобразовать ряд чисел вида 12345678900 в ряд вида 123-456-789-00 ?

papanja_084
а нужно, чтобы они так выглядели? тогда - можно задать пользовательский формат представления числа (формат ячейки/все форматы)
что-то типа:
Код: [<=99999999999]###-###-###-##;Основной

ALeXkRU 123
Спасибо!

Мне помогли выделять субботние (=ДЕНЬНЕД(AA72;2)=6) и воскресные (=ДЕНЬНЕД(AA72;2)=7) дни с условным форматированием. Только вот мешает, на тех ячейках, где условное форматирование заданы, как только удаляю даты закрашиваются в цвета субботы. Можно как то решить?
Оффис 2003.

Aktaf

Цитата:

как только удаляю даты закрашиваются в цвета субботы. Можно как то решить?

а надо-то что? чтобы в другой цвет красило? и желательно видеть, как оно сделано: вообще-то не должно красить не нужные ячейки:
http://jpegshare.net/thumbs/6c/95/6c9557d2254a0e20d04914857524f557.jpg

ALeXkRU 123
Судя по картинке не до конца смог объяснить. По Вашей картинке, в столбцах А, B, где вписаны даты, удаляем их. Освободившиеся ячейки окрашиваются в желтый цвет - цвет субботы. Каждый раз приходиться туда пере копировать обычные пустые ячейки. Таблица большая, ежедневно несколько раз вноситься изменения и это уходит много времени и сил.

Aktaf
всё-равно не объяснили... Не имея в руках самого файла, можно только так посоветовать: в моём примере видно, что можно создать несколько правил, действующих для одной и той же ячейки... Соответственно, нужно добавить ещё правило, в котором указать, что делать, когда не сб и вс... Самое простое

Aktaf Оффис 2003 А чуть по подробней, как ты смену цвета делаешь?
И цвет чего меняется, фона или числа?

Вообще то и так вроде подробно. Кроме того, цвет меняю фона. А настройки привел ALeXkRU 123 уже в картинке. В 2003 ем чуть отличается, только не существенно.
Если не в курсе, это нужно изучать условное форматирование. Встаньте на ячейку, правая кнопка и смотреть в меню Формат и там Условное форматирование. И можно задать три варианта формата, и в каждом из них можно задать по значению или по форуле. И уже в каждом варианте с правой стороне под кнопкой формат - определить параметры шрифтов, границы и вид. Вроде уже можно понять. Остается прямо сейчас поковыряться и закрепить.
ALeXkRU 123

Цитата:

Соответственно, нужно добавить ещё правило, в котором указать, что делать, когда не сб и вс... Самое простое

Так это верно. Но я пытался и не получается. Вот с цифрами там получилось, даже первоначальный вид ячеек в обычные линии ячеек восстановились.
А с датами никак. Видимо не правильно задаю, потому и обратился. Говорите не понятно, но, сами правильно указали. Осталось написать как именно задать третий вариант для "пустых" ячеек, в который формат и условный формат остался, но нет формул.
У Вас это можно легко пробовать.
В ячейке, с датами и условным форматирование удалить дату и посмотреть. Если не покраситься, а условное форматирование сохранилось значить у Вас нормально.
Можно бы кусочек такой таблички выложит. Правда, при открытии на 2003 ем может и потеряться. Если что, в начале открою на 2010 ым.
Вот от меня пример https://cloud.mail.ru/public/5rq5/A8hELYqdK ТО, что я пытался третье условие создать там нет. Видимо из-за того, что я задавал обычные параметры, поэтому как излишние не сохраняется?

Aktaf Слова... Слова... Ты формулу или формат ячейки кинь.

Aktaf
Как вариант, макрос для удаления содержимого выделенных ячеек вместе с форматированием:

Код: Sub ClearAll()
Selection.Clear
End Sub

ruzmaz
Что-то слишком сложно. а вот MisHel64 предлагал совсем просто решить.
Почему-то замолчал или пока нет времени.

Aktaf Да я тут. Просто не вижу вопроса. Что-то пропустил?
[more=На счет коммуналки] За март оплатить нужно до 25/04, а не 10/04. Это сделано для того, что бы люди успели получить зарплату за прошлый месяц и оплатить с нее услуги за прошлый месяц. Оплачивать заранее, до выставления счета не стоит. Если у вас не так, можете смело обращаться в общество защиты прав потребителей так как ваша управляющая компания нарушает закон. Если уже платили проценты за просрочку оплаты после 09 но до 25 смело можете обратится в прокуратуру по УК РФ. Я лично оплачиваю 16-20 числа следующего месяца, и никаких пеней не видел. За исключением пени за просрочку платежа за телевизионную антенну и радио, но я и не собираюсь оплачивать эти услуги, так как они оказываются с нарушением действующего законодательства.
Если денег на оплату нет, то можно обращаться в управляющую компанию и просить о реструктуризации долга. Они обязаны на это пойти. Проценты за просрочку в данном случае взиматься не будут.[/more].

Aktaf
Можно попроще, но медленнее:
"Правка" -> "Очистить" -> "Все"

MisHel64 извините, не туда щелкнул.
Хотел ALeXkRU 123

Цитата:

можно только так посоветовать: в моём примере видно, что можно создать несколько правил, действующих для одной и той же ячейки... Соответственно, нужно добавить ещё правило, в котором указать, что делать, когда не сб и вс... Самое простое

Я тоже думал посоветуют нормальный вариант или скажут нельзя иначе и все.
Почему то так затянулось.
ruzmaz чем так пере мудривать, легче перетаскивать любую ячейку, где не задано условное форматирование и удалить содержимое (если есть). Это всего два движения.
Мне бы хотелось, чтобы было автоматом. Удалил и не закрашивается. Если второго нельзя получать, то первый вариант (хуже) всегда есть. Для чего еще больше усложнять или замедлять?

Aktaf
Замените

Код: =ДЕНЬНЕД(AA72;2)=6

ruzmaz
Спасибо большое.
Вот, теперь действительно ОК! А как эту формулу "прочесть", понять?

Есть файл http://my-files.ru/7dqosu , прошу подсказать можно ли реализовать поиск по не скольким строкам, к примеру в первом столбце хочу найти все варианты которые были после повторения цифр 2 1 2 .

Aktaf

Цитата:

как эту формулу "прочесть", понять?

как-то так: если ячейка AA72 не пустая, и день недели = 6 (сб), то применять форматирование

Aktaf
Функция И() возвращает результат логического "И" значений двух выражений: AA72<>"" и ДЕНЬНЕД(AA72;2)=6. Выражение ДЕНЬНЕД(AA72;2)=6 возвращает ИСТИНА, только если ДЕНЬНЕД() возвращает 6 (дата в ячейке соответствует субботе; здесь "=" - это оператор логического сравнения "РАВНО"). Выражение AA72<>"" возвращает ИСТИНА, только если ячейка не пуста (здесь "<>" - это оператор логического сравнения "НЕ РАВНО"). В итоге, И() вернет ИСТИНА (и в этом случае будет применено форматирование) только когда ячейка не пуста и дата в ячейке соответствует субботе.
После удаления содержимого ячейки ексель пересчитывает значения по формулам условного форматирования для данного диапазона ячеек. Предположу, что функция ДЕНЬНЕД() преобразует значение содержимого очищенной ячейки, равное "" (пустая строка), в эквивалентное числовое значение, равное 0, а потом в эквивалентную дату 00.01.1900. Дата нереальная, но, поскольку 01.01.1900 - это воскресенье, то для 00.01.1900 возвращается суббота.

приветствую
необходимо свести в одну таблицу данные из трёх таблиц вида
код данные1
100 д1
200 д2
150 д3

код данные2
100 с1
150 с2
400 с3

код данные3
200 к1
700 к2
150 к3

чтоб в новой таблице получилось нечто похожее на

код данные1 данные2 данные3
100 д1 с1
150 д3 с2 к3
200 д2 к1
400 с3
700 к2

NEW_MAKC
Подобное можно сделать с помощью консолидации (вкладка "Данные").

Aktaf Чуть правильнее =И(Ч(AA72)>0;ДЕНЬНЕД(AA72;2)=6)
Срабатывает если оба условия верны.
Условие AA72<>"" сработает всегда, если ячейка не пустая, а Ч(AA72)>0 если только в ячейки число (а дата, это то же число).