А вот кому рейтинг?
Если вы составляете и ведете списки, в которых есть показатели, расставляющие всех по местам, то вам пригодится мой файлик.
Прикол в том, что в одном месте список ведется, а в другом — расставляется по убыванию показателя.
Как всегда, самое интересное приходит в голову во время работы над проектом клиента.
Вот здесь находится решение для всех смет, которые когда-либо создавались и будут создаваться. :-)
Сложность их составления состоит в том, что итоги разного уровня перемешаны между собой в одном столбце и необходимо производить всякий раз ручное, выборочное суммирование, что затягивает процесс и чревато множеством ошибок.
Мое решение основано на правильном кодировании операций. Все работы надо прокодировать в строгом формате 01.01.01.01. и тогда проблем с выборочным суммированием не будет.
Пользуйтесь.
Посмотрите мое последнее произведение. :-)
Помогите с решением вопроса. Возможно ли сделать так, что бы Если шифра нет, то выводится #Н/Д. Как от него избавиться? Что бы просто было пустое поле…. У меня только с функцией ЕСЛИ ноль получается… :(((((
Заранее спасибо!
More »
Заранее спасибо.
Подскажите следующее:
возникла проблема в Excel - хочу отобразить формулы на рабочем листе - делаю обычную процедуру - копировать - специальная вставка - формулы - отображаются числа (формула пересчитывает значение для следующих данных) либо значение 0. С чем это может быть связано (у меня есть предположение, что это настройки Excel), но какие понять не могу. Помогите пожалуйста, буду очень благодарна.
О., работающая на почте, снова просит о помощи (подробности внутри файла).
Что ж, поможем
Блин, приятно же.
Что изменилось.
1. На листе Тариф2 составлена таблица соответствий. В которой в первом столбце указаны минимальные значения для каждой категории, а во втором столбце — цены.
2. В красной ячейке на листе Заказ2 стоит Ваша же формула с ВПР. Но с некоторыми изменениями — изменена таблица выборки (лист Тариф2 и столбиков уже 2); в качестве признака интервального просмотра указана 1. Т.е., ВПР ищет данные не по точному совпадению веса, а по попаданию его в интервал между минимальным значением данного диапазона и минимальным значением следующего.
Ежели что-то будет непонятно — пишите.
Копия Возврат 10
с уважением О.*
Проблема в расчете времени простоев машин, не могу сделать так чтоб не прибавлялось время к расчету если простой был с одних суток на другие, эксель делает 24 в плюс к общему времени Особенно интересует колонка “Общее время простоя”
Строки которые не могу просчитать выделены зеленым.
Подправьте всю таблицу при возможности чтобы можно было считать время не зависимо был ли простой в течение дня или в течение нескольких дней
Заранее благодарен
Отчет +
Помогло?
Копия Отчет +
Вот ученики задали вопрос:
А как узнать, сколько человек отработал на предприятии?
Кадровики дают шесть чисел — год, месяц, день трудоустройства и год, месяц, день увольнения. Надо узнать, сколько полных лет в промежутке между двумя датами, сколько полных месяцев в неполном году и сколько дней в остатке месяца.
Во вложении — решение.
Пришлось применить редкую для меня функцию ДАТАМЕС, которая вычисляет дату, отстоящую от указанной даты на нужное количество месяцев.
Проверяем, пишем замечания.
Сколько работал
Ловите.
Скачки
Олег, гран мерси вам. Конечно можете публиковать мой пример. Здесь всё из практических нужд пользователя - нужен поиск образца в строке одного столбца и его кодировка в такой же строке другого столбца. Я в экселе обычно пользовался формулами ( IF, AND, OR) и мне этого хватало, а тут прямо жаба заела - жалко было всё это делать вручную.
Есть несколько вопросов, которые мне задают чаще всего. Некоторые из них — сложные и требуют углубления в проект. Некоторые — вызывают у меня улыбку. Вот как этот.
Чтобы улыбаться по другим поводам помещаю этот пост.
Добрый день, Олег!
Вы не подскажете, как сделать,чтобы текст улетал полностью из одной ячейки в другую ячейку на другом листе? Что это за волшебная кнопка?
Заранее огромное спасибо!
Попробуйте так.
Встаньте на втором листе в нужную конечную ячейку. Поставьте знак «=». И после этого тыкните на корешок первого листа, а затем по нужной начальной ячейке. Затем тыкните по волшебной клавише Enter. Должно помочь. Если не помогло — пишите, подскажу другой способ.
Здорово! Всё работает =)) Большое-большое спасибо!!!!!!!!
http://www.cnews.ru/news/line/index.shtml?2010/10/08/411491
Конечно, сдается мне, что эта статья появилась не совсем случайно в новостных лентах. Ибо Microsoft сейчас активно продвигает свой новый пакет MSO 2010. Но это тот самый случай, когда я с MS полностью согласен. Open Office — такая хрень, по-моему, хоть и бесплатная.
[11:45:21] R* A*: Олег, приветас!
[11:50:35] Олег Видякин: здарова!
[11:50:59] R* A*: : подскажи плиз как решить задачку.
есть файл и в нем данные
Абакан = 1
Алапаевск = 3
Александров = 7
Альметьевск = 6
Анадырь = 1
Анапа = 7
Ангарск = 5
Армавир = 2
Арск = 2
Артем = 2
как бы его в эксель импортировать так, чтобы город - это был один столбец, а
цифра - это второй столбец?
[11:51:11] R* A*: : файл исходно просто текстовый
[11:51:33] Олег Видякин: тебе как всегда в эксель?
[11:51:45] R* A*: : ну да :)
[11:52:12] Олег Видякин: вставляешь в лист ексель
[11:52:22] Олег Видякин: выделяешь, вкладка Данные Текст по столбцам.
[11:52:27] Олег Видякин: Затем с разделителями
[11:52:33] Олег Видякин: И указываешь = как разделитель столбцов
Здравствуйте, Олег.
Необходимо перевести время формата 00:00:00:00 (сутки:часы:минуты:секунды) в число количества часов формата 00,00.
Если время без суток, то переводим =00:00:00*24 и всё получается:
Если пробуем количество времени более суток т.е. 00:00:00:00 формула не работает:
#VALUE!
Посоветуйте, как решить эту проблему.
Заранее благодарен,
Проблема вовсе не в том, что время в Вашем примере больше суток. А в том, что данные представленные в виде 00:00:00:00 принципиально не распознаются Excel как Время. Для Excel в этих ячейках стоит текст и именно поэтому он не может их прочитать и дает при вычислениях ошибку #VALUE! Составители данной таблицы перемудрили с заполнением ячеек, пытаясь выделить количество полных суток в ячейках с Временем.
Вот решение Вашей задачи. Время, большее суток
В нем я “отрезал” лишнюю информацию о сутках, превратив ее в количество часов и произвел необходимые вычисления.
С уважением, Светлана.
Здравствуйте Олег!
Имею еще один вопрос к владыке :-)
Давно мучаюсь вопросом, существует ли в Excel возможность косвенной переадресации, а именно:
допустим в ячейке хранится адрес, который используется в какой-нибудь поисковой формуле (например Vlookup), при описании в формуле массива поиска(в позиции 2 ) вместо адреса идет ссылка на эту ячейку что-нибдь этакое Vlookup($B12;C23:B(A1);2,false). Соответственно меняя адрес в этой ячейке А1 (допустим результат каких -либо вычислений, например COUNTIF() будет простое число) можно сделать плавающий диапазон поиска (допустим по вертикали). До этого так делал > в СУБД, подозреваю, что для этого к Excel существует VBA. Как спец, что посоветуете?
Большое спасибо!
Посоветую использовать функцию ДВССЫЛ, которая позволяет обрабатывать “плавающие” диапазоны. Например, в ячейке A1 написано число 1, а в ячейке B1 число 50
Тогда результатом функции ДВССЫЛ(”Лист1!D”&A1&”:E”&B1) будет являться ссылка на диапазон, D1:E50 на листе “Лист1″.
Функция ДВССЫЛ редко используется самостоятельно. Как правило, внутри других функций.
Вот пример с ВПР: =ВПР(A3;ДВССЫЛ(”Лист1!D”&A1&”:E”&B1);2;ЛОЖЬ)
Как раз делает выборку по значению, указанному в ячейке A3 из таблицы Лист1!D1:E50, причем 1 и 50 можно менять в ячейках A1 и B1.
К сожалению, не очень знаком с англоязычной версией, поэтому в помощь предлагаю файл соответствий названий. Откопал где-то на сайте > Microsoft давным-давно… Пользуйтесь.
functions(en-rus)
Окончательно поверил во владыку Excel :- ) , оказывется Дед Мороз существует!
Обязательно поиграюсь с этой функцией.
PS: Кстати я был в шоке, когда увидел первый раз русскоязычную версию, надо же умудрились перевести язык программирования, понятно, что для обычного пользователя так намного легче, но функции на английском не работают! Получается необходимо в одной программе выучить язык дважды!
Еще раз огромное спасибо!
Константин.
Олег, добрый день.
Много делаю сам и многим даю советы, но почитав и посмотрев советы и примеры на вашем сайте, понял, что мне еще учиться и учиться… Восхищен. А пишу я еще вам и потому, что воткнулся в одну проблемку, и никак не могу найти решение. Подскажите, если возможно путь ее решения.
Есть дата оплаты по договору. И есть дата, по которой реально заплатили. Суть в том, что если дата по которой реально заплатили больше чем дата по договору, нужно считать пени. Пени считаются исходя из 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%
а как все это сделать, чтобы эксель сам раскладывал на число дней и брал применительно к этому числу дней нужную ставку, все перемножал и выводил готовый результат?
Добрый день!
Во вложении решение Вашей задачки. Без примерного файла с Вашей стороны я нафантазировал там… :-) Ну, надеюсь, разберетесь.
Там я чего только не использовал. И ВПР место нашлось, и СУММЕСЛИ. Но в итоге симпатишно получилось.
Как быть со ставкой рефинансирования — вводите ее в лист Справочник каждый раз когда она обновляется новой строкой по образцу прежних. Т.е., дата введения новой ставки, размер ставки. И будет Вам счастье в виде правильно рассчитанной пени.
Пеня
Удачи!
Олег, доброго времени суток!
Подскажите, как написать формулу для определения разницы во времени, если результат превышает 24 часа?
Данные вводятся в две соседние ячейки в формате ДД.ММ.ГГГГ чч:мм
Результат необходимо отобразить в текстовом формате. Например: 36 ч. 37 мин. (начальник не воспринимает формат отображения 36:37 )
Можно применить к этой ячейке следующий числовой формат: «[ч] “ч. “мм” мин.”». Смотрите пример во вложении.
А вообще с форматами надо поступать так:
Тыкаете правой кнопкой мышки по ячейке и выполняете ФОРМАТ ЯЧЕЙКИ — ЧИСЛО
там выбираете наиболее близкий к нужному формат и экспериментируете с ним. Например, я выбрал в разделе «Время» строчку 37:30:55. Затем тут же выбираем категорию «(все форматы)» и получаем строчку «[ч]:мм:сс», которую преобразуем в нужную, ориентируясь на то, как будет оформляться число.
Здравствуйте, 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 (или ИСТИНА), чтобы выборка происходила по попаданию в интервал.
Олег Видякин
Все, наступает лето, курсов становится меньше, времени — больше. Так что принимаю снова задачки и вопросы. Но только не контрольные от студентов (см. пост ниже).
А в качестве затравки рассказываю пример про использование функции ВПР для вычищения грязи из столбиков.
Проблема: Например, таблица вводится вручную неквалифицированным человеком, который оставляет много “мусора”. Или, в базе собираются данные из разных источников (Axapta, 1C, … ) и в этих источниках один итот же товар назван по-разному. Как можно быстро навести порядок и привести все данные к единым образцам? Смотрите пример.
Столбец A содержит название месяца, столбец B — показатель этого месяца. И так на 20 тыщ строк вниз. Месяцы введены как бог на душу положит.
Делаем так: Копируем в буфер столбик A и вставляем его на листе Справочник.
Затем удаляем дубликаты кнопку “Удалить дубликаты” с вкладки “Данные”.
У нас собрались все варианты месяцев в базе. И прописываем напротив каждого варианта правильный. Напротив правильного тоже пишем правильные.
Теперь переходим на базу и создаем столбец для правильных месяцев.
В нем пишем формулу =ВПР(A2;Справочник!A:B;2;0)
И наслаждаемся… Отпишитесь, кто попробовал на своем опыте…. интересно применение.
А пример мне ученики подсказали на уроках моего курса. Спасибо им за это, я уже их конфеткой наградил, а им тоже польза была — от решения задачки.
Лист “Временной промежуток” — Задача решается через простое вычитание с добавлением условия: “ЕСЛИ второе время меньше первого, то добавь сутки к вычислению, иначе не добавляй ничего”.
У проблема и не знаю как её решить, т.к. познания в Exel плохие. Скажем, есть два столбца (А и В), в столбце А идут номера по порядку, а в столбце В разные значения и пустые ячейки. Применяю фильтр по столбцу В, чтобы отображались только непустые ячейки. И как сделать так, чтобы в столбца А пересчитывался номер по порядку и опять начинался с 1. (Причем пустая ячейка может быть и первой)
Здесь три задачи.
По заказу Группы компаний “РЕСКОМ” нарисовал учет “движения автомобилей” в автосервисе.
Крррррасиво… Подробности читайте в самом файле на листе “Инфо”.
Для реализации данного эффекта надо применить возможность под названием “Проверка данных”.
1. Выделить диапазон, на который надо наложить ограничение.
2. Выполнить команду ДАННЫЕ — ПРОВЕРКА ДАННЫХ.
3. На вкладке “Параметры” выбрать Тип данных “Длина текста”
4. установить ограничение.
С уважением.
Олег Видякин.
Добрый вечер! Подскажите, пожалуйста, как в Excel 2007 заблокировать отдельные ячейки? Спасибо за ответ.
Шаг первый.
Выделяем те ячейки, которые НЕ будут блокироваться.
Тыкаем правой кнопкой по выделенному и выбираем ФОРМАТ ЯЧЕЙКИ – ЗАЩИТА.
Снимаем галочку, «Защищаемая ячейка»
Затем защищаем весь лист
Если в 2003, то СЕРВИС – ЗАЩИТА – установить защиту листа
Если в 2007, то Вкладка Главная – кнопка формат – защитить лист
Пароль по желанию, можно не вводить
Если убрать галочку около «Выделение заблокированных ячеек», то заблокированные ячейки даже выделить будет нельзя.
Ответил?
Да! Спасибо большое!
скажите пожалуйста как в excele отключить функцию замена 01-4 на 1.апр?
Боюсь, что отключения такой замены нет.
Другое дело, что Вы можете писать текст “01-4″, начиная с апострофа (Вот так ‘01-4), это одинокая черточка на русской буковке “Э”, но в английской раскладке клавиатуры. Тогда введенная Вами цифровая последовательность будет рассмотрена как текст и к ней не будет применена никакая трансформация. Сам апостроф не показывается на экране.
Помог?
оо!спасибо большое,так все подробно прям написали)
В Питере есть Игра. Ночью, с субботы на воскресенье, люди, которым нравятся приключения, отправляются на поиски заданий и три часа колесят по городу, находя и решая загадки.
Вот тут подробное описание правил: http://vkontakte.ru/club7250364
У нас в Калининграде что-то подобное есть. Называется “ночной дозор”, по-моему. С нашими ребятами-организаторами не знаком, а вот для питерских нарисовал учетную табличку.
Вот тут она лежит…
Игра2
Кстати, этот файл для Excel 2007.
Добрый день :) у меня возник вопрос с формулой. Не знаю как воплотить в жизнь такую задачу: Есть 31 день в столбце, в ячейке есть данные (например 231, 263, 238, 231, 250 …. n)
1 (231)
2 (250)
3 (269)
4 (250)
5 (и так далее)
Я хочу вычислить сколько раз за 31 день у меня были одинаковые отметки. Например 250 уже 2 раза (СЧЁТЕСЛИ). Но проблема в том что у меня не жестко заданное число 250 а интервал например от 255 до 250, и я не знаю как записать команду чтобы так же считались и промежуточные числа, 251, 252, 253.
Рассмотрите пример на использование функции БСЧЁТ.
Синтаксис этой функции: =БСЧЁТ(база данных;поле;критерий)
где база данных – это вся исходная таблица, в которой находятся данные.
поле — это номер столбца в базе данных, который содержит подсчитываемые данные.
Критерий — особая таблица, которая задает условия отбора. Она содержит названия столбцов, которые будут помогать отбирать данные. В данном примере два раза один столбец, поскольку данные необходимо отобрать припомощи комбинации условий логической операцией И.
Рассмотрите внимательно пример и напишите, что оказалось непонятным.
Скачать пример на БСЧЁТ
Если Вы живете в Калининграде и Вас интересует “продвинутый” Excel, то Вы можете воспользоваться случаем и в апреле присоединиться к моему авторскому семинару “Excel для управления и анализа”.
Это будет 6 суббот с 10-00 до 13-30. Программу семинара читайте на сайте моего Центра http://www.vidyakin.ru/improvement/Excel2/.
А для записи звоните секретарю (4012)39-12-37 или пишите мне excel@vidyakin.ru.
Пришла задачка от человека, который занимается тем, что я совершенно не понимаю. Мне потом объяснили, что он трейдер. Вот решил задачку для него. Качайте.
sravnenie
(здесь есть программка, так что надо макросы включить)
Похвастаюсь его откликом:
“Удивительно как Вы быстро решаете подобные задачи, просто, кладезь знаний по Excel :)”.
Вот. Люблю когда меня хвалят. Доброе слово и кошке приятно. А уж мне-то как хорошо.
Прислал Сергей.
Описание: Приходят прайс-листы на телефоны в виде ”марка модель цвет1, цвет2, цвет3,…”
Надо представить в виде
“марка модель цвет1
марка модель цвет2
марка модель цвет3
…”
Сделал, любуйтесь.
Тут макросы могут потребоваться. Их надо включить.
Если чего непонятно — пишите.
Сергей из Санкт-Петербурга попросил сделать табличку для учета рейсов. Что здесь есть:
Вобщем, информация на листе “Информация”. Работа на листе “заявка”. Вот табличка: transport Макросы надо включить. Как же без них?
Можно ли делать запрос по двум (или более) ключевым полям, чтобы однозначно определить их комбинацию? Тогда как их задавать?
Ключевое поле потому и называется ключевым, чтобы быть единственным.
Чтобы производить выборку по уникальному совпадению более чем одного параметра надо либо использовать специальную операцю текстового сложения СЦЕПИТЬ или использовать функцию по-русски называющуюся “БИЗВЛЕЧЬ”.
Смотрите примерчик про магазинчик с применением СЦЕПИТЬ :
можно ли в Excel делать запросы от одной таблицы к другой (или от одной рабочей старницы к другой) (назовем их А и В), имеющих одинаковые ключевые поля. Задача:
заполнить строки в таблце А соотвествующими значениями из таблицы В, связанных по ключевым полям (т.е. имеющих одинаковые значения параметров в ключевых полях таблиц А и В соотвественно).
Да. Для этого используется функция ВПР.
Смотрите пример на использование этой функции. More »
Помоги, пожалуйста сделать следующий расчет для налога.
Пожалста. Смотри вложение.
Да, спасибо! Все верно!
Обязательно пополню твой яндекс-кошелек!
С наступающим, большого соц налога!
Excel для управления и анализа (Excel1)
http://vidyakin.ru/courses/adult/excel-dlya-upravleniya-i-analiza/
А в нем мы будем изучать, что такое «Ниагарский водопад», как на его основе и с помощью сводных таблиц мгновенно получать ответы на свои вопросы. Попутно научимся строить правильные диаграммы, поймем, как работать с датой и временем, как вытягивать нужные сведения из текста, решать задачи с ЕСЛИ, ВПР-ить и условно суммировать при помощи СУММЕСЛИМН. Ну не мне вам рассказывать — вы и так все знаете ;-)
Как всегда — 12 занятий по 90 минут.