Защита ячеек

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

"Задача поставлена так, в ячейку забиваю число "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)))

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

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

Назад

Сложный текст

Если количество знаков возвращаемое функцией ПСТР заранее неизвестно то можно попытаться их определить используя функцию НАЙТИ опираясь на какой либо признак. В приведенном ниже примере таким признаком является пробел " ".

=ДАТАЗНАЧ(ПСТР(C2;НАЙТИ("/";C2;1)+1;НАЙТИ(" ";C2;1)-НАЙТИ("/";C2;1)))

Здесь возвращаемое количество знаков определяется как разность между позицией пробел " " и позицией слеш "/".

В третьей строке функция НАЙТИ(" ";C2;1) возвращает "#ЗНАЧ!" потому, что не находит пробела. И таких строк может быть много. Что делать? Можно оставшееся исправить руками, если это в разумных пределах, или сохранить полученный результат, отсортировать по столбцу "Дата" и опять заняться поиском признака по которому возможно выделить дату. Например, если все даты в пределах 2008 года, то таким признаком вполне может быть "8".

Далее - совсем плохой текст...
Назад

Применение текстовых функций

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

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

=ЛЕВСИМВ(C2;НАЙТИ("/";C2;1)-1)

Здесь функция НАЙТИ используется для определения количества знаков входящих в номер счета, естественно должно выполняться условие что номер счета и дата всегда будут разделены слешем "/". Если это не выполняется надо поискать другие надежные признаки для разделения текста. Задача существенно упростится если количество знаков в номере счета всегда одинаково, но мы не ищем легких путей. От номера позиции слеша надо отнять "1", чтобы сам слеш в номер счета не попадал. Имеем результат в столбце "D".
Теперь займемся самой датой. Для выделения даты можно использовать функцию ПСТР. Она позволяет выбрать определенное количество знаков из середины текста. При этом количество знаков можно задать и больше реального ( вдруг где то дату написали в формате ДД.ММ.ГГГГ) но это возможно если после даты нет текста. Формула будет выглядеть так:

=ДАТАЗНАЧ(ПСТР(C2;НАЙТИ("/";C2;1)+1;255))

Здесь функции ПСТР может возвратить до 255 знаков начиная от позиции слеша +1, а реально возвращает столько знаков сколько есть. Однако возвращает она, дату в текстовом формате. Для ее преобразования в числовой код Excel используется функция ДАТАЗНАЧ, а чтобы совсем была похожа на дату надо ячейкам в столбце "D" задать формат даты.

Далее - сложный текст...