Добрый день!

Захотелось красоты и помпезности.

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

Добро пожаловать на http://vidyakin.biz

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

А*

Вот такая проблема:

существует первый лист “Итого” и 20 листов 1-20, задаю в “итого” =’001′!$F$43, соответственно ячейка F43 постоянная. А листы не протягиваются от 1 до 20, их приходится менять вручную.

Подскажите, пожалуйста, как сделать.. Как протянуть формулу чтоб листы “=001″ поменялись в периодичностью до 20ти и больше если нужно.

О.В.

Ловите решение

Заказы

А*

Спасибо большое, с помощью Вашей формулы разобрался, хотя с XLем давно дружу.

Всё получилось.

Первая колонка у меня гиперссылка на лист потом № и т.д.

подставил формулу, протянул и “О чудо!”

Теперь осталось прописать ручками все гиперссылки и нумерацию листов.

Теперь буду знать где спросить помощи и буду рекомендовать Вас как специалиста.

Еще раз спасибо.

Ловите формулу для расчета пенсионного, когда зарплата позволяет применять пониженную ставку расчета пенсионных начислений.

http://1drv.ms/1HTzc9n

Мой хороший знакомый и по совместительству хороший программист Валерий Ромашин на досуге написал несколько полезных плагинов для пользователей Excel.
Среди полезняшек:
1. Считывание курса валют с сайта ЦБ;
2. Число прописью;
3. И еще что-то добавится наверняка.
Весьма рекомендую.
http://architect.com.ru/officeplugins/

Меня часто спрашивают про то, как вычислить количество рабочих дней между датами. Ловите решение для любых дат от 2004 по 2014 год включительно.
Если захочется добавить другие периоды — пишите даты внизу на листе Календарь и проставляйте напротив каждой 0, если это выходной день и 1, если день рабочий. Также меняйте нолики на единички и наоборот в моем периоде, если несогласны с моей информацией о выходных и рабочих днях.

Затем на листе “Расчет…” в желтых ячейках вводите крайние даты и наслаждайтесь.

Вот, научился рисовать такие линеечки. :-)

Кому интересно — пользуйтесь:

Вот еще один шаблончик. Теперь для завучей.
Смысл такой: она построчно вводит данные в лист “источник” (заполняя старательно каждую клеточку). А потом на листе отчет лицезреет табличку и диаграммку по каждому классу/предмету по выбору.

Защитил листы для демонстрации того, в каком виде файлик отдал. Для тех, кто захочет покопать в решении сообщаю: “Пароль 1″.

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

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

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

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

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

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

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

r*0@mail.ru

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

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

Читать далее »

r*0@mail.ru

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

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

О.В.

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

p*@mail.ru

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

Читать далее »

e*@ukr.net

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

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

О.В.

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

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

О.:

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

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

О.В.:

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

О.:

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

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

О.*:

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

О.В.:

Ловите.

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

О.*:

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

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

26 ноября в 6-й раз в этом году запускаю открытую группу по моему курсу “Excel для управления и анализа”. Так что если Вы читаете мой блог в Калининграде — прошу в гости. Парочку мест еще осталось.
Подробности о курсе здесь: http://www.vidyakin.ru/improvement/Excel2/

В*

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

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

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

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

О.В.

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

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

В*

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

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

Нифига не понимаю в футболе и тем более в ихних чемпионатах. А вот файлик понравился. Однозначно. Так легко используют ВПР — просто загляденье. Смотрим, не забываем только снимать защиту с листов, чтобы формулы подглядывать. Затейники такие! Формулы скрыли.
Вот тут взято: http://www.ua-football.com/foreign/excel/4e0393de.html,
а скачивать от сюда: http://www.ua-football.com/tmp/uefa_coefficients_2011-12.xls

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

Ekaterina

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

О.В.

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

 

Ekaterina

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

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

А мне понравилось:
http://www.gazeta-unp.ru/reader.htm?id=29615
Здесь идет речь о том. что можно скормить в Excel кусок страницы и информация оттуда будет приходить в табличный лист постоянно с любой периодичностью.
Читать надо “между строк”. Кому курс валюты, а кому и мониторинг цен конкурентов по интернет-магазинам в яндексе. Сейчас как раз такую задачку решаем с программистом. Когда нарисуем — поделюсь впечатлениями.

В.

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

О.В.

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

В.

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

Светлана

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

О.В.

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

Светлана

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

О.В.

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

Светлана

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

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

Константин

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

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

Давно мучаюсь вопросом, существует ли в 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%

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

О.В.

Добрый день!

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

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

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

Удачи!

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

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

Баста! Зарекаюсь!

Господа и дамы студенты и студентки. Простите меня горемычного, но не буду я больше вам помогать.

Мне жутко не нравится делать за вас контрольные. Как будто я за вас хлебушек пережевываю. Это старичкам да младенцам беззубым так делают. А думать за молодых, активных, умных как-то совсем горестно.
Да, я люблю денежки. Но не настолько, чтобы делать эту неприятную работу.

Я с удовольствием вам объясню, как работает та или иная команда, решу для вас настоящую задачу, которая поможет вам в вашей жизни и в вашем бизнесе. Но только не вашу контрольную.

Каюсь, грешен, решил пару раз задачки студиозусам. А теперь все. Хватит.
Отныне только чистая польза и никаких подтасовок.

Начинаю честную жизнь. И вам советую.

o****v@mail.ru:

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

О.В.

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

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

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

Нужели, когда пойдет вторая волна кризиса? Не дай Бог. Придется заставить себя сесть и написать остальное, чтобы не провоцировать судьбу на такие “волшебные пинки”.
:-)

Владимир

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

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

О.В.

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

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

Первая.

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

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

Вторая.

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

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

Третья.

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

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

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

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

autoservice

q*@yandex.ru

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

О.В.

Добрый день!

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

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

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

Poker House

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

О.В.

Шаг первый.

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

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

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

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

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

Ответил?

Poker House

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

z**@Yandex.ru

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

О.В.

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

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

Помог?

z**@Yandex.ru

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

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

Вот тут подробное описание правил: 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 :)”.

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

Андрей С. (и не только) просит:

“Напишите инструкцию, как включить макросы в Excel 2003-2007.”

Мне не жалко

Excel 2003:

Меню “Сервис” — Подменю “Макрос” — Команда “Безопасность…” Устанавливаем уровни безопасности “Средняя” или “Низкая” — ОК Если “Средняя” — компьютер будет спрашивать, включать или нет макросы в книге. Если “Низкая” — просто будет включать макросы без предупреждения.

Excel 2007:

Слева вверху есть кнопка “Office”. Она круглая и красивая. Тыкаем по ней. Внизу меню выбираем кнопку “Параметры Excel”. Затем слева выбираем “Центр управления безопасностью”. Потом справа кнопка “Параметры центра управления безопасностью”. Потом слева “Параметры макросов”. Потом справа отмечаем “Включить все макросы” или “отключить все макросы с уведомлением”. Если просто включить все макросы, то при открытии будут открываться все макросы. Если выбран вариант открывать с уведомлением, то при открытии книги с макросами будет возникать предупреждение и вопрос про “Включить” или “Не включить”. Если в книге есть полезные макросы, значит, “Включить”. Ежели книга не должна содержать макросы, то, вероятно, кто-то внедрил в нее вредную программу и тогда “Отключить”.

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

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

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

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

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

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

…”

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

telefon

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

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

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

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

ha*@yandex.ru:

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

О.В.

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

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

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

magazin4ik

 

h*@yandex.ru:

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

О.В.:

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

Нурсултан:

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

О.В.:

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

 socnalogkz

Нурсултан:

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

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

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

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

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