Добрый день!

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

Заказал себе новый цветной сайтик, где буду заниматься тем же — принимать вопросы по работе в 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

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

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

More »

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, затем период будет меняться. Как это сделать. В ячейке пыталась написать формулу но она показывает какую то чушь. Пыталась правда на сумме.

More »

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 для управления и анализа (Excel1)

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

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

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