22 Dec 2013 @ 1:33 PM 

А вот кому рейтинг?
Если вы составляете и ведете списки, в которых есть показатели, расставляющие всех по местам, то вам пригодится мой файлик.

Прикол в том, что в одном месте список ведется, а в другом — расставляется по убыванию показателя.

 15 Dec 2013 @ 9:35 PM 

Как всегда, самое интересное приходит в голову во время работы над проектом клиента.
Вот здесь находится решение для всех смет, которые когда-либо создавались и будут создаваться. :-)

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

Мое решение основано на правильном кодировании операций. Все работы надо прокодировать в строгом формате 01.01.01.01. и тогда проблем с выборочным суммированием не будет.

Пользуйтесь.

Tags Tags: , , , ,
Categories: Бизнес-задача
Posted By: vidyakin
Last Edit: 09 Jul 2014 @ 11 08 AM

E-mailPermalinkComments Off
 06 Jun 2013 @ 11:09 AM 

Посмотрите мое последнее произведение. :-)

Tags Tags: , , , , , ,
Categories: Новости
Posted By: vidyakin
Last Edit: 12 Nov 2014 @ 10 43 PM

E-mailPermalinkComments Off
 05 Mar 2013 @ 9:26 AM 

r*0@mail.ru


День добрый, владыка Олег!

Помогите с решением вопроса. Возможно ли сделать так, что бы Если шифра нет, то выводится #Н/Д. Как от него избавиться? Что бы просто было пустое поле…. У меня только с функцией ЕСЛИ ноль получается… :(((((
Заранее спасибо!

More »

Tags Tags: , , , , , ,
Categories: Новости
Posted By: vidyakin
Last Edit: 14 Mar 2013 @ 02 09 PM

E-mailPermalinkComments (0)
 14 Feb 2013 @ 11:18 PM 

r*0@mail.ru


Здравствуйте Олег!
Помогите в решении проблемы. Просто мне надо общее количество минут потому что дальше другая формула это время использует.
В Excel при расчете разницы значений двух ячеек, содержащих значение времени (например, 10:17 минус 9:55), выводится ответ “0:22″. Как выводить его в виде “22″ (то есть только минуты)
=МИНУТЫ(ЯчейкаСРазностьюВремени) - это работает, НО только если время “0:22″. А что если, допустим, “1:22″?? Как вывести 82 минуты??

Заранее спасибо.

О.В.


Умножьте время на 24 и на 60 вот и будет Вам количество минут. И функций никаких не надо.
Смотрите примерчик:

Tags Tags: , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 14 Mar 2013 @ 02 41 PM

E-mailPermalinkComments (0)
 08 Feb 2013 @ 1:56 PM 

p*@mail.ru


Олег, добрый день!
Помогите, пожалуйста, есть файл в нем есть лист в котором указываются результаты по датам. Во вложенном файле это лист “выход”
и есть отчет в который надо вставить средние данные за период между датами указанными в столбце например за период с 01.02.12 по 09.02.12, затем период будет меняться. Как это сделать. В ячейке пыталась написать формулу но она показывает какую то чушь. Пыталась правда на сумме.

More »

 06 Feb 2013 @ 5:07 PM 

e*@ukr.net


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

Подскажите следующее:
возникла проблема в Excel - хочу отобразить формулы на рабочем листе - делаю обычную процедуру - копировать - специальная вставка - формулы - отображаются числа (формула пересчитывает значение для следующих данных) либо значение 0. С чем это может быть связано (у меня есть предположение, что это настройки Excel), но какие понять не могу. Помогите пожалуйста, буду очень благодарна.

О.В.


Если Ваша цель — просто отобразить формулы, то действовать надо не так.
Во вкладке формулы надо найти кнопку «Показать формулы»

Tags Tags: , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 21 Mar 2015 @ 02 42 PM

E-mailPermalinkComments (0)

О., работающая на почте, снова просит о помощи (подробности внутри файла).

О.:


Форматирование

Что ж, поможем

О.В.:


Проблема в том, что у Вас ячейка AK3 содержит результат операции с применением функции ПСТР, а это текстовая функция, значит и результат — текст. Т.е. 6 в ячейке AK3 — это цифра. А ячейка AU3 — это результат арифметического действия (вычитания). Т.е., 6 в этой ячейке — это число. 6 как цифра и 6 как число — это разные значения. Поэтому их сравнение в условном форматировании и приводит к имеющемуся результату.
Чтобы избежать этого эффекта, надо сравнивать не сами значения, а результат преобразования при помощи функции, например, ТЕКСТ.
Пример я положил во вложение.
Копия форматирование

О.:


Олег огромное Вам спасибо. Вы просто умница
с уважением О.

Блин, приятно же.

Tags Tags: , , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 23 Nov 2011 @ 10 57 PM

E-mailPermalinkComments (0)
 07 Nov 2011 @ 10:57 PM 

Почтальон Печкин принес посылку.

О.*:


Олег! Прошу помощи по EXCE,
файл прикреплён, там всё описано.
Возврат

О.В.:


Ловите.

Что изменилось.
1. На листе Тариф2 составлена таблица соответствий. В которой в первом столбце указаны минимальные значения для каждой категории, а во втором столбце — цены.
2. В красной ячейке на листе Заказ2 стоит Ваша же формула с ВПР. Но с некоторыми изменениями — изменена таблица выборки (лист Тариф2 и столбиков уже 2); в качестве признака интервального просмотра указана 1. Т.е., ВПР ищет данные не по точному совпадению веса, а по попаданию его в интервал между минимальным значением данного диапазона и минимальным значением следующего.
Ежели что-то будет непонятно — пишите.
Копия Возврат 10

О.*:


Олег!
Большое мерси!!!
Всё хорошо, так как надо.
Ещё раз спасибо ВАМ и ВАШЕМУ сайту

с уважением О.*

Tags Tags: , , , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 07 Nov 2011 @ 11 04 PM

E-mailPermalinkComments Off
 06 Nov 2011 @ 12:17 AM 

В*


Олег Видякин.
Доброго времени суток!

Проблема в расчете времени простоев машин, не могу сделать так чтоб не прибавлялось время к расчету если простой был с одних суток на другие, эксель делает 24 в плюс к общему времени Особенно интересует колонка “Общее время простоя”

Строки которые не могу просчитать выделены зеленым.
Подправьте всю таблицу при возможности чтобы можно было считать время не зависимо был ли простой в течение дня или в течение нескольких дней

Заранее благодарен
Отчет +

О.В.


Добрый!
А так ли уж важно, чтобы таблица велась именно в этом виде?
Попробуйте мою таблицу. Она не требует сложных формул.
Секрет в том. что надо знать сущность дат и времени в Excel. Дата — это целое положительное число, которое обозначает порядковый номер дня, начиная с даты 1 января 1900 года. Сейчас идет 40800 какой-то день с момента начала отсчета. Время — это дробное число в диапазоне от 0 до 1 (как правило), обозначающее долю суток. Т.е. 1 — это сутки.
Если сложить дату и время, то получится “момент” ( во всяком случае его я так буду называть). Который и надо использовать в Вашем примере. Есть момент начала простоя — это какое-то число, целая часть которого соответствует дате, а дробная — времени суток. Так же есть момент конца простоя. Длительность простоя — это простое вычитание момента конца простоя от момента начала простоя.
Представить результат можно по-разному. Excel как правило показывает время не более 24 часов, но можно поменять оформление столбика (”Общее время простоя”) и получить вид, в котором количество часов показывается больше чем 24.
Посмотрите мой пример. В нем сначала вводятся дата начала простоя, затем время начала простоя, затем эти величины складываются, чтобы получить “момент” начала простоя. Аналогично поступаем для получения момента конца простоя. Затем производим вычитание и имеем величину простоя, Если она меньше единицы, то в обычном оформлении времени представится как величина с количеством часов меньше 24. Если величина просто больше суток, то Excel “съест” сутки и все равно покажет только хвост без учета прошедших полных суток. Но храниться все равно эти полные сутки будут и надо просто эти ячейки переоформить соответствующим образом (правой кнопкой по ячейкам — формат ячейки — число — время — выбрать вариант “37:30:55″), чтобы показывалось количество часов большее чем 24.

Помогло?
Копия Отчет +

В*


Спасибо огромное!
Помогло, не думал что так все просто!
Спасибо еще раз Олег

Tags Tags: , , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 06 Nov 2011 @ 12 17 AM

E-mailPermalinkComments Off
 23 Jul 2011 @ 5:52 PM 

d182d180d183d0b4d0bed0b2d0b0d18f-d0bad0bdd0b8d0b6d0bad0b0Вот ученики задали вопрос:
А как узнать, сколько человек отработал на предприятии?
Кадровики дают шесть чисел — год, месяц, день трудоустройства и год, месяц, день увольнения. Надо узнать, сколько полных лет в промежутке между двумя датами, сколько полных месяцев в неполном году и сколько дней в остатке месяца.
Во вложении — решение.
Пришлось применить редкую для меня функцию ДАТАМЕС, которая вычисляет дату, отстоящую от указанной даты на нужное количество месяцев.
Проверяем, пишем замечания.
Сколько работал

Tags Tags: , , ,
Categories: Бизнес-задача
Posted By: vidyakin
Last Edit: 23 Jul 2011 @ 06 34 PM

E-mailPermalinkComments (0)
 15 Jul 2011 @ 6:00 PM 

From: A*

Subject: Нужна помощь. Help!

Здравствуйте Олег. Занимаюсь в экселе анализом скачек на Английских ипподромах.
Образец файла прикреплён. Мне нужно что-бы например в столбце класс 6 появлялась единичка,
если в строке столбца А есть это слово и цифра (Class 6), если нет - то ничего. Ну и заковыка с
количеством лошадей, участвующих в забеге 5 runners, 10 runners и т.д. - тут нужна уже не единичка, а число из строки.
Помогите, а то я гуманитарий и в математике не силён. Строки 2, 3, 4 - это то, как я бы хотел это видеть.

O.В.

Ловите.
Скачки

From: A*

Олег, гран мерси вам. Конечно можете публиковать мой пример. Здесь всё из практических нужд пользователя - нужен поиск образца в строке одного столбца и его кодировка в такой же строке другого столбца. Я в экселе обычно пользовался формулами ( IF, AND, OR) и мне этого хватало, а тут прямо жаба заела - жалко было всё это делать вручную.

Tags Tags: , , , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 15 Jul 2011 @ 06 11 PM

E-mailPermalinkComments (0)

Tags Tags: , ,
Categories: Новости
Posted By: vidyakin
Last Edit: 12 Jan 2011 @ 01 25 PM

E-mailPermalinkComments (0)

Есть несколько вопросов, которые мне задают чаще всего. Некоторые из них — сложные и требуют углубления в проект. Некоторые — вызывают у меня улыбку. Вот как этот.
Чтобы улыбаться по другим поводам помещаю этот пост.

Ekaterina

Добрый день, Олег!
Вы не подскажете, как сделать,чтобы текст улетал полностью из одной ячейки в другую ячейку на другом листе? Что это за волшебная кнопка?
Заранее огромное спасибо!

О.В.

Попробуйте так.
Встаньте на втором листе в нужную конечную ячейку. Поставьте знак «=». И после этого тыкните на корешок первого листа, а затем по нужной начальной ячейке. Затем тыкните по волшебной клавише Enter. Должно помочь. Если не помогло — пишите, подскажу другой способ.

 

Ekaterina

Здорово! Всё работает =)) Большое-большое спасибо!!!!!!!!

Tags Tags: , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 30 Sep 2014 @ 10 08 PM

E-mailPermalinkComments Off
 08 Oct 2010 @ 3:33 PM 

http://www.cnews.ru/news/line/index.shtml?2010/10/08/411491
Конечно, сдается мне, что эта статья появилась не совсем случайно в новостных лентах. Ибо Microsoft сейчас активно продвигает свой новый пакет MSO 2010. Но это тот самый случай, когда я с MS полностью согласен. Open Office — такая хрень, по-моему, хоть и бесплатная.

Tags Tags: , ,
Categories: Новости
Posted By: vidyakin
Last Edit: 19 Oct 2014 @ 04 56 PM

E-mailPermalinkComments Off
 01 Oct 2010 @ 11:53 PM 

[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] Олег Видякин: И указываешь = как разделитель столбцов

Tags Tags: , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 14 Dec 2014 @ 06 38 PM

E-mailPermalinkComments Off
 01 Sep 2010 @ 3:12 PM 

В.

Здравствуйте, Олег.
Необходимо перевести время формата 00:00:00:00 (сутки:часы:минуты:секунды) в число количества часов формата 00,00.
Если время без суток, то переводим =00:00:00*24 и всё получается:
Если пробуем количество времени более суток т.е. 00:00:00:00 формула не работает:
#VALUE!
Посоветуйте, как решить эту проблему.
Заранее благодарен,

О.В.

Проблема вовсе не в том, что время в Вашем примере больше суток. А в том, что данные представленные в виде 00:00:00:00 принципиально не распознаются Excel как Время. Для Excel в этих ячейках стоит текст и именно поэтому он не может их прочитать и дает при вычислениях ошибку #VALUE! Составители данной таблицы перемудрили с заполнением ячеек, пытаясь выделить количество полных суток в ячейках с Временем.
Вот решение Вашей задачи. Время, большее суток
В нем я “отрезал” лишнюю информацию о сутках, превратив ее в количество часов и произвел необходимые вычисления.

В.


Олег,
большое спасибо!
Опубликуйте ответ на блоге, пусть люди пользуются.
Всего доброго.

Tags Tags: , , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 19 Oct 2014 @ 03 58 PM

E-mailPermalinkComments Off

Светлана


Олег, здравствуйте!
Недавно освоила функцию ВПР, но тут попал файл с похожей задачей, и там применялась функция ИНДЕКС(Столбец с интересуемой инф из Табл;ПОИСКПОЗ(Искомый арт; столбец с арт из Табл;0))
Мне непонятно почему был применен ИНДЕКС, а не ВПР, ведь ВПР-удобнее.
Или я по незнанию функции ИНДЕКС так рассуждаю?
Да, ИНДЕКС применялся с ссылкой на другой лист, хотя ВПР тоже можно так применять.

О.В.


Наверное, принципиальная разница возникает при использовании функции ВПР с признаком интервального просмотра =1 (последний параметр). Тогда эта функция приобретает новые возможности и становится заменой даже функции ЕСЛИ, поскольку позволяет гораздо проще разбивать данные на диапазоны.
А для задач выборки — использование ВПР проще чем комбинация ПОИСКПОЗ + ИНДЕКС. Разве что когда возникает ограничение в использовании ВПР — при применении этой функции очень важно, чтобы ключевой столбец стоял именно слева в таблице. Когда как применение ПОИСКПОЗ + ИНДЕКС не накладывает такого ограничения.
Ответил?

Светлана


Огромное спасибо!
Особенно за оперативность :)))))
Правда пока ковырялась в своей таблице возник новый вопрос - как отделить ячейки с текстом в латинице от ячеек с текстом в кириллице?
Сижу выискиваю подходящие функции и пока безрезультатно, а работа стоит, может это вообще невыполнимо….

О.В.


для новой задачи попробуйте при помощи КОДСИМВ вычислить код первой буквы и сравнить его, по-моему с числом 128. Ибо все кода большие 127 — не латиница.

Светлана


Еще раз ОгромноеСПАСИБО!
И буду считать что мне крупно повезло :)))
Я даже не предполагала такого варианта решения.

С уважением, Светлана.

Tags Tags: , , , , , , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 09 Oct 2014 @ 08 40 AM

E-mailPermalinkComments Off

Константин

Здравствуйте Олег!

Имею еще один вопрос к владыке :-)

Давно мучаюсь вопросом, существует ли в 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: Кстати я был в шоке, когда увидел первый раз русскоязычную версию, надо же умудрились перевести язык программирования, понятно, что для обычного пользователя так намного легче, но функции на английском не работают! Получается необходимо в одной программе выучить язык дважды!

Еще раз огромное спасибо!

Константин.

Tags Tags: , , , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 20 Oct 2014 @ 08 08 AM

E-mailPermalinkComments Off
 31 May 2010 @ 5:04 PM 

Б.Л.

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

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

Есть дата оплаты по договору. И есть дата, по которой реально заплатили. Суть в том, что если дата по которой реально заплатили больше чем дата по договору, нужно считать пени. Пени считаются исходя из 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%

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

О.В.

Добрый день!

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

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

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

Удачи!

Tags Tags: , , , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 07 Oct 2014 @ 08 47 AM

E-mailPermalinkComments Off

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

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

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

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

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

О.В.

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

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

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

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

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

Tags Tags: , , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 07 Oct 2014 @ 08 45 AM

E-mailPermalinkComments Off

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

Tags Tags: , , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 09 Jul 2014 @ 11 08 AM

E-mailPermalinkComments Off

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

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

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

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

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

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

vlookup

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

Tags Tags: , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 18 May 2010 @ 07 07 PM

E-mailPermalinkComments (0)
 30 Nov 2009 @ 3:35 PM 

o****v@mail.ru:


Доброго времени суток,
столкнулся с проблемой, (когдато проходил курсы ексель 97) счас стоит задача:
тип ячейки - время в формате 00:00
как следать так, чтобы вводить время просто цифрами, не вводя знак “:”
т.е.:
чтобы вводить например: 2311, а в ячейке появлялось: 23:11 и еще, есть две ячейки: время начала и время конча, в третьей считается разница, т.е. время затраченное от начала до конца, НО!!! если время начала например 23:50, а конца 0:35, то не считается разница из-за большого или отрицательного времени!!!
возможно ли реализовать так, чтобы и в первом и в данном случае считало разницу 0:45, т.е. учитывался переход суток ???

О.В.


Во вложении результат.
Лист “Ввод данных” — тут я разделил число, вводимое в ячейке А2 на 100, чтобы отсечь первые две цифры использовал их в как количество часов в функции ВРЕМЯ, затем взял остаток от деления числа в А2 на 100, таким образом я отсек минуты и использовал их как минуты в функции ВРЕМЯ. Про секунды Вы ничего не сообщали, поэтому поставил 0 секунд.

Лист “Временной промежуток” — Задача решается через простое вычитание с добавлением условия: “ЕСЛИ второе время меньше первого, то добавь сутки к вычислению, иначе не добавляй ничего”.

Tags Tags: , , , , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 30 Nov 2009 @ 03 35 PM

E-mailPermalinkComments (0)
 13 Aug 2009 @ 4:25 PM 

Владимир


Доброго времени суток Олег.

У проблема и не знаю как её решить, т.к. познания в Exel плохие. Скажем, есть два столбца (А и В), в столбце А идут номера по порядку, а в столбце В разные значения и пустые ячейки. Применяю фильтр по столбцу В, чтобы отображались только непустые ячейки. И как сделать так, чтобы в столбца А пересчитывался номер по порядку и опять начинался с 1. (Причем пустая ячейка может быть и первой)

О.В.


Решение во вложении.

Здесь три задачи.

Первая.


Как пронумеровать строки, чтобы нумерация сама отслеживалась.


Ответ такой: Формула =СТРОКА()-1 дает номер строки ячейки, в которой она находится. А минус 1, чтобы отнять строку заголовка.

Вторая.


Как нумеровать игнорируя пустоты.


Совершенствуем первую формулу, отнимаем из нее количество пустот сверху от текущей строки:
=СТРОКА()-1-СЧИТАТЬПУСТОТЫ($B$1:B2)
Не забудьте пометить адрес B1 долларами, чтобы он при размножении вниз не менялся

Третья.


Как понять, что включена фильтрация.


Я не нашел ничего лучше, как в ячейке E1 разместить переключатель. Если в нем написать “да”, то будет применяться вторая формула, если другое слово, то применяется первая формула. И этот переключатель надо будет менять вручную каждый раз после фильтрации. Я думаю, что это несложно.
Т.е., итоговая формула в ячейке A1 выглядит так:
=ЕСЛИ($E$1=”да”;СТРОКА()-1-СЧИТАТЬПУСТОТЫ($B$1:B2);СТРОКА()-1)
Нумерация и фильтрация — пример скачать

 04 Jul 2009 @ 1:11 PM 

По заказу Группы компаний “РЕСКОМ” нарисовал учет “движения автомобилей” в автосервисе.

Крррррасиво… Подробности читайте в самом файле на листе “Инфо”.

autoservice

Tags Tags: , , ,
Categories: Бизнес-задача
Posted By: vidyakin
Last Edit: 04 Jul 2009 @ 01 25 PM

E-mailPermalinkComments (1)

q*@yandex.ru


Здравствуйте, Видякин.
Пишу Вам с вопросом. Excel 2003, как сделать макс. кол-во символов в ячейке = 100?
Помогите.
Спасибо!

О.В.


Добрый день!

Для реализации данного эффекта надо применить возможность под названием “Проверка данных”.

1. Выделить диапазон, на который надо наложить ограничение.
2. Выполнить команду ДАННЫЕ — ПРОВЕРКА ДАННЫХ.
3. На вкладке “Параметры” выбрать Тип данных “Длина текста”
4. установить ограничение.

С уважением.
Олег Видякин.

Poker House

Добрый вечер! Подскажите, пожалуйста, как в Excel 2007 заблокировать отдельные ячейки? Спасибо за ответ.

О.В.

Шаг первый.

Выделяем те ячейки, которые НЕ будут блокироваться.
Тыкаем правой кнопкой по выделенному и выбираем ФОРМАТ ЯЧЕЙКИ – ЗАЩИТА.
Снимаем галочку, «Защищаемая ячейка»

Затем защищаем весь лист

Если в 2003, то СЕРВИС – ЗАЩИТА – установить защиту листа
Если в 2007, то Вкладка Главная – кнопка формат – защитить лист

Пароль по желанию, можно не вводить

Если убрать галочку около «Выделение заблокированных ячеек», то заблокированные ячейки даже выделить будет нельзя.

Ответил?

Poker House

Да! Спасибо большое!

Tags Tags: , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 02 Sep 2014 @ 08 39 AM

E-mailPermalinkComments (3)
 20 May 2009 @ 12:24 PM 

z**@Yandex.ru

скажите пожалуйста как в excele отключить функцию замена 01-4 на 1.апр?

О.В.

Боюсь, что отключения такой замены нет.

Другое дело, что Вы можете писать текст “01-4″, начиная с апострофа (Вот так ‘01-4), это одинокая черточка на русской буковке “Э”, но в английской раскладке клавиатуры. Тогда введенная Вами цифровая последовательность будет рассмотрена как текст и к ней не будет применена никакая трансформация. Сам апостроф не показывается на экране.

Помог?

z**@Yandex.ru

оо!спасибо большое,так все подробно прям написали)

Tags Tags: , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 19 Oct 2014 @ 03 58 PM

E-mailPermalinkComments Off
 27 Apr 2009 @ 1:48 PM 

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

Вот тут подробное описание правил: http://vkontakte.ru/club7250364

У нас в Калининграде что-то подобное есть. Называется “ночной дозор”, по-моему. С нашими ребятами-организаторами не знаком, а вот для питерских нарисовал учетную табличку.

Вот тут она лежит…
Игра2
Кстати, этот файл для Excel 2007.

Tags Tags: , , ,
Categories: Бизнес-задача
Posted By: vidyakin
Last Edit: 02 Feb 2015 @ 11 43 AM

E-mailPermalinkComments Off
 15 Apr 2009 @ 10:58 PM 

Михаил:

Добрый день :) у меня возник вопрос с формулой. Не знаю как воплотить в жизнь такую задачу: Есть 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.

ОВ:

Рассмотрите пример на использование функции БСЧЁТ.
Синтаксис этой функции: =БСЧЁТ(база данных;поле;критерий)
где база данных – это вся исходная таблица, в которой находятся данные.
поле — это номер столбца в базе данных, который содержит подсчитываемые данные.
Критерий — особая таблица, которая задает условия отбора. Она содержит названия столбцов, которые будут помогать отбирать данные. В данном примере два раза один столбец, поскольку данные необходимо отобрать припомощи комбинации условий логической операцией И.

Рассмотрите внимательно пример и напишите, что оказалось непонятным.
Скачать пример на БСЧЁТ

Tags Tags: , , ,
Categories: Консультация
Posted By: vidyakin
Last Edit: 24 Oct 2014 @ 08 16 AM

E-mailPermalinkComments Off

Если Вы живете в Калининграде и Вас интересует “продвинутый” Excel, то Вы можете воспользоваться случаем и в апреле присоединиться к моему авторскому семинару “Excel для управления и анализа”.

Это будет 6 суббот с 10-00 до 13-30. Программу семинара читайте на сайте моего Центра http://www.vidyakin.ru/improvement/Excel2/.

А для записи звоните секретарю  (4012)39-12-37  или пишите мне excel@vidyakin.ru.

Tags Tags: ,
Categories: Новости
Posted By: admin
Last Edit: 04 Jul 2009 @ 01 26 PM

E-mailPermalinkComments (0)

Пришла задачка от человека, который занимается тем, что я совершенно не понимаю. Мне потом объяснили, что он трейдер. Вот решил задачку для него. Качайте.
sravnenie
(здесь есть программка, так что надо макросы включить)

Похвастаюсь его откликом:

“Удивительно как Вы быстро решаете подобные задачи, просто, кладезь знаний по Excel :)”.

Вот. Люблю когда меня хвалят. Доброе слово и кошке приятно. А уж мне-то как хорошо.

Tags Tags: , , , ,
Categories: Бизнес-задача
Posted By: admin
Last Edit: 10 Mar 2014 @ 04 39 PM

E-mailPermalinkComments (1)

Прислал Сергей.

Описание: Приходят прайс-листы на телефоны в виде ”марка модель цвет1, цвет2, цвет3,…”

Надо представить в виде

“марка модель цвет1

марка модель цвет2

марка модель цвет3

…”

Сделал, любуйтесь.

telefon

Тут макросы могут потребоваться. Их надо включить.
Если чего непонятно — пишите.

Tags Tags: , , , ,
Categories: Бизнес-задача
Posted By: admin
Last Edit: 04 Jul 2009 @ 01 26 PM

E-mailPermalinkComments (0)

Сергей из Санкт-Петербурга попросил сделать табличку для учета рейсов. Что здесь есть:

  1. Ввод новой записи о рейсе. С автоподстановками, например, выбираешь компанию — подставляется контактное лицо, выбираешь водителя — подставляются паспортные данные.
  2. Сохранение с присваиванием уникального кода каждой записи.
  3. Распечатка. Не сохраните — не распечатаете.
  4. Просмотр записей из базы.

Вобщем, информация на листе “Информация”. Работа на листе “заявка”. Вот табличка: transport Макросы надо включить. Как же без них?

Tags Tags: , , , ,
Categories: Бизнес-задача
Posted By: admin
Last Edit: 05 Aug 2014 @ 12 16 PM

E-mailPermalinkComments Off

ha*@yandex.ru:

Можно ли делать запрос по двум (или более) ключевым полям, чтобы однозначно определить их комбинацию? Тогда как их задавать?

О.В.

Ключевое поле потому и называется ключевым, чтобы быть единственным.

Чтобы производить выборку по уникальному совпадению более чем одного параметра надо либо использовать специальную операцю текстового сложения СЦЕПИТЬ или использовать функцию по-русски называющуюся “БИЗВЛЕЧЬ”.

Смотрите примерчик про магазинчик с применением СЦЕПИТЬ :

magazin4ik

 

Tags Tags: , , , ,
Categories: Консультация
Posted By: admin
Last Edit: 11 Sep 2014 @ 09 48 PM

E-mailPermalinkComments Off
 06 Jan 2009 @ 1:39 PM 

h*@yandex.ru:

можно ли в Excel делать запросы от одной таблицы к другой (или от одной рабочей старницы к другой) (назовем их А и В), имеющих одинаковые ключевые поля. Задача:
заполнить строки в таблце А соотвествующими значениями из таблицы В, связанных по ключевым полям (т.е. имеющих одинаковые значения параметров в ключевых полях таблиц А и В соотвественно).

О.В.:

Да. Для этого используется функция ВПР.
Смотрите пример на использование этой функции. More »

Tags Tags: , , , , ,
Categories: Консультация
Posted By: admin
Last Edit: 19 Oct 2014 @ 09 13 PM

E-mailPermalinkComments (14)

Нурсултан:

Помоги, пожалуйста сделать следующий расчет для налога.

О.В.:

Пожалста. Смотри вложение.

 socnalogkz

Нурсултан:

Да, спасибо! Все верно!
Обязательно пополню твой яндекс-кошелек!
С наступающим, большого соц налога!

Tags Tags: , ,
Categories: Бизнес-задача
Posted By: admin
Last Edit: 04 Jul 2009 @ 01 26 PM

E-mailPermalinkComments (0)
 13 Apr 0201 @ 10:18 AM 

Excel для управления и анализа (Excel1)

http://vidyakin.ru/courses/adult/excel-dlya-upravleniya-i-analiza/

А в нем мы будем изучать, что такое «Ниагарский водопад», как на его основе и с помощью сводных таблиц мгновенно получать ответы на свои вопросы. Попутно научимся строить правильные диаграммы, поймем, как работать с датой и временем, как вытягивать нужные сведения из текста, решать задачи с ЕСЛИ, ВПР-ить и условно суммировать при помощи СУММЕСЛИМН. Ну не мне вам рассказывать — вы и так все знаете ;-)

Как всегда — 12 занятий по 90 минут.

Tags Tags: ,
Categories: Новости
Posted By: vidyakin
Last Edit: 13 Apr 2017 @ 12 37 PM

E-mailPermalinkComments Off