Как переделать цены в таблице excel © создание справочников

(О порядке округления. )

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

При округлении числа до N-го знака, если следующий за N знак:

— = 5, то N-й знак увеличивается на 1.

Например, число «пи» = 3,14159 при округлении до сотых долей равно 3,14. А число «e» = 2,71828 при округлении до сотых равно 2,72.

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

Мы остановимся лишь на некоторых моментах, связанных с округлением.

Заметим, что, говоря об округлении, например, до второго знака после запятой, мы имеем в виду округление до сотых долей (XXX,XX).

Когда можно округлять НДС

По общему правилу исчисленный за квартал НДС нужно уплачивать в бюджет ежемесячно тремя равными долями в течение трех месяцев, следующих за отчетным кварталом (ст. 163, п. 1 ст. 174 НК РФ).

Если разделить сумму НДС к уплате без остатка невозможно, тогда первый и второй ежемесячный платеж вы можете округлить до полных рублей в меньшую сторону, а последний – в большую.

Смотреть пример округленияПо итогам I квартала общая сумма НДС к уплате составила 255 892 руб. Разделив ее на три части, вы получите 85 297,33333 руб.В этом случае в апреле и мае вы должны уплатить в бюджет по 85 297 руб., а в июне – 85 298 руб.

Счета-фактуры

В счетах-фактурах не допускается округление копеек до целых рублей. Бумаги заполняют в рублях и копейках (письмо Минфина России от 29.01.2014 г. № 03-02-07/1/3444). Правилами оформления счета-фактуры, применяемого при расчетах по НДС (утв. постановлением Правительства РФ от 26.12.2011 г. № 1137) в пункте 3 указано, что все стоимостные показатели отражаются в рублях и копейках (долларах США и центах, евро и евроцентах либо в другой валюте).

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

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

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

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

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

Какие показатели можно округлять, а какие нет

Показатели	Правила округления
Выплаты сотрудникам	Все выплаты сотрудникам надо начислять с копейками.
Лимит Кассы	Лимит наличности в кассе можно округлить до целых рублей по правилам математики. Это подтвердили чиновники (письмо ФНС России от 6 марта 2014 г. № ЕД-4-2/4116@). А кассовую первичку, например кассовые расходники, заполняйте с копейками
Коэффициент в декларации по транспортному налогу	Коэффициент владения транспортным средством в отчетном периоде (строка 120 раздела 2) приведите с четырьмя знаками после запятой. Этот коэффициент надо указывать, если компания являлась собственником машины не весь период
Коэффициенты в декларации по ЕНВД	Коэффициент К1 округлять не нужно. В 2014 году К1 — 1,672 (строка 080 раздела 2 декларации по ЕНВД). Коэффициент К2 ЕНВД округляют до трех знаков после запятой, его утверждают местные власти (строка 090 раздела 2)

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

ИСПОЛЬЗУЕМАЯ ЛИТЕРАТУРА и ДОПОЛНИТЕЛЬНЫЕ ССЫЛКИ

КАК СДАТЬ налоговую отчетностьЗаконодательством РФ предусмотрены два способа сдачи налоговых деклараций (расчетов).

Схемы исправления старых ошибок

При выявлении неправильного отражения хозяйственных операций в отчетном году после его завершения, но за который годовая бухгалтерская отчетность не утверждена в установленном порядке, исправления производятся записями декабря года, за который подготавливается к утверждению и представлению в соответствующие адреса годовая бухгалтерская отчетность.
В случаях выявления организацией в текущем отчетном периоде неправильного отражения хозяйственных операций на счетах бухгалтерского учета в прошлом году исправления в бухгалтерский учет и бухгалтерскую отчетность за прошлый отчетный год (после утверждения в установленном порядке годовой бухгалтерской отчетности) не вносятся. П. 11 Приказа минфина РФ от22 июля 2003 г. N 67н.

В документах «Корректировка поступления» и «Корректировка реализации» изложенные выше требования законодательства поддерживаются опционально.

Когда возникает необходимость корректировки

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

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

Как заполнить корректировочный счет-фактуру при уменьшении стоимости поставки?

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

Если изменения произошли в течение 5-дневного срока с момента первичной отгрузки, особых проблем не возникает. К примеру, в целях налогового учета достаточно выставить новый счет-фактуру, учитывающий изменения (ст. 168 НК РФ п. 3). Однако так бывает не всегда. Рассмотрим ситуации корректировки реализации прошлого периода в ту или иную сторону на примерах.

Как учесть корректировку реализации при изменении цены?

Корректировки реализаций и поступлений прошлых периодов

Л.А. Елина, экономист-бухгалтер Как исправить ошибку прошлых периодов в бухучете, налогах и страховых взносах с учетом и официальных, и негласных правил Ошибки неприятны сами по себе.

А если за них могут еще и оштрафовать, совсем невесело. Избежать штрафов можно, если вовремя и правильно все исправить. Эта статья посвящена исправлению ошибок прошлых периодов. Поскольку с текущими периодами, как правило, сложностей не возникает. Исправляем прошлогодние ошибки в бухучете Новый Закон о бухучете ничего не изменил в этом вопросе. Таким образом, исправлять ошибки прошлых лет надо по всем знакомой схеме. 1п. 6 ПБУ 22/2010; 2п. 3 ПБУ 22/2010; 3п. 14 ПБУ 22/2010; 4п. 9 ПБУ 22/2010; 5подп. 2 п. 9 ПБУ 22/2010; 6пп. 6—8 ПБУ 22/2010 (1) Критерий существенности ошибки вы определяете и устанавливаете сами. Его надо закрепить в учетной политикеп. 3 ПБУ 22/2010; п.

Документы, корректирующие таможенную стоимость

Таможенный орган, проверяя ГТД, может скорректировать указанную в ней таможенную стоимость товаров только в двух случаях (Пункты 2, 5 ст. 323 Таможенного кодекса РФ):

(или) если сочтет, что представленные вами сведения недостоверны;

(или) если вы не представили таможне данных, подтверждающих правильность определения таможенной стоимости товаров.

Примечание

Однако работники таможни это правило довольно часто нарушают. И пытаются пересчитать таможенную стоимость товаров (разумеется, в большую сторону) даже тогда, когда импортер представил все необходимые документы и нет сомнений в их достоверности. Правда, если в этом случае дело доходит до суда, импортерам, как правило, удается доказать необоснованность пересчета таможенной стоимости (Письмо Северо-Западного таможенного управления РФ от 19.08.2008 N 12-01-12/18853; Постановления ФАС СЗО от 10.12.2009 по делу N А56-2811/2009; ФАС ВСО от 20.05.2010 по делу N А78-6284/2009).

Корректировку таможенной стоимости таможенный орган оформляет в специальном листе-приложении к таможенной декларации. Это либо форма КТС-1 (если изменения коснулись только одной товарной позиции), либо КТС-2 (если корректируется стоимость нескольких товарных позиций) (Пункт 5 Приложения N 2 к Приказу ФТС России от 20.09.2007 N 1166).

Причем таможенная стоимость товара может изменяться как до, так и после выпуска товара в свободное обращение. Вторая ситуация часто связана с тем, что таможня не успевает определить таможенную стоимость товара в течение 3 рабочих дней со дня получения ГТД и документов, подтверждающих сведения в ней, которые отводятся таможне на растаможивание товара (выпуск в свободное обращение) (Пункт 1 ст. 149, п. 1 ст. 152 Таможенного кодекса РФ). И тогда товар выпускается под обеспечение уплаты таможенных платежей, которые могут быть дополнительно начислены таможней (Пункт 3 ст. 153, п. 6 ст. 323, п. 3 ст. 338 Таможенного кодекса РФ).

Если после корректировки таможенная стоимость увеличена, то потребуется доплата таможенных платежей, и таможенный орган выставит вам требование об их уплате. А вы должны будете перечислить деньги в течение 10 рабочих дней (Пункт 7 ст. 323 Таможенного кодекса РФ).

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

Рассмотрим оба случая: и увеличение, и снижение таможенных платежей.

Объединение ячеек и добавление текста

Для каждого прайс-листа нужен заголовок. Чтобы он выглядел привлекательно, нужно поместить название посередине. Чтобы заголовок размещался в одной ячейке, необходимо объединить ячейки над таблицей. Это возможно с помощью окна выравнивание, в верхней панели необходимо нажать на кнопку разворота. В появившемся окне необходимо поставить галочку рядом с надписью «объединение ячеек».

Как правильно объединить ячейки

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

Объединённые ячейки в таблице

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

Настройка сквозных строк

В меню выбираем раздел «Лист» и напротив надписи «Сквозные строки» нажимаем на кнопку вызова строки, куда должны быть внесены координаты шапки. Координаты получаем двойным нажатием по необходимой ячейке. Теперь на каждой странице будет обозначено название прайс-листа.

Создание сквозных строк

Проблемы с покупателями

Покупатель может усомниться в правильности округленных сумм. Даже незначительные расхождения становятся причиной постоянного обращения контрагентов с просьбой исправить документы. В этом случае предлагаем внести в учетную политику положение: «Сотрудники оформляют первичные документы и счета-фактуры в специальной программе. Стоимость товаров автоматически округляется до двух знаков после запятой. При расчете программа учитывает НДС без округления, поэтому возможны расхождения между ручным счетом и данными программы — до 5 копеек».

Такое положение поможет решить проблему постоянного недовольства покупателей.

Сколько составляет процент от числа.

Предположим, вы покупаете новый телевизор за 800 долларов, и за доставку с вас просят 4% от стоимости покупки. Вопрос в том, сколько вы должны заплатить сверх первоначальной цены? Другими словами, что составляет 4% от 800 долларов?

Поможет следующая формула:

Предполагая, что цена записана в ячейке A2, а процент — в B2, приведенная выше формула превращается в =A2*B2 и возвращает 32.

Помните, что когда вы записываете в таблицу число, за которым следует знак процента (%), то оно интерпретируется Excel как сотая часть его значения (то есть, сразу делится на 100). Например, 4% фактически сохраняется как 0,04, и Excel использует это значение во всех формулах и расчетах.

Другими словами, формула =A2*11% эквивалентна =A2*0.11. Естественно, вы можете использовать десятичное число, соответствующее процентному соотношению, непосредственно в формуле, если это лучше подходит для ваших таблиц.

Проверка результата в режиме «1С:Предприятие»

Создадим документ «Приходная накладная»:

Рисунок 5 – Документ «Приходная накладная № 1»

Проверим движения по РН «Остатки номенклатуры»:

Рисунок 6 – Движения документа «Приходная накладная № 1»

Приход сформирован. Займемся списанием товаров.

Создадим документ «Расходная накладная». Выполним в нем списание всех трех ручек, которые имеются на остатке.

Рисунок 7 – Документ «Расходная накладная № 1»

При проведении получим следующие сообщения:

Рисунок 8 – Сообщения при проведении документа «Расходная накладная № 1»

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

Если заглянуть в движения по РН «Остатки номенклатуры», то там тоже все отлично – никаких копеек:

Рисунок 9 – Движения по РН «Остатки номенклатуры»

Учет корректировочных счетов-фактур

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

В результате увеличения цены или объема на основании корректировочного счет-фактуры, разницу между НДС с первоначальной и корректировочной стоимостями покупатель может заявить к вычету. При этом документ корректировки на увеличение обязательно фиксируется в книге покупок. Право на вычет может быть предоставлено в течение 3 лет с даты получения этого документа.

Деление чисел

Допустим, вы хотите узнать количество человеко-часов, затраченных на завершение проекта (общее количество сотрудников в Project), или фактические нормы миль на галлон для вашего последнего межфирменного перерыва (общее количество мильных галлонов). Есть несколько способов разделения чисел.

Деление чисел в ячейке

Для выполнения этой задачи используйте арифметический оператор / (косая черта).

Например, если в ячейке ввести = 10/5 , в ячейке будет отображено 2.

Важно: Убедитесь, что перед вводом чисел и оператора () в ячейку введите знак равенства ( =); в противном случае приложение Excel будет интерпретировать введенную дату. Например, если ввести 7/30, Excel может отобразить 30-е в ячейке

Если вы вводите 12/36, Excel сначала преобразует это значение в 12/1/1936 и отобразит в ячейке 1-дек.

Регистрация в книгах покупок и продаж

Корректировочные счета‑фактуры надо зарегистрировать в книге покупок или книге продаж, в зависимости от того, как изменилась стоимость.

Если она выросла, то продавец вписывает в книгу продаж, при снижении цены — в книгу покупок.

У покупателя наоборот: при уменьшении суммы — в книгу продаж, при увеличении — в книгу покупок.

Продавец

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

Покупатель

Увеличение стоимости. У покупателя появляется право на вычет дополнительно предъявленной суммы НДС.
Уменьшение стоимости. У покупателя возникает обязанность восстановить сумму НДС, излишне принятую к вычету по первоначальному счету-фактуре.

Округлять ли пени по налогам и как

Пени по налогам исчисляют из 1/300 ставки рефинансирования. Она в настоящее время (конец 2017 г.) равна 7,75 процента. То есть размер ежедневных пеней составляет 0,0275 процента (8,25: 300).

Округлять дневную ставку пеней до двух знаков после запятой, то есть до 0,03 процента, не надо. Ведь из Налогового кодекса РФ не следует, что ежедневную сумму пеней надо округлять. А все неясности толкуют в пользу компаний. Поэтому выгоднее применять дневную ставку, в которой четыре знака после запятой — 0,0275. Так сумма пеней у компании будет меньше. И только их общую сумму за все дни просрочки можно округлить до двух знаков после запятой (определение ВАС РФ от 22 октября 2009 г. № ВАС-13685/09). Такой вариант не спровоцирует споров с проверяющими. Именно такой расчет приводят налоговики в приказе ФНС от 18 января 2012 г. № ЯК-7-1/9@.

Если в счетах-фактурах вы будете указывать стоимостные показатели с копейками, а в первичке в целых рублях, у компании возникнут разницы. Из-за этого налоговые инспекторы наверняка откажут вашему покупателю в вычете входного НДС. Поэтому во всех документах показатели лучше отражать с копейками. Тем более бухгалтерские программы обычно автоматически округляют данные в первичных документах и счетах-фактурах до двух знаков после запятой. Однако у контрагентов могут возникнуть сомнения, правильно ли сделано округление. Предположим, ваш покупатель рассчитал сумму аванса в размере 125 026,55 руб., а вы выставили ему счет на предоплату в сумме 125 026,52 руб.

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

ВЫПИСКА из учетной политики ООО «Гаспром»
Округления в первичных документах и счетах-фактурах
6.1. Первичку и счета-фактуры сотрудники оформляют в бухгалтерской программе. Программа автоматически округляет итоговую стоимость товаров до двух знаков после запятой. НДС при расчете итоговой стоимости программа берет, не округляя. В связи с этим возможны небольшие расхождения (до 5 копеек) между данными в программе и суммами, рассчитанными вручную.

Сделайте для контрагента выписку из учетной политики. У него больше не будет вопросов об округлении в первичке и счете-фактуре.

Корректировочный счет-фактура полученный (уменьшение цены товаров, текущий период)

Входной НДС принят к вычету во II квартале. В программе отражены следующие операции:

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

В программе в IV квартале необходимо отразить следующие операции:

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

изменить первоначальную стоимость объекта ОС в сторону уменьшения;
изменить сумму амортизации ОС в сторону уменьшения и пересчитать амортизацию за все месяцы до октября;
внести изменения в регистр сведений для изменения параметров начисления амортизации.

Эти исправления в программе также не автоматизированы и регистрируются документом Операция, введенная вручную.

Главная — Документы

Вычитаем проценты в таблице с зафиксированным %

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

В данном случае, формула расчета будет выглядеть так (на примере ячейки G2):

Примечание: Знаки “$” можно прописать вручную, либо наведя в формуле курсор на ячейку с процентами нажать клавишу “F4”. Таким образом, вы зафиксируете ячейку с процентами, и она не будет меняться при растягивании формулы вниз на другие строки.

Далее нажмите “Enter”, и результат будет посчитан.

Теперь можно растянуть ячейку с формулой аналогичными предыдущим примерам способом на остальные строки.

Аннулирование ошибочно занесённого документа

Бывают ситуации, когда ошибочно занесён документ, например, создан дубль в базе 1С 8.3.

Аннулирование ошибочно занесённого документа в 1С 8.3 производится ручной операцией Сторно-операцией в Операции, введённые вручную из раздела Операции.

В поле Сторнирующий документ выбрать ошибочно введённый документ. Данный сторно документ сторнирует все проводки, а также начисление НДС:

Для занесения сторно операции в Книгу покупок необходимо создать Отражение НДС к вычету из страницы Операции:

В документе необходимо установить все галочки;
Обязательно указать дату записи дополнительного листа:

На закладке Товары и услуги:

Заполнить данные из расчётного документа и установить отрицательную сумму;
Проконтролировать, чтобы в поле Событие было установлено значение Предъявлен НДС к вычету:

Проверить правильно ли отражено аннулирование ошибочного документа можно в Книге покупок – раздел Покупки:

Что же такое «проблема копеек»?

В задачах списания товаров «проблема копеек» выражается в следующем: при полном списании товара по количеству его себестоимость не списывается в «ноль». Остается несколько копеек. Связано это с округлениями при расчете себестоимости списания.

Видим, что следствием нерешенной «проблемы копеек» является еще и другая ошибка: не будут выведены одновременно в ноль все ресурсы регистра остатков. За такую ошибку на экзамене снижают от 0,5 до 3 баллов.

Пример: на остатке есть 3 ручки общей стоимостью 100,00 руб. Спишем 3 ручки. Расчет себестоимости списания выполним по средней.

Формула: Себестоимость Остаток / Количество Остаток * Количество Списания

Себестоимость единицы товара составит 100 / 3 = 33,33 руб.

Себестоимость списания согласно формуле составит 33,33 * 3 = 99,99 руб.

Итого после списания останется 0 шт по количеству и 1 копейка по стоимости.

Возникает вопрос: зачем рассчитывать себестоимость за единицу и уж тем более ее округлять? Давайте сразу применим формулу без промежуточных округлений.

Вычисления система будет выполнять последовательно:

100 / 3*3 = 33.33333…* 3 = 99.99999…

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

Казалось бы, все хорошо. Но в таком варианте на экзамене «проблема копеек» будет считаться нерешенной. Почему? Из-за тех самых 99.999999. Правильный результат в данном случае был получен в результате автоматического округления.

Макрос поиска ячейки с выпадающим списком

Допустим у нас имеется таблица Excel сформированная в результате экспорта журнала фактур из истории взаиморасчетов с клиентами фирмы, как показано ниже на рисунке:

Нам необходимо найти все выпадающие списки или определить каким ячейкам присвоена проверка вводимых данных, создана инструментом: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

В программе Excel по умолчанию есть встроенный инструмент для поиска ячеек с проверкой правил вводимых значений. Чтобы его использовать следует выбрать: ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек». В появившемся диалоговом окне следует отметить опцию «проверка данных» и нажать на кнопку ОК. Но как всегда более гибким решением является написание своего специального макроса. Ведь в такие случаи всегда можно усовершенствовать инструмент и дописать много других полезных функций. А этот код макроса послужит прекрасным началом программы.

Откройте редактор макросов Visual Basic (ALT+F11) и создайте новый модуль выбрав в редакторе инструмент: «Insert»-«Module». В созданный модуль введите VBA код макроса:

Если нужно выделить все ячейки в таблице, которые содержат проверку вводимых значений включенной инструментом «Проверка данных», тогда выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«ProvDan»-«Выполнить».

В результате выделились 14 ячеек в столбце G, для которых включена проверка данных в стиле выпадающего списка:

В данном коде мы сначала выделяем все ячейки на текущем листе с помощью инструкции:

Cells.Select

После, определяем диапазон ячеек на листе, который использует исходная таблица и с которыми будет работать наш макрос. Чтобы определить диапазон таблицы на рабочем листе Excel, мы могли бы использовать свойство UsedRange при создании экземпляра объекта Range в переменной diapaz1. Данное свойство охватывает только непустые ячейки, а это может быть даже несмежный диапазон. Но таблица может содержать пустые ячейки для, которых присвоена проверка ввода значений. Чтобы наш макрос не игнорировал пустые ячейки внутри таблицы мы определяем смежный (неразрывный) диапазон, который начинается с ячейки A1 и заканчивается последней используемой ячейкой на рабочем листе Excel.

Set diapaz1 = Application.Range(ActiveSheet.Range(“A1”), ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

Последняя ячейка находиться наиболее отдаленно от ячейки A1 (в данном примере – это G15) и была использована на листе (это обязательное условие). При чем использована в прямом смысле, она может даже не содержать значения, но иметь измененный числовой формат, другой цвет фона, другие границы, объединение и т.п. Чтобы найти последнюю используемую ячейку на листе стандартными средствами Excel, выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек».

В появившемся окне следует выбрать опцию «последнюю ячейку». А после просто нажать ОК. Курсор клавиатуры сразу переместиться на последнюю используемую ячейку на рабочем листе Excel.

Можно даже при создании экземпляра объекта Range в переменной diapaz1 использовать диапазон целого листа. Для этого просто замените выше описанную инструкцию на:

Set diapaz1 = Selection

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

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

On Error Resume Next

Но после конца цикла обработка ошибок снова включается.

On Error GoTo 0

Внутри цикла проверяться по отдельности все ячейки на наличие включенной проверки вводимых значений инструментом «Проверка данных». Если ячейка содержит проверку вводимых значений?

If IsError(diapaz1(i).Validation.Type) Then

Тогда она присоединяется к диапазону ячеек, находящихся в переменной diapaz2.

Set diapaz2 = Application.Union(diapaz2, diapaz1(i))

В конце кода выделяется несмежный диапазон переменной diapaz2, который включает в себя все выпадающие списки на текущем рабочем листе Excel. И сразу же выводиться сообщение о количестве найденных и выделенных ячеек в этом же диапазоне.

MsgBox “Найдено: ” & diapaz2.Count & ” ячеек!”