Срок службы агрегата

"Здравствуйте!

Нашла Ваш сайт об экселе. Подумала - может Вы мне поможете, если хорошо знаете эту программу.

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

Есть некий агрегат. Дата выпуска агрегата - 25.03.1987 г. Срок службы, например, 35 лет, т.е. - до 25.03.2022 г.

Нужно высчитать:

- сколько агрегат отработал с даты выпуска (гг., мм.) до сегодняшнего дня

- остаток срока службы на сегодняшнюю дату (гг., мм.), т.е. сколько осталось до 25.03.2022 г.

Спасибо заранее за оказанную помощь."

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

Однако вопрос задан и на него надо отвечать, тем более, что человек ищет, как автоматизировать и облегчить работу с помощью глубоко уважаемого мной Excel! А это, есть ХОРОШО!!!

И так, рисуем табличку.

Для того чтобы посчитать сколько агрегат отработал нужно от текущей даты отнять дату выпуска и полученную разницу обработать формулами ГОД() и МЕСЯЦ() соответственно.

=ГОД(СЕГОДНЯ()-B3)-1900

Отсчет даты в Excel ведется с 1900 года, поэтому от полученного года надо отнимать "1900"

=МЕСЯЦ(СЕГОДНЯ()-B3)

Для того чтобы посчитать сколько агрегату осталось еще работать до конца срока службы нужно от даты окончания срока службы отнять текущую дату и полученную разницу обработать формулами ГОД() и МЕСЯЦ() соответственно.

=ГОД(C3-СЕГОДНЯ())-1900

=МЕСЯЦ(C3-СЕГОДНЯ())

Чтобы цифры в ячейках были "красивые", указанным ячейкам надо задать числовой формат.

Архив с файлом примера можно скачать.

Табель учета рабочего времени

"Добрый день. Есть время работы в месяц, и по этому календарю надо сделать табель учета рабочего времени, так как не все сотрудники работают одинаковое время, допустим Сидоров в понедельник отработал 6 часов, а Иванов 8 часов, вот и надо чтобы не считать на калькуляторе время, а просто ввел часы за месяц и табель уже раскидал время."

К письму прилагается табличка с табелем учета рабочего времени, фрагмент которой прилагаю.

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

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

Первая задача преобразовать значения типа "8\17" в число отработанных часов. Кроме этого 17-8=9, а из таблички видно, что рабочий день 8-часовой, значит сюда заложен еще и перерыв на обед - 1 час. А если работа с "8/11" 11-8=3, то перерыва на обед нет. С учетом дополнительных условий, задачу можно решить с помощью следующей формулы.

=ЕСЛИ(ЕОШ(НАЙТИ("\";B7;1));0;ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))-ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))-ЕСЛИ(ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))-ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))>4;1;0))

Общий смысл этой формулы заключается в том, что сначала находится и преобразуется в цифру время конца рабочего дня, далее - начало рабочего дня и берется между ними разница. Кроме этого проверяются логические условия на заполненность ячейки и длительность рабочего дня для определения наличия "обеда". И так, время конца рабочего дня возвращает фрагмент формулы.

ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))

Опорой для этого служит знак "\", который разделяет значения начала и конца рабочего дня. Далее аналогично находится время начала рабочего дня и берется между ними разница.

ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))-ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))

Учитывая, что функция НАЙТИ("\";B7;1) возвращает ошибку "#ЗНАЧ!" для пустых ячеек таблицы, т.к. не находит опорный знак "\", в начало формулы добавляем условие, которое позволяет возвращать "0" если ячейка таблички вообще не заполнена.

=ЕСЛИ(ЕОШ(НАЙТИ("\";B7;1));0;

Теперь осталось разобраться с перерывом на обед. Условие такое, если рабочий день длится больше 4 часов, то перерыв есть и 1 час надо вычитать из общего рабочего времени, в противном случае перерыва нет. Эту задачу решает следующий фрагмент формулы.

ЕСЛИ(ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))-ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))>4;1;0)

Полученную формулу копируем в дополнительные расчетные ячейки.

Теперь осталось разобраться с графой "Ночь", т.е. с учетом ночных часов работы, видимо они оплачиваются отдельно. Дело хорошее, строго соответствует КЗОТ, поэтому надо что то придумывать. Условие такое: ночные часы считаются с 22.00 и рабочий день заканчивается в 24.00. За основу возьмем выше приведенную формулу.

=ЕСЛИ(ЕОШ(НАЙТИ("\";B7;1));0;ЕСЛИ(ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))>22;ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))-22;0)-ЕСЛИ(ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))<22;0;ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))))

Фрагмент формулы

ЕСЛИ(ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))>22;

проверяет оканчивается ли рабочий день в ночные часы, т.е. после 22.00.

Фрагмент формулы

ЕСЛИ(ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))<22;

проверяет начинается ли рабочий день в ночные часы, т.е. после 22.00. В зависимости от результата проверки указанные фрагменты возвращают либо "0" либо часы начала и конца работы в ночное время. Далее берется разница этих значений.

ЕСЛИ(ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))>22;ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))-22;0)-ЕСЛИ(ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))<22;0;ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1)))

Полученную формулу копируем в дополнительные расчетные ячейки.

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

Ограничения к применению.

1. В табеле не менять стиль заполнения рабочего времени, особенно это касается знака "\" по нему идет поиск и преобразование текстовых значений часов в числовые. Часы могут заполняться двух или одно-значными цифрами.

2. Формулы рассчитаны на приведенный график работы т.е. рабочий день с 8.00 до 24.00, после 22.00 считается ночное время, если график выйдет за указанные значения возможны ошибки.

Архив с файлом примера можно скачать.

Защита ячеек

Пару дней назад получил вопрос.

"Задача поставлена так, в ячейку забиваю число "1" и блокируется строка и данные забитые в строке изменить нельзя
а если в ячейку забиваю число "0" то в строке можно работать и изменять данные. Как это можно сделать?"

Вообще, в Excel, существует стандартная процедура защиты листа или книги от изменений. Там можно установить пароль и тогда "враг" ничего не сможет изменить. Насколько я рассмотрел, это защита целого листа, отдельно защитить ячейку на листе не получается. Кроме этого, судя по вопросу, речь идет не о защите от "врага", который будет специально пытаться изменить содержимое ячейки. Думаю, речь идет о защите от "дурака", т.е. случайного, непреднамеренного изменения значения ячейки. Я часто использую такую защиту, когда рассылаю шаблоны для заполнения данных в нижестоящие подразделения, однако управлять защитой, используя значения другой ячейки, этого делать, не приходилось.

Рассмотрим сначала, как в принципе создать такую защиту.

Я для этого использую стандартную процедуру проверки вводимых значений. Стоя на определенной ячейке выбрать пункт меню "Данные" - "Проверка", откроется окно "Проверка вводимых значений". На вкладке параметры установить условия проверки согласно рисунка.

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

Конечно, эта защита имеет ряд недостатков: во-первых ее легко убрать, во-вторых она не защищает от копирования данных в ячейку, т.е. срабатывает только при ручном вводе с клавиатуры, однако в качестве защиты от случайного, непреднамеренного изменения вполне сгодится.

Чтобы управлять этой защитой необходимо найти способ изменения значения параметра "Максимум"=0. Это можно сделать при помощи функции ЕСЛИ(), образец на рисунке ниже.

Логика формулы

=ЕСЛИ(B1=1;0;257)

следующая. Если значение ячейки B1 равно "1" то формула возвращает "0", т.е. длина текста задается меньше нуля - ячейка A1 заблокирована для ввода. Если значение ячейки B1 равно "0" или "пусто" то формула возвращает "257", т.е. длина текста вводимого в ячейку A1 меньше 257 знаков и ячейка разблокируется. Проверяем.

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

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

Однако, при копировании, надо помнить, что адрес ячейки управления B1 будет изменяться точно так, как и при копировании формул. Если это не надо, допустим, вы хотите одной ячейкой управлять блокировкой целой таблицы, то адрес ячейки надо задать абсолютным $B1$. В общем, те же правила что и при копировании формул.

Архив с файлом примера можно скачать.

P.S. Кстати, этот вопрос напомнил мне одну старинную тему разработки еще 2004 года - управление списками.

Управление списками

Управление списками рассмотрим на том же примере, что и Применение списков.

Управление списками может понадобится в тех случаях, когда набор значений вносимых в одно поле зависти от значения ячейки в другом поле. В рассматриваемом примере это поля "Вопрос" и "Ответ". Список ответов заранее определен и ограничен, однако ответы зависят от вопроса.

      

Как поступить правильно? Для ответов на разные вопросы использовать разные поля с соответствующими списками? Это приведет к необоснованному расширению количества полей базы. Собрать все ответы в один список? Список получится большим, с ним будет неудобно работать. Кроме этого потребуется "напряжение ума" что бы выбрать ответ принадлежащий заданному вопросу - следовательно это приведет к ошибкам.

Лучше всего, сделать так, чтобы в поле "ответ" подставлялись имена списков в зависимости от значения ячейки в поле "Вопрос". Для этого в поле "Источник" надо ввести функцию ЕСЛИ(), возвращающую имена списков в зависимости от значения ячейки в поле "Вопрос".

Учитывая, что формула в поле "Источник" целиком невидна, привожу ее отдельно в полной красе.

=ЕСЛИ(F22="Будете голосовать";Ответ;ЕСЛИ(F22="За кого";Кандидаты; ЕСЛИ(F22="Процент явки";Ответ1;1)))

Внимание! Имена списков (Ответ; Кандидаты; Ответ1) в формуле в кавычки брать ненадо, т.к. это уже не текст, это имя списка! В конце формулы стоит "1", т.е. если ни одно условие не выполняется функция ЕСЛИ() в качестве имени списка вернет "1". Так как такого списка не существует, то он будет пуст. Это дополнительная страховка от ошибок.

Для тех кто превозмогая нечеловеческую усталость, безразличие и скуку все таки смог прочитать все три поста о СПИСКАХ - бонус - архив файла Excel с описанным примером, там есть еще макросы, поэтому не пугайтесь это из другой темы.

Применение списков

Применение списков рассмотрим на том же примере, что и Создание списков.

Становимся на ячейку A8 и задаем для неё условие на значение. Пункт меню "Данные" - "Проверка" - "Условия на значение - Список". В поле "Источник" вводим имя списка "Район". "Галочка" "Список допустимых значений" ограничивает допустимые значения для ячейки A8 списком "Район".

Таким образом, можно задать (или скопировать) условие для всех ячеек столбца A поля "Район, город". Для других полей списки создаются аналогичным образом.

При установке курсора на эти ячейки, справа появляется кнопка лифта, нажав на которую можно открыть список значений и выбрать нужное.

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

Далее рассмотрим вопрос управления списками.

Создание списков

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

Для начала открываем или создаем чистый лист книги Excel, в которой будет база данных. В ячейку A1 вносим наименование списка (например "Район" - не обязательно, но для "памяти" не помешает). Далее под наименованием списка в столбце A вносим значения, которые может принимать этот параметр.

Теперь, для удобства, этому списку надо присвоить имя. Выделяем список (за исключением названия) и в поле "Имя" вносим "Район".

Аналогично создаем другие списки, которые нам необходимы.

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

Вот, собственно, и все. Теперь рассмотрим применение списков.

Успеваемость студентов

Последнее время мне начали присылать письма с различными "каверзными" вопросами. Стараюсь на них отвечать. Этот пост - ответ на вопрос

Студенты сдают экзамены. Кто то сдал все на "отлично", кто то на "хорошо", кто то на "хорошо" и "удовлетворительно". Некоторые студенты умудрились сдать сессию с диапазонам оценок по экзаменам от 2 до 5. Как сгруппировать студентов по полученным оценкам за сессию?

Чтобы вопрос был более понятен привожу таблицу с примером.

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

=СЦЕПИТЬ(ЕСЛИ(СЧЁТЕСЛИ(B3:I3;5)>0;5;"");ЕСЛИ(СЧЁТЕСЛИ(B3:I3;4)>0;4;""); ЕСЛИ(СЧЁТЕСЛИ(B3:I3;3)>0;3;"");ЕСЛИ(СЧЁТЕСЛИ(B3:I3;2)>0;2;""))

Логика формулы следующая.

Фрагмент формулы возвращает "5" если в диапазоне оценок полученных студентом есть оценка "5" и пусто, если такой оценки нет.

СЧЁТЕСЛИ(B3:I3;5)>0;5;"")

Аналогично анализируется наличие других оценок от 2 до 4.

Далее с помощью функции СЦЕПИТЬ(), полученные значения сбираются уникальную в комбинацию цифр для каждой уникальной комбинации оценок. (см. столбец "код текст")

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

Функция СЦЕПИТЬ() возвращает код в текстовом формате, если нужен числовой формат то можно преобразовать код в числовой формат при помощи функции ЗНАЧЕН().

=ЗНАЧЕН(СЦЕПИТЬ(ЕСЛИ(СЧЁТЕСЛИ(B3:I3;5)>0;5;""); ЕСЛИ(СЧЁТЕСЛИ(B3:I3;4)>0;4;"");ЕСЛИ(СЧЁТЕСЛИ(B3:I3;3)>0;3;""); ЕСЛИ(СЧЁТЕСЛИ(B3:I3;2)>0;2;"")))

Пациенты, получившие лекарственные препараты

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

Приведенный ниже пример подтверждает выше сказанное. Однако он интересен методом решения задачи.

Задача. Необходимо подсчитать, сколько пациентов получило по бесплатным рецептам в 2010 году лекарственные препараты, заданные в перечне вышестоящего министерства.

Осложняющим фактором является то, что отпущено рецептов 58892, а заданный перечень лекарственных препаратов составляет 630 позиций. Понятно, что в ручную можно считать до конца 2011 года, про ошибки я уже не говорю.

Пример базы исходных данных приведен в таблице на рис 1.

Рис. 1

Поле UKP - уникальный код пациента в системе (в строгом соответствии с Федеральным законом Российской Федерации от 27 июля 2006 г. N 152-ФЗ О персональных данных, данные обезличены). Поле SN_LR - серия и номер рецепта, поле MHH - международное непатентованное наименование лекарственного препарата. В таблице уже отобраны только те наименования лекарственных препаратов, которые заданы в перечне. Отбор выполнен с помощью функции ВПР.

Шаг 1. Через сводную таблицу сгруппируем МНН, чтобы получить список уникальных (т.е. неповторяющихся) значений, в таблице на рис. 2.

Рис. 2

В таблице, в поле "Итог" приведено количество значений по полю МНН, т.е. сколько раз всего по всем рецептам отпускался лекарственный препарат. Это нам не поможет, т.к. один пациент мог получать препарат несколько раз, а другой ни одного раза.

Шаг 2. Надо сгруппировать отпуск препаратов по пациентам. Для этого делаем еще одну сводную таблицу, рис. 3.

Рис. 3

Теперь у нас в таблице отпущенные лекарственные препараты привязаны к конкретному пациенту, но как посчитать пациентов, которым был отпущен конкретный препарат из перечня в таблице на рис. 2?

Шаг 3. Для решения этой задачи используем функцию СЧЁТЕСЛИ, пример в таблице на рис. 4.

Рис. 4

В функции СЧЕТЕСЛИ($F$5:$F$26195$;A5) диапазон поиска значений $F$5:$F$26195$ задан абсолютным, что бы при копировании формулы диапазон поиска не менялся. Искомое значение (критерий поиска) A5 задан относительной ссылкой, что бы при копировании функции адрес ссылки изменялся. Т.о. Функция возвращает количество сгруппированных по пациентам наименований лекарственных препаратов. Т.к. для каждого пациента конкретный препарат встречается только один раз, то это и есть количество пациентов, которым был отпущен конкретный препарат.

Думаю, что есть и другие варианты решения задачи, но я сделал так.

Скачать архив с примером можно здесь.

Уникальность записей РЗН

Продолжая предыдущую тему необходимо отметить, что два раза в месяц, на 1-е и 15-е число Росздравнадзор публикует Информационное письмо "О государственной регистрации предельных отпускных цен на ЖНВЛС", которое содержит кроме собственно самого письма (формат PDF) еще и реестр зарегистрированных лекарственных средств на дату публикации. Данный реестр выполнен в формате Excel.

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

Вы спросите зачем нам поле с уникальными данными? Ответ прост - только используя это поле можно будет связать две базы данных: реестр Росздравнадзора (РЗН) и свою базу, например в 1С и далее наращивать ее по мере добавления лекарственных средств в реестр РЗН. Т.е. мы исходим из предположения что двух одинаковых записей в реестре РЗН быть не должно, т.к. это будет означать что одно и тоже лекарственное средство с совершенно одинаковыми параметрами регистрации зарегистрировано два раза. В чем смысл?

Обращают на себя внимание два поля "UniqNx (PackNx)" и "Штрих-код" здесь можно поискать уникальность.

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

1. Выделить всю таблицу, включая заголовки и присвоить этой области имя, лучше всего "База_данных". РЗН до этого не додумался, придется дорабатывать.

2. Добавить лист в файл и на нем создать сводную таблицу для поля проверяемого на уникальность, в нашем случае займемся полем "UniqNx (PackNx)".

Обращаю внимание, в области данных нужно выбрать "Количество значений" по полю "UniqNx (PackNx)", тогда в сводной таблице все одинаковые значения поля "UniqNx (PackNx)" сгруппируются и будет указано их количество.

3. Получаем результат.

Теперь если по столбцу "B" поставить автофильтр можно увидеть сколько одинаковых значений поля "UniqNx (PackNx)" встречается в таблице.

Да, результат не радует. В поле "UniqNx (PackNx)" отдельные значения повторяются от 2 до 7 раз. Какие это значения, можно увидеть отобрав по фильтру количество повторений.

На мой вопрос специалистам РЗН был получен ответ. Поле "UniqNx (PackNx)" имеет уникальные значения для зарегистрированных лекарственных средств без учета производителя. С учетом производителя уникальность пропадает.

Ладно, у нас еще осталось поле "Штрих-код", уж оно то должно быть уникальным. По сути штрих-кода, два разных товара не могут иметь один и тот же штрих-код, а два одинаковых зачем регистрировать два раза. Анализируем поле "Штрих-код" на уникальность по выше описанному методу и получаем результат.

Как видно поле "Штрих-код" тоже не обладает уникальностью и таких позиций 63 (смотри файл). Мой удивленный вопрос к специалистам РЗН не вызвал никаких эмоций. Ответ был примерно таков: "Мы тут работаем в режиме ошпаренной кошки и поэтому возможны технические накладки. Дайте срок все исправим." К слову сказать движение в этом направлении есть, в предыдущем реестре на 31.04.2010 неуникальных позиций по полю "Штрих-код" было 111, так что прогресс на лицо. Радует то, что есть понимание необходимости уникальности указанного поля, будем ждать.

А что, впрочем, ждать? Можно попробовать с помощью функции СЦЕПИТЬ() связать поле "Штрих-код" или поле "UniqNx (PackNx)" с еще с другим и получить уникальное поле. Но это уже на Ваше усмотрение, уважаемые пользователи редактора Excel и мученики данных от Росздравнадзора РФ. О какой всеобщей автоматизации и информатизации может идти речь, если источник информации совершенно не задумывается как эта информация будет обрабатываться теми, для кого она предназначена. Где наша единая база данных? Где единые коды для лекарственных средств? Как без этого отойти от ручной обработки и бесконечного, ручного создания таблиц перекодировки для увязки информации из разобщенных баз данных!!! Понятно, что все это делается не в один день, но начинать когда-то надо и основа всего единые коды, это скелет, а мясо нарастет!!!

Обработка данных РЗН

Думаю, эта проблема будет интересна всем, кто так или иначе связан с реализацией лекарственных средств.

История вопроса.

С начала февраля 2010 года началась регистрация цен на жизненно необходимые и важнейшие лекарственные средства. Регистрацию проводит Росздравнадзор РФ. На начало мая 2010 года уже зарегистрировано 6281 наименование. Естественно что такой объем данных крайне проблематично обрабатывать в ручную. Хочется создать некое подобие базы данных, которую потом использовать в качестве опорно-справочной информации для работы самых разных бухгалтерских и складских программ. Первый шаг на этом пути - получение указанных данных в формате Excel (о DBF мы даже и не мечтаем). Видимо народные чаяния дошли до ушей наших чиновников и где то с начала марта они стали публиковать вместе с приказом в формате PDF и файл Excel с данными регистрации. Вот счастье-то какое, но радоваться оказалось рано! Дело в том что в файле Excel цены проставлены в текстовом формате и никаким образом математические операции делать с ними нельзя. Учитывая, что там бывает до 800 строк записей, в голове начала бродить мысль как автоматизировать процесс преобразования "текстовых" цен в "числовые" и свести затраты человеко/часов к минимуму.

И так, рассмотрим решение проблемы на реальном примере.

Берем последний приказ РЗН № 3572 от 29.04.2010 года. Там в приложении есть файл 3572.xls с табличкой на 104 строки. Привожу фрагмент содержимого

Как видите цена действительно не в цифровом формате.

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

Жмем "Заменить все" и получаем результат.

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

Далее, зная позицию пробела, можно отобрать все левые символы до позиция пробела, см. строку формул на рисунке.

Теперь осталось только преобразовать текст в числовой формат с помощью функции "ЗНАЧЕН", см. строку формул на рисунке.

Для тех, кто уже "набил руку" в Excel, не составит труда объединить все вышеописанные шаги в одну формулу, см. строку формул на рисунке.

Файл с приведенными выше формулами можно скачать для примера.
Желаю всем успехов в преодолении последствий непрофессионализма вышестоящих чиновников!!!

функции И и ИЛИ

Функции И и ИЛИ являются теми начальными кирпичиками на которых базируется вся логическая математика, а по большому счету, вся цифровая техника начиная от простейших схем управления и кончая суперсовременным компьютером. Синтаксис функций достаточно прост и позволяет ввести до 30 условий, сочетание которых определяет возвращаемое значение "ИСТИНА" или "ЛОЖЬ". Логика работы функций представлена ниже.

=И(Условие1;...;Условие30)

=ИЛИ(Условие1;...;Условие30)

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

=ЕСЛИ(И(C1=1;D1=1);1;0)

=ЕСЛИ(ИЛИ(C1=1;D1=1);1;0)

Деление на "0"

Часто функцию ЕСЛИ удобно применять для исключения нарушения математических правил при вычислении. Например, в столбце С мы имеем результат деления столбца В на столбец А. Формула в столбце С выглядит так:

=В1/А1

Если А1=0, получим ошибку, т.к. согласно всем математический канонам деление на 0 невозможно и наша табличка будет иметь вид:

Чтобы справится с этой проблемой достаточно немного изменить формулу

=ЕСЛИ(А1=0;0;В1/А1)

и наша табличка приобретет совершенно другой вид:

Функция ЕСЛИ

Функцией ЕСЛИ удобно пользоваться для создания логических условий при отборе данных. В классическом варианте применения формула выглядит следующим образом:

=ЕСЛИ(A1=B1;0;1)

A1=B1 - это условие, которое может иметь два значения ИСТИНА или ЛОЖЬ. Если условие выполняется, т.е. равно ИСТИНА, тогда функция ЕСЛИ возвратит значение 0, если - ЛОЖЬ тогда 1. Функция удобна тем, что как условия, так и возвращаемые значения могут быть заданы другими функциями а, следовательно, можно создавать достаточно сложные логические конструкции.
Функцию ЕСЛИ можно вкладывать внутрь друг друга до 7 раз. Например:

=ЕСЛИ(A1=B1;0;ЕСЛИ(A1>B1;"A1 больше B1";ЕСЛИ(A1<B1;"A1 меньше B1";"Так не бывает")))

Логический смысл данной конструкции можно сформулировать так: если A1=B1 тогда 0, если A1>B1 тогда функция возвращает сообщение A1 больше B1, если A1<B1 тогда A1 меньше B1, если ни одно из выше приведенных условий не выполняется (чего в принципе быть не может) тогда Так не бывает. Пример таблицы:










Пример применения функции ЕСЛИ для преобразования из двоичной в десятиричную систему исчисления.

=СУММ(ЕСЛИ(C3=1;8;0);ЕСЛИ(D3=1;4;0);ЕСЛИ(E3=1;2;0);ЕСЛИ(F3=1;1;0))

Совсем плохой текст

Даже если год может быть 2008 и 2007, а в номере счета могут попадаться цифры "7" и "8", эта проблема решаема, правда формула существенно усложнится. Зато какая красивая!

=ДАТАЗНАЧ(ПСТР(C2;НАЙТИ("/";C2;1)+1;ЕСЛИ(ЕОШ(НАЙТИ("08";C2;1)-НАЙТИ("/";C2;1));НАЙТИ("07";C2;1)-НАЙТИ("/";C2;1)+1;НАЙТИ("08";C2;1)-НАЙТИ("/";C2;1)+1)))

Здесь для определения года добавлена конструкция функций ЕСЛИ и ЕОШ которая определяет ошибку если год не 2008 и возврашает номер позиции уже по двум цифрам "08" или "07".

Задача еще более усложняется, если нет никакой уверенности, что в номере счета не попадется сочетание "08" или "07". Но и здесь решение может быть найдено. Например, исключить из определения позиции номер счета в принципе. Для этого функция НАЙТИ должна искать не с первой позиции текста, как это было раньше НАЙТИ("/";C2;1), а от слеша, который разделяет номер счета и дату (выделено красным), и функция будет выглядеть так:
НАЙТИ("/";C5;НАЙТИ("/";C5;1)), а в общем конструкция станет еще "красивше".

=ДАТАЗНАЧ(ПСТР(C5;НАЙТИ("/";C5;НАЙТИ("/";C5;1))+1;ЕСЛИ(ЕОШ(НАЙТИ("08";C5;НАЙТИ("/";C5;1))-НАЙТИ("/";C5;НАЙТИ("/";C5;1)));НАЙТИ("07";C5;НАЙТИ("/";C5;1))-НАЙТИ("/";C5;НАЙТИ("/";C5;1))+1;НАЙТИ("08";C5;НАЙТИ("/";C5;1))-НАЙТИ("/";C5;НАЙТИ("/";C5;1))+1)))

Тем не менее эта формула работает и имеет перспективу дальнейшего развития в плане добавления условий на поисковые позиции.

В общем, многое зависит от Вашей фантазии, упорства и желания.

Назад