• Полный экран
  • В избранное
  • Скачать
  • Комментировать
  • Настройка чтения
Excel таблица - качай, читай инструкцию, пользуйся. 04.03.2011 - Обновлены ссылки для скачивания.

Синхронизация публикаций - в помощь сетератору. Работа со списками в Excel

  • Размер шрифта
  • Отступ между абзацем
  • Межстрочный отступ
  • Межбуквенный отступ
  • Отступы по бокам
  • Выбор шрифта:










  • Цвет фона
  • Цвет текста

Синхронизация публикаций – в помощь сетератору. Работа со списками в Excel

Синхронизация публикаций – в помощь сетератору. Работа со списками в Excel.

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

    Статья разбита мною на четыре раздела:

  1. Зачем я это сделал?
  2. Как это работает?
  3. Как я это сделал?
  4. Как этим пользоваться?


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

Зачем я это сделал?

Мой сетевой стаж (на момент написания этой статьи) три с половиной года, за это время накопилось около сотни произведений, в разной степени активен на пяти Литсайтах, на некоторых просто ради конкурсов, другие страницы содержатся как резервные. Логика такова – чем больше у меня страниц, тем проще доказать авторские права, плюс любой портал временами лихорадит, потому лучше иметь страховку. Но вернёмся к теме. На эти пять порталов публикации добавлялись бессистемно. Т.е. в определённый момент возникла необходимость отслеживать наличие публикаций на той или иной веб странице. Вести статистику вручную мне всё как-то не давалось. То забуду пометить то, что добавил, то просто не хватало времени, а потом приходилось заново отслеживать вручную накопившиеся изменения. Тогда и родилась идея создать таблицу, которая автоматически анализировала бы списки публикаций и указывала каких, и на каком сайте не хватает.

Как это работает?

В Excel файле создано шесть листов (не считая Инфо). Сп_Осн (Список Основной) является «корневым» и наиболее полным списком моих произведений, формируется постепенно, может дополняться, так же можно произвести сортировку, т.к. порядок публикаций в списке не повлияет на конечный результат. В нём и происходит сведение всех данных.


Остальные листы - каждый соответствует одному порталу, содержит список произведений на нём (портируется вручную)


Каждый содержит формулы вычислений (столбцы с последними скрыты, чтобы не «рябило в глазах», только в листе proza.kz они намеренно оставлены видимыми, для наглядности).


Формулы содержатся в столбцах С, E, F, G, H, они нужны для отображения конечного результата на листе Сп_Осн, а в D, копия «корневого списка» лишь для наглядности, в остальных листах D пуст. Количество столбцов и формул сокращено по сравнению с предыдущим вариантом таблицы (фото ниже), вам представлена редакция версии 1.1 и вероятно не конечная.


В столбце C реализован поиск дублей (одинаковых названий) в «дочерних» списках и поиск новинок, произведений, ещё не внесённых в основной список. Если произведение повтором не является и есть в основном списке, то помечается плюсом. Счётчики запрятаны в F, G и H, а в ячейках C1 и C2 они уже отображаются с комментариями.


В столбце E задаётся правило, при котором «список портала» сравнивается с основным списком, и в «корневом» листе (Сп_Осн) в соответствующем столбце отображается «Да» если произведение опубликовано на сайте, или короткое тире «-», чтобы ячейка выглядела пустой, но таковой не являлась в знак подтверждения работы «анализатора».


Т.е. в Сп_Осн полностью копируется столбцы E из всех остальных листов.

Как я это сделал?

►► пропустить раздел

Открыл новый файл Excel и начал эксперименты. Функции ЕСЛИ явно не хватало, на ней одной вышло бы слишком громоздко, если вообще получилось. Стал просматривать другие функции из списка, выбрав в панели инструментов, ВставкаФункция. В появившейся панели кроме группировки по категориям я с восторгом обнаружил окно Поиск функции. По ключевому слову «Поиск» и нашлась функция ВПР. Как гласит описание - Ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. После экспериментов с ней оказалось, что это то, что нужно.

схема формулы:

ВПР(искомое_значение;таблица ;номер_столбца ;интервальный_просмотр)

«интервальный_просмотр» я выбрал «ЛОЖЬ», т.к. только в этом случае сортировка обрабатываемого списка не требуется.

Так выглядит часть кода для поиска совпадений и отображения одинаковых значений в обоих списках с сортировкой в порядке основного списка.

=ВПР(Сп_Осн!B:B;B:B;1;ЛОЖЬ)
=ВПР(Сп_Осн!B:B;B:B;1;ЛОЖЬ)
=ВПР(Сп_Осн!B:B;B:B;1;ЛОЖЬ)…

Т.е. берётся текущая ячейка из «Основного списка» (столбец B) и осуществляется поиск по всему списку в столбце B. Если значение есть в обоих списках, то оно отображается в текущей ячейке, в которой находится формула.

Важно - поиск ведётся по полному совпадению, поэтому необходимо единообразное написание.

Но если значение не найдено, то отображается код ошибки #Н/Д. Эта ошибка и стала камнем преткновения. Перевести название произведения в слово «Да», а #Н/Д, в тире оказалось сложной задачей. Чтобы я не придумывал, #Н/Д упрямо не хотело становиться значением, с которым можно было бы работать дальше. Собственно, можно было бы оставить всё как есть, и ограничиться одной этой формулой, но воспринимать информацию в таком виде неудобно – названия могут быть длинными, а от #Н/Д «рябит».
Поиск функции, которая бы превращала ошибку в другое значение привёл меня к функции ТИП.ОШИБКИ, она трансформирует ошибку в число, которому соответствует её код. Поэкспериментировав немного, я выяснил, что и в каскаде формул отображается первая ошибка, она имеет приоритет, даже если были ещё. Это и было моей проблемой во время попытки создать правило трансформации #Н/Д в тире. С той же проблемой я столкнулся применив одиночную формулу ТИП.ОШИБКИ#Н/Д превращалось в число 7, но названия в списке через эту формулу вызывали новую ошибку ЗНАЧ!. Получился замкнутый круг – ошибка превращалась в число, а данные в ошибку, и все последующие шаги лишь давали их чередование.
И вот тут у меня возникла (пардон за бахвальство) гениальная идея – искусственно симулировать ошибку для найденного значения (имени). Зная, что приоритетна первая ошибка, я превратил этот недостаток в достоинство. Я поделил на ноль результат, полученный формулой ВПР. Таким образом добился двух ошибок – если значение не найдено #Н/Д, если есть название произведения, то отображается ошибка ЗНАЧ! Эти две ошибки преобразуются через функцию ТИП.ОШИБКИ в цифры 7 и 3, а затем в «Да» и «-». Я сэкономил пространство и объединил все функции в столбце E.

Вот «формула успеха»!:

=ЕСЛИ(ЕПУСТО(Сп_Осн!B:B);"";ЕСЛИ(ТИП.ОШИБКИ(ВПР(Сп_Осн!B:B;B:B;1;ЛОЖЬ)/0)=7;"-";"Да"))

Причём =ЕСЛИ(ЕПУСТО(Сп_Осн!B:B);""; потребовалось чисто из эстетических соображений, чтобы отображалось пустое поле вместо тире в ячейках, в местах, где основной список пуст.

Это конечный результат моих мытарств, длившихся три вечера подряд. Точнее ко второму я уже доводил таблицу до ума и добавил функцию поиска дублей и новинок, используя всё те же ВПР, ТИП.ОШИБКИ, деление на ноль и ЕСЛИ с «сотоварищами».

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

=ЕСЛИ(ЕПУСТО(B:B);"";ЕСЛИ(ТИП.ОШИБКИ(ВПР(B5;B$3:B4;1;ЛОЖЬ)/0)=3;"<<_D";ЕСЛИ(ТИП.ОШИБКИ(ВПР(B:B;Сп_Осн!B:B;1;ЛОЖЬ)/0)=7;"<_NEW";"+")))

ВПР(B:B;Сп_Осн!B:B;1;ЛОЖЬ) отвечает за поиск новинок, теперь уже значения из «Дочернего списка» сравнивается с «Основным», и если значение не будет найдено, то появится код ошибки, который через ЕСЛИ(ТИП.ОШИБКИ(ВПР_#Н/Д)/0)=7; преобразуется в информацию  <_NEW  в столбце C.

ВПР(B5;B$3:B4;1;ЛОЖЬ) отлавливает дубли, а ЕСЛИ(ТИП.ОШИБКИ(ВПР_ЗНАЧ!)/0)=3; аналогично преобразует код ошибки в  <<_D  в столбце C, причём приоритетность у значения выше чем у  <_NEW , т.е. если новый текст будет повторятся отобразится значение дубля. Плюс отображается если значение из дочернего списка (столбец B) не является новым или повтором.

Формула поиска дублей далась немного сложнее остальных. Сначала я нагородил «многоэтажную» и даже занял несколько столбцов для этого. Но всё оказалось гораздо проще.

=ВПР(B3;B$1:B2;1;ЛОЖЬ)
=ВПР(B4;B$1:B3;1;ЛОЖЬ)
=ВПР(B5;B$1:B4;1;ЛОЖЬ)
=ВПР(B6;B$1:B5;1;ЛОЖЬ)
=ВПР(B7;B$1:B6;1;ЛОЖЬ)…

Алгоритм работы: Значение из текущей строки столбца B сравнивается со всеми предыдущими, начиная с B1, т.е. формула из ячейки C6 сравнивает значение в B6 просматривая интервал от B1 до B5. Запись B$1 запрещает Excel модифицировать это значение при копировании формулы в следующие строки. Ячейка B1 это название портала, а B2 дата проверки, но ничего страшного, вероятность совпадения с названием мало, зато формула всех ячеек приведена к единому стандарту.

Важно - все листы (кроме Сп_Осн) защищены от модификации, чтобы случайно не удалить нужные формулы, к редактированию доступен столбец B:
В первую ячейку вводится заголовок списка, а во вторую дата проверки, далее сам список.
Если требуется модификация таблицы под свои нужды и уверены в своих силах, то пароль на снятие защиты 0000 (Панель инструментов – Сервис – Защита – Снять защиту листа).
В данный момент прописано двести строк до 202-й включительно. Если потребуется больше, просто выделите любую строку между 202-й и 3-й, скопируйте и вставьте, выделив, нужное количество строк ниже.


Ну, и осталась «косметика»:

В H1 и H2 прописаны счётчики, которые дублируются в C1 и C2 уже с текстовыми пояснениями, а считают они столбцы F и G, в которых отображается единичка при наличии дублей или новинок, т.е. ссылаются на столбец C.

На H1 и H2 ссылаются счётчики в «корневом» списке, они уже подсчитывают общее количество дублей и новинок во всех «дочерних» списках. Собственно это почти всё. Если требуется большее количество листов, то просто копируется и переименовывается один из «дочерних», затем останется лишь подредактировать в «корневом» счётчики и добавить новый столбец.


Как этим пользоваться?

Почти каждый литпортал предоставляет список произведений, чаще он на авторской странице, но копирование может быть затруднено наличием лишней информации, такой как жанр, аннотации, и т.п., а иногда список разделён на несколько страниц. Проблему можно решить, перейдя в папку статистика (если сервис позволяет), так на сайте Литсовет и Самоиздат списки в статистике предоставлены в табличном виде. Можно выделить всё и потом удалить лишние цифры в промежуточном текстовом документе. Т.е. прежде чем добавить список в Excel требуется предварительная подготовка.

Важно – всегда копируйте списки в Excel через текстовый документ. Промежуточное сохранение в обычном блокноте, удаляет исходное форматирование и лишние объекты, к примеру – картинки, значки, смайлики и т.п. Это гарантия, что не будут задеты соседние столбцы с формулами и всё скопируется правильно. Если копирование было произведено из Word таблицы, то следует удалить лишние табуляторы. Подробней как это делать я опишу в статье «Работа со списками и таблицами в Microsoft Word».

Вставляем полученный с сайта список в лист файла Excel, соответствующий нужному нам порталу (в столбец B). В первую ячейку вводится заголовок списка, а во вторую дата проверки, далее сам список. Если требуется, переименовываем или создаём путём копирования новый лист. Для это кликаем на ярлыке листа правой кнопкой мыши и выбираем Переместить/скопировать и ставим галочку создать копию, так же выбираем место перед каким листом он должен находится.


Если осуществили копирование (не забудьте переименовать скопированный лист и сменить цвет ярлычка), то в листе Сп_Осн следует создать очередной столбец (можно назначить ему определённый цвет), в котором следует прописать в первой ячейке ссылку на E1 нового листа и методом копирования заполнить ею весь столбец.


Если при этом дата будет отображаться некорректно, то, выделив вторую ячейку, или целую строку кликом правой кнопки мыши вызываем меню Формат ячеек и в первой вкладке Число устанавливаем Дата.


Затем необходимо поправить счётчики в ячейках AA1 и AA2, добавив в формулы счёта ссылки на ячейки H1 и H2 нового листа:

=СУММ(litsovet.ru!H1;samlib.ru!H1;'add-text.com'!H1;litprichal.ru!H1;proza.kz!H1;NEW!H1)

Достаточно отредактировать только один счётчик, а потом выделить ячейку, кликнуть копировать и вставить в AA2 (клавиши Ctrl C и Ctrl V), чтобы получить вторую формулу автоматически (для того они один под другим и размещены):

=СУММ(litsovet.ru!H2;samlib.ru!H2;'add-text.com'!H2;litprichal.ru!H2;proza.kz!H2;NEW!H1)

Так же можно выделить первые ячейки в Сп_Осн с названиями списков, скопировать их в блокнот, а потом автозаменой (клавиши Ctrl H) преобразовать табуляторы в !H1; и подставить в конец получившейся строки !H1), а в начале =СУММ(.



Обратите внимание на запись 'add-text.com'!H1, из-за тире, которое Excel понимает как минус, название заключено в одинарные кавычки, если будете редактировать формулу счётчика вручную, то лучше не использовать в названиях листов математических знаков, иначе можете долго биться над ошибкой, упустив из виду эти кавычки.

Основной список полезно сформировать из списка с сайта, на котором наиболее полное собрание сочинений. У меня это Литсовет. В дальнейшем я по результатам вычислений таблицы дополняю его, просматривая новые в остальных списках. Последовательность списка значения не имеет, можно даже отделять некоторые публикации, собирая в группы, и оставлять комментарии или заголовки. Так у меня отделены статьи от основной группы. Для слова «Статьи:» также работают правила поиска, но ни на одном сайте такого «произведения» нет и это никак не мешает мониторингу.



Важно – все глобальные преобразования основного списка, такие как сортировка, группировка и пр. так же лучше осуществлять во внешнем текстовом файле, чтобы это не повлияло на формулы и перекрёстные ссылки в Excel.

Обратите внимание на ячейки A1 и A2 в Сп_Осн, они информируют о наличии дублей (повторов) в списках произведений, и их количестве. Слово «Добавить» говорит о «новых» произведениях, не включенных в основной список, т.е. которые следует в него добавить. А «Есть дубли» информирует о повторах в «дочерних» списках. Отследить повторы и новинки в листах публикаций можно так же по ячейкам C1 и C2, которые, в отличие от Сп_Осн, уже указывают на их наличие в этом конкретном списке, а так же по всему столбцу C, в котором напротив интересующих нас произведений отображается  <<_D  и  <_NEW. Дубли, помеченные  <<_D , можно удалить, а новые добавить в основной список.


PS: Я не программист, потому не претендую на изящество и особою правильность воплощения этого файла - главное, он работает. Надеюсь, таблица принесёт вам пользу, а может полученный опыт работы с ней сподвигнет на самостоятельное освоение Excel и поможет в создании своих таблиц. Если заметите ошибки или недоработки, то буду рад замечаниям, советам и даже пожеланиям.

Особая благодарность создателям Excel и vdasus, за подсказки при редактировании версии 1.1 В планах реализовать функцию поиска по приближённому значению, и возможны иные доработки, необходимость в которых выявится во время эксплуатации.

Если будут пожелания, vdasus обещал написать свой вариант решения этой задачи, как её решал бы профессиональный программист (позже тут может появиться ссылка).

Скачать таблицу с vdasus.com


Если по каким-либо причинам ссылка не работает, то свежий файл можно скачать с сайта моего друга.

Скачать свежую таблицу

Ремарка от vdasus:
Если у кого-то английский Excel, то, достаточно скачать файл и открыть его. Английский Excel все формулы “переводит”.

Cвидетельство о публикации 377256 © Wassillevs 05.02.12 16:10

Комментарии к произведению 4 (5)

Точно, шедевр!

хм. интересно

очень

я ведь только в другом качестве ёксель всегда использовал, и даже считал себя довольно продвинутым)

Я тоже себя считаю проДвинутым, правда, приставка "про" не очень чёткая. (с) :)

Тут ниже пишут, что не скачивается табличка, добавил ещё ссыль на свежий файл.

http://dimonk.my1.ru/load/0-0-0-33-20

И вообще "ёксель" не просто большой калькулятор, в нём такого нагородить можно. Я в нём подготовил файл для конкурса моего многострадального "Мастера Экспромта", теперь он сам считает баллы турнирной таблицы, и конвертирует в ХТМЛ, то, на что я убивал минимум 5-6 часов теперь делается за секунды. Правда, над таблицей я возился несколько недель. Но, зато какой опыт приобрёл! :)

интересная тема, Вась ) никогда не думал, что ёксель-моксель такие штуки могёт )

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

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

«а даунлоад почему запаролен? ты тему продаешь что ли? »

Нет. Какие выгоды? Я же не изобрёл Excel.

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

http://dimonk.my1.ru/load/0-0-0-33-20

Должно скачаться нормально, файл в Rar архиве. Отпишитесь, если можно - Нормально ли скачивается?

Ага, все скачивается! ) Теперь будем разбираться, что ты там наворотил? Я так понимаю, что сперва надо твои данные зачистить, а потом уже "по образу и подобию" ))) У меня как раз сейчас назрела потребность в систематизировании кое-каких данных )

Видишь, Барамунда тоже заценил ) Твои наработки интересны, так что ты делись с людьми и впредь! Это очень позитивно! )

Рад помочь. В первом листе есть неболшая инструкция. Я теперь многие списки сравниваю им, на конкурсах удобно. Есть недостаток, поиск по точному совпадению, можно сделать по приблизительному, слегка отредактировав формулу, но, тогда списнку нужно будет сортировать по-порядку.

Будут ешё некоторые фишки. первая на очереди - методы экспорта списка произведений с сайтов. (пригодится для работы с этой таблицей)

  • Francua
  • (Аноним)
  • 02.03.2012 в 22:39
Комментарий неавторизованного посетителя

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