Excel несомненно обладает богатейшим инструментарием по обработке больших массивов информации. Много трудов написано по этой замечательной программе. Я же хочу остановиться на двух практически полезных функциях и их возможном применении при работе с 1С.
Несмотря на громкое название заголовка статьи (это всего лишь шутка), материал не претендует на оригинальность и открытие каких-то сокровенных тайн. Естественно, что эти две описываемые функции не являются панацеей. Но по опыту могу сказать, что они довольно часто выручают в различных ситуациях.
1. Функция ВПР (VLOOKUP). И ее разновидность — функция ГПР.
Данная функция позволяет «склеивать» по какому-либо ключевому (уникальному) полю две таблицы в одну. Например, можно склеить справочник физических лиц с их адресами по коду физического лица.
1) Как это работает
Представим, что есть две таблицы в Excel. Первая содержит поля: Код физлица, ФИО физлица, дата примема на работу. Вторая содержит поля: Код физлица, Адрес проживания. И в одной и в другой таблице есть уникальное поле «Код физлица». По нему и произведем сборку двух таблиц в одну. На скриншоте, представленном ниже в колонку «D» выведен результат работы функции. Цифрами обозначены составные части функции. Итак, в колонку «D» по ключевому полю (1) из области ячеек (2) Excel нам «подтянул» вторую колонку — по номеру (3) указанной области (2). При этом мы использовали точное совпадение ключевого поля (4).
Разберем пошагово работу функции.
Увидев в ячейке формулу ВПР Excel смотрит на ключевое поле текущей строки (1). Затем он ищет это ключевое поле в первой колонке в указанной области (2) — область поиска. Причем ищет по точному совпадению — это говорится в параметре (4) функции (ИСТИНА — поиск по вхождению значения ключевого поля в значение первой колонки области поиска, ЛОЖЬ — точное совпадение). После того, как находит — берет колонку, указанную в параметре (3) функции, т.е. вторую. Вот и все — ничего сложного, как видите.
Обратите внимание на указание области поиска — параметр (2) функции ВПР. Вы можете видеть там знаки доллара — это так назваемые абсолютные ссылки. Они означают, что при копировании формулы из ячейки в ячейку ссылка меняться не будет. В то время как ссылка на ключевое поле (1) будет меняться по мере копирования формулы в нижние строки. Что нам собственно и на руку, ведь ключевое поле мы уже должны будем взять из нижней строки, при этом область поиска остается прежней. Ссылку можно сделать абсолютной, выделив ее в строке формулы и нажав F4.
Функция ГПР является полным аналогом функции ВПР, с той лишь разницей, что поиск осуществляется не по вертикали, а по горизонтали.
2) Кому и когда может пригодиться
- Пользователю. В любую типовую конфигурацию 1С встроен универсальный отчет по справочникам, документам и регистрам. С помощью него можно выгрузить любую информацию по соответствующей таблице. И не нужно ждать отдел разработки, чтобы они сделали отчет, долго тестировали и еще дольше бились за выпуск в свет релиза с этим отчетом. Все бы хорошо, но как быть, когда нужно получить один отчет на базе двух и более таблиц — универсальный отчет может за один раз выгрузить информацию только по одной таблице?
В развитие этой ситуации, предположим, что нам нужно получить адреса всех сотрудников, принятых на договор подряда за первое полугодие текущего года. Нет ничего проще. Выгружаем универсальным отчетом в Excel таблицу по документу «Договор на выполнение работ с физлицами», у которого есть реквизит «Физлицо» (используем нужные нам отборы по реквизитам документа, чтобы выгрузить только нужных сотрудников). Отдельной колонкой в этом отчете показываем код физлица. Адресная информация хранится в другой таблице — соответствующем регистре сведений. Выгружаем ее по всем физлицам. Здесь же отдельной колонкой также показываем код физлица. Теперь в Excel склеиваем эти две таблицы. Задача выполнена. - Разработчику. Представим ситуацию, когда вам дали несколько таблиц, выгруженных из другой системы (например SAP) в Excel, и попросили прогрузить их в один справочник в 1С (все таблицы представляют собой реквизиты одного и того же справочника внутри 1С, но разных таблиц хранения в системе SAP). Обрабатывать поочередно каждый файл нудно, долго и неэффективно. И снова нам поможет ВПР. Собираем разрозненные таблицы воедино и загружаем за один заход.
- Тестировщику или пользователю, для проверки отчета или других данных. Допустим есть отчет, написанный разработчиками, соединяющий информацию из разных таблиц. Стоит задача проверить его. Также можно прибегнуть к этой функции, чтобы что-то с чем-то сравнить в отчете.
2. Функция СЧЁТЕСЛИ (COUNTIF).
Данная функция подсчитывает количество значений указанной области, удовлетворяющих определенным критериям. Так, например, можно искать дубли значений (есть ли задвоенные элементы справочника, не двоит ли строки отчет и т.д.).
1) Как это работает
Представим, что есть таблица в Excel, которая содержит список эелементов справочника физических лиц с колонками: Код физлица, ФИО физлица. Нужно прверить справочник на дублирующиеся элементы. Выведем напротив каждой строки количество совпадений кода физлица. На скриншоте, представленном ниже в колонку «C» выведен результат работы функции. Цифрами обозначены составные части функции. Итак, в колонке «C» в области (1) Excel подсчитал количество совпадений значения (2).
Разберем пошагово работу функции.
Увидев в ячейке формулу СЧЁТЕСЛИ Excel ищет в области, указанной в параметре (1) формулы, совпадения со значением, указанным в параметре (2). Количество найденных совпадений суммируется и результат выводится в ячейку с формулой. Далее можно наложить автофильтр на таблицу и отобрать строки, где количество совпадений больше 1 — это и будут задублированные элементы. Быстро и просто.
Также обратите внимание на абсолютную ссылку на область, указанную в параметр (1). Более подробно можете прочитать в описании предыдущей функции.
Имейте ввиду, что критерием поиска совпадений может быть не только ссылка на одну ячейку, но и другие произвольные выражения, что фактически позволяет применять данную функцию при решении широкого спектра задач.
2) Кому и когда может пригодиться
- Пользователю. Для подсчета значений по определенным критериям в больших массивах информации, выгруженных из 1С и при невозможности в последней накладывать отборы на извлекаемые данные.
- Разработчику и тестировщику. Для тестирования разработок. Например для поиска дублей строк отчетов.
Related Posts
Получение логина и пароля техподдержки 1С из базы
Класс для вывода отчета в Excel
Счет-фактура для УПП
Библиотека классов для создания внешней компоненты 1С на C#- Акт об оказании услуг (со скидками) — внешняя печатная форма для Управление торговлей 11.1.10.86
Прайс-лист с артикулом в отдельной колонке
старые добрые функции ВПР, СЧЕТЕСЛИ )))
еще СУММЕСЛИ
один немаловажный момент по поводу ВПР: данные должны быть отсортированы в возрастающем порядке по полю поиска, иначе можно выхватить кучу сюрпризов
(2) приведите плииз пример сюрприза ВПР при несортированности?
Не понял, при чем тут вообще 1С?
Давайте учебник по устройству компьютера здесь еще выложим, по ОС и тренажеры клавиатуры.
(4) а ни причем, человек баллы набирает за статьи))))
(4) с чего начать — всегда проблема
-: ну ни разу ни профиль
б…, дочитал до конца:
«* Например для поиска дублей строк отчетов.»
…
таки здесь шняга известной фирмы, а не ексель-юзерс
(3) вот:
(8) Вы на формулы свои посмотрите. У вас в них отсутствует последний 4-й параметр «интервальный просмотр». Если его задать со значением ЛОЖЬ, то все прекрасно будет искаться и в неотсортированных списках.
(8) вот повторил ваш фокус и никаких #Н/Д, найдите отличие в формуле))))
параметр для точного поиска должен быть ЛОЖЬ
ответ: последний
(9) пока я мучался со скриншотами вы вперед успели)))
(9) ну да, согласен, меня первое время сбивало с толку описание этого интервального просмотра:
… по умолчанию таблица должна быть отсортирована по возрастанию …
… Интервальный_просмотр — логическое значение, определяющее, какое соответствие должна найти функция ВПР — точное или приблизительное …
этож надо дочитать до конца хэлпик про это значение, вот и акцентирую внимание
(13) просьба уважать других читателей материала, а детские замашки типа «нах» и «ексельнегов» оставьте пожалуйста при себе
если вам статья не интересна — вас никто здесь не держит, тем более, что свою оценку материала вы дали
А мне материал был полезен. На портале есть примеры вообще «тупых» публикаций, но на 1С!
Имхо, публикации самое место в разделе Life.
Предлагаю автору переместить ее туда добровольно.
Заодно и добавить чего-нибудь интересненькое из Word и Photoshop
(16) Для чего предназначен раздел Life?
Для абсолютно ВСЕГО, что не касается 1С. Хоть анекдоты пиши
К 1С весьма приблизительное отношение. Ликбез по экселю.
В Лайф. Или вообще подальше…
(19) Оно не «весьма приблизительное», а абсолютно никакое
даже фраза
ни грамма не приближает, путний разработчик дубли в отчете проверит перед выводом на печать. Той же сверткой ТЗ, например
(18) Я согласен, что напрямую с 1С публикация не связана (естественно, ведь Excel — это не 1С), но это и не анекдот. Давайте тогда внешние компоненты написанные на C# тоже помещать в Life — ведь с 1С не связано. По-моему, я в статье раскрыл связь материала публикации с тематикой 1С.
есть макрос в инете для EXEL который тоже самое делает…натыкался на него
(23)
1. Не помню почему
2. Какая разица
3. Ты меня не любишь!
4 ыыыыыыыыыыыыыы… плачет
(с) Красная Бурда.
» тогда внешние компоненты написанные на C#» имеют САМОЕ непосредственное отношение к 1С, они в ней работают.
Кроме того, написаны теми, кто опубликовал.
Может я чего не знаю, может автор один из авторов Excel? Тогда сорри 😀
(24) «напрямую с 1С публикация не связана» не означает, что совсем не связана
раз вы так хорошо научились цитировать, то как никто другой должны понимать конкретный смысл, вкладываемый в словосочетания
(26)Тогда передаю конкретный смысл словосочетания
Через полчаса я её грохну, если не переместится.
Жалобы подавать в Ватикан, Спортлото и в партячейку «Единой Росии»
(26) хорошо, требование администрации — закон, хотя я конечно и не согласен
проверяйте
(26) обещания, обещания 🙂
(28) Танго, проверься на дальтонизм, звездочка стала СИРЕНЕВАЯ 😀
Все хорошо, но так беспардонно выкладывать в общий доступ персональную информацию неведающих об этом сотрудников — это жестоко по отношению к ним…
(30) да ну что вы — это всего навсего демо-база конфигурации УПП, но за бдительность спасибо 🙂
В екселе при выборе любой функции есть адекватное описание с примерами. К чему эта статья?
Кстати серьезный повод задуматься (или понять) почему за действительно нужные разработки так мало плюсов. Может мы вообще зря все это выкладываем и никому наши идеи не нужны?? Вот что спасет мир! а не изыскания в области извращений с кодом. 😀 😀 Надо будет тут скан учебника по Excel потолще выложить… так вообще весь TOP-100 по рейтингу сделаю!
(33) Так пройди мимо, попутно написав настоящий шедевр, только рады будем.
По поводу ВПР и ГПР:
1. Таблица с данными может нахожться НА ОТДЕЛЬНОМ ЛИСТЕ
2. Область данных может быть задана столбцами
3. В области данных могут находится внешние данные в виде запроса, например к SQL-ной таблице, акцесной базе, другому эксельному или даже «блокнотному» файлу.
(36)Господа это вроде ресурс по 1С (не по SQL, Office и прочим операционным приложениям, по ним есть свои тематические ресурсы) или я чего то пропустил?
(37) ну..sql ещё можно обсуждать в контексте 1с, а вот тут excel вообще без привязки к чему либо.
(37) Теперь сие находится в разделе Life, можешь хоть обсуждать книгу «Миф о Холокосте»
(0) экселем не пользююсь вообще, мне проще в 1с-ине всё это слепить..
но так, для справки, плюс.. хотя про вслукап и так знал.
(29) Аб, у меня как бы нету звездочков 🙁
хм. вот спецом убрал фильтры. — сиреневая?
не, ну правда прикольно — 72 плюса …
пипл! если чё — попробуйте жамкнуть мышом на клавишу Ф1
(не благодарите)
есть еще вот такой замечательный
(41) Сиреневая!!!
да ну?
(45) Смотрите не из каталога, а в самой публикации, там, где «Оценка сообщества». В каталоге все звездочки одного цвета 🙂
не-е, там где Игорь рядом ошивается, не пойду
Знал бы автор заранее, какие бои разгорятся из-за этих звездочек 🙂
разбивать надо с тупого конца…
тупого конца головы.
Статья- имеет полезную информацию не оскорбляет никого и может привлечь залетного управленца или бухгалтера а он может купить что=то у вас…
(49) хоть кто-то понимает смысл фразы «привлечение целевой аудитории»… спасибо
а то вот так делаешь полезное инфостарту, а тебя еще и охаят — типа куда полез, не та тема, ты еще фотошоп выложи…
(50) Блин этож форум программистов тут же не пнешь и не обхаешь ближнего — не настоящий программист.
Шутка конечно сам понимаешь как ранима душа программиста увидевшего на полочке с ядом баночку мёда…
(50) По твоей (весьма хромающей) логике надо будет положить pdf-книгу «Windows для чайников».
Можешь быть уверен, что «целевой аудитории» при влечется куда как поболее, чем на твой ёксель
+/52/
А так же:
1. Раздел «Аквариумистика». Нищеброды аквариумы не держат, прям готовые потенциальные покупатели
2. Раздел «Автомобили» — см. п.1
и т.д, и т.п.
Кстати, по правилам это все как раз можно, но имен
Если кому интересна моя позиция, как автора данной публикации, то вот она:
если инфостарт позиционируется как собрание кодеров 1С, то наверное да, моя статья не в тему и напрасно я ее написал,
если же все-таки инфостарт позиционируется как ресурс, содержащий инструментарий, помогающий как разработчику (внедренцу), так и бизнесу (финансистам, бухгалтерам и т.д.) решать свои повседневные задачи, а именно таковым я и считаю данный ресурс, то мои труды были не напрасны. О последнем, кстати, свидетельствует более 80 проплюсовавших.
Да, я зарабатываю рейтинг и ничего зазорного в этом не вижу. Считаю, что любой труд должен быть оплачен, в данном случае рейтингом.
Повторюсь еще раз: не напрасно, в разделе Life 😉
Правда, если ты кармадрочер, то тут облом — очки за лайф в общий рейтинг не идут
(55) да, спасибо за напутствие, ты очень тактичен…
(56) Два года назад мы все тут дружно боролись за то, чтобы в рейтинг не шло типа «Мы учим железяки разговаривать».
Всегда найдутся…, которые наплюсуют. Побороли.
Хочешь возродить?
(56) ой, да, ладно..excel стар как мир, и все всё знают…может соберусь напишу чего-нибудь про killer офиса — LibreOffice 😉
+ 58 а то все про excel и OO, а там тоже макросы есть
(58) почему бы и нет? если там будет что-то интересное для меня, что поможет в работе — скажу огромное спасибо
(0)Хорошая статья. Нужный функции.
Excel умеет делать практически все
— очень мощный инструмент.
прочитал все посты, ты не защищаешь свою статью
(А со своим уставом в чужой монастырь не лезут (с))
С другой стороны радуйся — что твоя статья получила такой резонас
уже 60 постов — вот это уже успех — как мне когда то сказали , после
первой мой публикации.
Мне вот только интересно. Все пишут , что EXCEL все знают и давно понимают
Маленькая задачка (см. на рисунок)
два столбца с числами нужно их попарно перемножить и сложить
т.е.
С7*D7+C8*D8+…. = ?
всего одна ячейка с формулой в ней правильный ответ
количество знаков в формуле всего 20
Сможешь написать правильный ответ или найте более элегантное решение ?
что я нивижу ваших рук — знатоки MS EXCEL
а пообщаться ? 😀 😀 😀
Автор ты где ?
(61)
оно?
(63) Ну ты как всегда молодец. Решиние в итоге правильное.
только у тебя длинна формулы 26 знаков у меня 20
(раньше этой функции не было, видно не очень хороший пример привел)
там есть очень интересная фишка
(64) с длиной я лоханулся слегка, не обратил на это внимание 🙂
(0) Автор — если не ответишь на (61) буду считать данную статью простым КОПИПАСТОМ
(66) ну я как бы и не бил себя пяткой в грудь и не доказывал с пеной у рта, что я гуру Excel — я просто поделился своим опытом, тем, чем пользуюсь в повседневной деятельности
такого решения к сожалению не знаю, но если поковыряться — думаю можно разобраться
(67)Понятно.
просто ник КРУТОЙ и я подумал, а вдруг …
Сначала я был на твоей стороне.
Недавно анекдот тут просто слышал, ничего личного
— один пишет до , другой после ….1С
(64)
Из написанного в 63 выкидываем ПРОИЗВ и используем в конце ввода формулы комбинацию из трех клавиш.
(69) В десятку !
В итоге формула в ячейки будет выглядеть вот так
{=СУММ(C7:C20*D7:D20)}
вот эти «скобочки» по бокам — вот это вкусняшка от EXCEL
Господа, ну вы даете!
Нормальная полезная статья!
По-больше бы таких на портале.
Это люди, которые оказывают профессиональные услуги автоматизации, фрилансеры или работники партнерской сети «1С Франчайзи», штатные сотрудники компаний, которые внедряют систему учета на своем предприятии, а также люди, которые ежедневно используют программу 1С:Предприятие для решения своих задач в бизнесе.
Проект объединяет специалистов различных профилей: программисты, консультанты, бухгалтеры, экономисты, менеджеры проектов и руководители, все те, кто принимает непосредственное участие в постановке учета и внедрении автоматизации на предприятии.
Каждый из нас применяет разные инструменты для учета и автоматизации, так что писать можно обо всем, что вам помогает в работе. 1С — это только то, что нас здесь объединило.
господа, вы поймите, что если сделать большую статью, например, про sharepoint и уделить несколько слов точкам соприкосновения с 1с — разве это плохо и никак с 1с не будет связано? — это же расширение кругозора
(78) лично мне интереснее будет sharepoint, так, что давайте 😉 тем более, что на нем многие документооборот нормальный делают
Предлагаю снести рубрикатор.
Вполне серьезно.
(72)
Доржи, ты, видимо, давным-давно не общался с обычными юзерами. Для них не менее полезными будут статьи: «Как скопировать в буфер», «Что означает «Пароль набран не верно»?» и т.д. Не преувеличиваю, заметь! Ну давай сделаем ликбез по Windows, тебе точно будут благодарны.
О полезности (бесполезности) статьи никто не спорил. Разговор шел только о разделе. Если ты решил в очередной раз поменять правила на ходу — ну что ж, право твоё…
😀 Буквально пару дней назад врисовывал печать в ВПФ к документу Счет. С отсксанированного рисунка. Может, написать, как добавлял альфа-канал, в какой бесплатной программе, чтобы подложка стала прозрачной? А что? Самое прямое отношение имеет к 1С! 😉
Продолжим театр абсурда? Тоже свежак двухдневной давности.
Очень полезный совет:
Если у Вас подключено два сетевых принтера, и один находится в соседней комнате, то не бейтесь в истерике «у меня ничего не печатает!», а:
а) внимательно сморите какой сетевой принтер Вы выбираете при печати;
б) прогуляйтесь в соседнюю комнату
я бы даже сказал, что статья — определенно вредная
тем более, что автор позиционирует себя как автоматизатор на 1се
записная книжка — ексель — 1С — далее везде. вроде бы — это путь автоматзатора
(0) же предлагает 1с — ексель
тут, елы палы, жизь кладешь, чтоб юзеры имели все желтое, а тут, елы палы, им говорят: екселем умеете? маладца! вот вам еще пару функций. при этом стыдливо умалчивают о кнопе ф1
Плюс за полезность и за стойкость 🙂
(102)
И еще нюансик: у нас холдинг далеко не бедный, но нема ёкселя, опен-офис стоит.
А у тех, у кого он есть — на 80% обязательно пиратский.
Только что прям в тему прислали 😀
Веду тут переговоры с крупной международной корпорацией в Мск по поводу устройства туда на работу и переезда в эту Мск.
Набил резюме на три страницы, там куча крупных проектов и внедрений за мой десятилетний опыт, от газа до мяса, среды разработок от детского Дельфи до нынешнего .Net, знания СУБД oracle и SQL, и т.п. Не думайте что хвастаюсь.
А они мне в ответ: «…не могли бы вы поправить свое резюме, т.к. непонятно имеете ли вы опыт работы с Excel, а это очень важно по этой вакансии…»
(104) Так и есть…а у нас libreoffice стоит…мне не нравится скорость работы openoffice с родными форматами excel…
(105) резюм надо составлять под вакуху
(105) у меня на этот случай есть грамота городской олимпиады Юзер-2000 (: Набрал 105 баллов из предполагаемых 100 (:
Было время(после олимпиады) до 1с когда связывал Sybase с Excel и отчеты писал.
(105)Скорее всего, Вы всё это, наверняка, знаете…, но всё же..; там очень много интересного вообще, не только про SUMPRODUCT.
(109) На фига всё это надо? Накладные в ёкселе выписывать? Зачем 1С тогда?
Лично мне, если бы потребовалось ссуммировать произведение (вдруг) я бы даже заморачиваться не стал искать какие там функции есть в этом ёкселе.
Ткнул бы «макросы» и через 30 секунд результат готов (чем искать эти самые СУММПРОИЗВ). Ёксель можно всю жизнь изучать, только ЗАЧЕМ?
XML бы лучше учили — и то проку больше
(110) это для бухов, какие-то ручные операции на нем и вправду удобнее выполнять, если подключить к источнику данных, то можно простенькие отчетики клепать, кому-то удобно…мне же, конечно, ексель тоже нафиг не нужен, почему к своему стыду его плохо знаю…xml можно и к екселю подключить 😀
(111) Если бух самостоятельно клепает отчетики — значит, у него нет программиста, или программист @овно. В любом случае это не по адресу, для этого другие сайты есть
вообще была идея сделать к екселю распределенную бд, чтобы отделить слой данных от интерфейса, т.е. человек запускает с любого места в сети (авторизовавшись) программу и получает свои документы, всем этим делом можно управлять, подключатся с сотового, тогда можно подстроить документооборот под свои нужды. можно сделать в 1С, но лишние лицензии…может что-то подобное уже есть?
(112) ну это я сильно сказал отчетики, получить какую-нибудь единоразово выборку, для этого программист не всегда нужен, на все случаи жизни программу не напишешь и может так оказатся, как в «Дневниках невестки программиста», когда программист 2 дня клепал программу по отсылке открыток на свадьбу.
А если мы говорим о супер-бухах, то вот вам — ТАКИЕ запросищи в 8-ке клепает, что многие тут «спецы» фиг напишут.
И точно так же на ёксель с прибором положила
+ 114 когда там проще и быстрее сделать было вручную…тем более не всегда есть 1С (потому наверное эта тема действительно с 1С не связана)
(115) у каждого свой путь
меня больше интересует за, что мне больше дадут денег при моих минимальных усилиях и вложениях
+ 118 😀 потому я екселем пользуюсь нечасто, не срослось
(117) Просто ей нужен результат, и быстро, а всякие приходящие сертифицированные ларыбуйловы ее окончательно достали. Вот и решила свои проблемы одним махом
в принципе можно всю логику 1С перенести на ексель….интересно, что за монстр получится 😀
(120) респект и уважуха! побольше таких бухгалтеров…
(122)
Если таких будет побольше, представляешь сколько народу к мусорным бакам потянется? 😀
(115) дык мы запросов Зайки не видели, остается полагаться на ваше мнение г-н Abadonna
(120) попахивает вашей личной неприязнью , что не к лицу модеру
ну а вся ветка отражается стишком детским перефразированным:
системы всякие важны, системы всякие нужны раз они имеют место быть и 1С и Excel и DOORS)))))
(4)1с, может, впрямую и ни причём, но для меня данная публикация была весьма полезна. Спасибо!