Б.Л.

Олег, добрый день.

Много делаю сам и многим даю советы, но почитав и посмотрев советы и примеры на вашем сайте, понял, что мне еще учиться и учиться… Восхищен. А пишу я еще вам и потому, что воткнулся в одну проблемку, и никак не могу найти решение. Подскажите, если возможно путь ее решения.

Есть дата оплаты по договору. И есть дата, по которой реально заплатили. Суть в том, что если дата по которой реально заплатили больше чем дата по договору, нужно считать пени. Пени считаются исходя из 1/180 ставки рефинансирования ЦБ за каждый день просрочки платежа. Все бы ничего, если бы ставка эта не менялась в диапазоне дат между датой по договору и датой реальной оплаты. Как заставить Ексель посчитать мне эти пени, исходя из разного количества дней и размера ставки рефинансирования? Вот примерные цифры:

дата по договору 16.05.10

дата оплаты 25.05.10

ставка реф на 15.05.10 = 8,5%

ставка реф с 17.05.10 = 8,0%

ставка реф с 21.05.10 по наст. время 21.05.10 = 7,75%

так вот пени считаются как за 1 день (за 16.05.10) по ставке 8,5%, за 4 дня (с 17.05.10 по 20.05.10) по ставке 8,0%, и оставшиеся 5 дней (с 21.05.10 по 25.05.10) по ставке 7,75%

а как все это сделать, чтобы эксель сам раскладывал на число дней и брал применительно к этому числу дней нужную ставку, все перемножал и выводил готовый результат?

О.В.

Добрый день!

Во вложении решение Вашей задачки. Без примерного файла с Вашей стороны я нафантазировал там… :-) Ну, надеюсь, разберетесь.

Там я чего только не использовал. И ВПР место нашлось, и СУММЕСЛИ. Но в итоге симпатишно получилось.

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

Удачи!

d*******@**********.ru

Олег, доброго времени суток!

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

Данные вводятся в две соседние ячейки в формате ДД.ММ.ГГГГ чч:мм

Результат необходимо отобразить в текстовом формате. Например: 36 ч. 37 мин. (начальник не воспринимает формат отображения 36:37 )

О.В.

Можно применить к этой ячейке следующий числовой формат: «[ч] “ч. “мм” мин.”». Смотрите пример во вложении.

А вообще с форматами надо поступать так:

Тыкаете правой кнопкой мышки по ячейке и выполняете ФОРМАТ ЯЧЕЙКИ — ЧИСЛО

там выбираете наиболее близкий к нужному формат и экспериментируете с ним. Например, я выбрал в разделе «Время» строчку 37:30:55. Затем тут же выбираем категорию «(все форматы)» и получаем строчку «[ч]:мм:сс», которую преобразуем в нужную, ориентируясь на то, как будет оформляться число.

Пример про время

a**o@gmail.com

Здравствуйте, Excel.

В Excel есть колонки с данными (цифры) A и B на странице 1

Как сделать, чтобы при вводе данных , равных по значению какому-либо из столбика А в клетку С1 на странице 2 в клетке С2 отображались данные из столбика B , напротив этого значения в столбике А,

Еще было бы идеально сделать поправку на вводимые данные следующие:

Значения дробные, например есть 4, 4.2, 4.4 Сделать в этуже формулу так, чтобы округлялось к выводу болле близкого значения - вводим 4.1 - показывает 4 (из таблицы)

если равное, то в сторону меньшего - 4.3 -> 4.2

С уважением,

a**o@gmail.com

О.В.

Вам поможет использование функции ВПР с истинным признаком интервального просмотра.

Обратите внимание на пример (во вложении).

Последний, четвертый, параметр в функции говорит о том, каким образом будет происходить отбор. Если там стоит 0 (или ЛОЖЬ), то отбирается значение по уникальному совпадению. По Вашей задаче, там надо поставить 1 (или ИСТИНА), чтобы выборка происходила по попаданию в интервал.

Олег Видякин

akk

Все, наступает лето, курсов становится меньше, времени — больше. Так что принимаю снова задачки и вопросы. Но только не контрольные от студентов (см. пост ниже).
А в качестве затравки рассказываю пример про использование функции ВПР для вычищения грязи из столбиков.

Проблема: Например, таблица вводится вручную неквалифицированным человеком, который оставляет много “мусора”. Или, в базе собираются данные из разных источников (Axapta, 1C, … ) и в этих источниках один итот же товар назван по-разному. Как можно быстро навести порядок и привести все данные к единым образцам? Смотрите пример.

Столбец A содержит название месяца, столбец B — показатель этого месяца. И так на 20 тыщ строк вниз. Месяцы введены как бог на душу положит.

Делаем так: Копируем в буфер столбик A и вставляем его на листе Справочник.
Затем удаляем дубликаты кнопку “Удалить дубликаты” с вкладки “Данные”.
У нас собрались все варианты месяцев в базе. И прописываем напротив каждого варианта правильный. Напротив правильного тоже пишем правильные.

Теперь переходим на базу и создаем столбец для правильных месяцев.
В нем пишем формулу =ВПР(A2;Справочник!A:B;2;0)

И наслаждаемся… Отпишитесь, кто попробовал на своем опыте…. интересно применение.

vlookup

А пример мне ученики подсказали на уроках моего курса. Спасибо им за это, я уже их конфеткой наградил, а им тоже польза была — от решения задачки.