РНУ в Excel (для затравки)

Здравствуй, любимая! Здравствуй, далёкая!
Мысли к тебе улетают, как птицы...
Больно представить, что ты, одинокая,
Таешь в борьбе с электронной таблицей.
..........Сколько смогу, Лен, - всегда помогу!
..........(Только другим - уговор - ни гу-гу!)

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

Стройный каркас - это только "початок"...
Важно продумать солидную шапку,
Шрифт, логотип, общий стиль... Распечатать...
И обязательно - в красную папку!
..........Красная папка, Наташ, это писк!
..........В ней и уверенность дышит и риск!

Выплесни, милая! Выдумай, славная!
Изобретательство - вроде искусства.
Делай, как нравится. В творчестве главное -
Чуточка меры и чуточка вкуса.
..........Внешний вид важен, Кать, это закон.
..........Но бухгалтерией правит не он...

Смысла ты ищешь в букаевском логове?
Тезис, Оль, чувствую определяющий:
Статус регистра в учёте налоговом -
Ниточка следствия для проверяющих.
..........Будь осторожней, Тань, в оба гляди!
..........Помни советы Пруткова и бди!

Правило есть, и оно обязательно:
Наши таблицы - отчётов истоки.
Из декларации, Надь, показатели
Пишем в регистры - в столбцы или строки.
..........Лишними данными, Юль, не сори:
..........Только что нужно в регистры бери.

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

Вот и готово невесте приданое,
Чтоб поженить наш учёт с декларацией.
В Экселе - ссылки, а в Ворде - слияние -
Авто - в народе зовут - матизация!
..........Я для тебя, Нин, пыхтел от души!
..........Если что будет неясно - пиши.

Несколько полезных формул...
Предварительные замечания. Во всех формулах я пишу такие имена, чтобы сразу было понятно, о чём идёт речь. В реальных таблицах удобнее использовать более короткие имена. Для этого лучше разработать собственную систему. Например, всё, что относится к продажам можно начинать буквой П или заканчивать кодом 90 (это могут быть колонки количества, цены, ставки НДС, номер фактуры или партии, НсП, кода позиции (например, партия*100+N;п/п) колонка со спец. знаком чёрного нала и т.д. и т.п.)... :oops:
Далее. Если у меня в формуле стоит имя Коэффициент (реально это имя будет выглядеть, скорее всего, например, как ПК или К90) то это может быть либо ячейка, в которой мы можем проставлять какое-то значение (например, 1,12 для подбора продажной цены или 0,88 для подбора закупочной цены :wink: ) для всей партии, а может быть - это имя целой колонки (которое можно специально и не задавать, если установлен флажок Сервис/Параметры/Вычисления/"допускать названия диапазонов") - в этом случае формула будет ссылаться на значение из пересечения той же строки, где она записана и указанной колонки, т.е. для каждого товара можно задать индивидуальный коэффициент. Зачем это нужно? Абсолютно всё считается автоматом (если, конечно установлен флажок Сервис/ Параметры/ Вычисления/ "автоматически"), поэтому изменяя значение этого коэффициента, я могу подбирать (изменения видны сразу) нужное значение, например, прибыли или НДС за месяц или за квартал и т.д. Ну, это не для всех подходит, я понимаю, а всё же...
Итак...

Подбираем не просто цену по коэффициенту, а такую цену, которая будет делиться без остатка на 11 или на 6 соответственно для НДС 10% или 20% (удобнее расчитывать цену без НДС и сумму НДС - получается всё точно, без округлений). Формула:
=ОКРВВЕРХ(ИсходнаяЦена*Коэффициент;ВЫБОР(СтавкаНДС*10;0,11;0,06))

А вот такие (или похожие) формулы стоят в ячейках счёта-фактуры, товарной накладной и т.п. Здесь Номер - имя ячейки, значение в которой удобнее менять при помощи элемента управления "спин", он же "счётчик" (при щелчке меняется номер фактуры и, соответственно, всё содержимое). Пример приведён для колонки "Наименование", для других будет попроще (без конкатенации).:
=ЕСЛИ(ЕНД(ПОИСКПОЗ(Номер*100+RC[-1];Код;0));" - ";ИНДЕКС(Товар;ПОИСКПОЗ(Номер*100+RC[-1];Код;0))&" "&ИНДЕКС(Мод;ПОИСКПОЗ(Номер*100+RC[-1];Код;0))&" "&ИНДЕКС(С;ПОИСКПОЗ(Номер*100+RC[-1];Код;0))&" "&ИНДЕКС(Разм;ПОИСКПОЗ(Номер*100+RC[-1];Код;0)))
Считаем итог (любого показателя) партии или счёта-фактуры (для регистра) из аналитической таблицы:
=СУММЕСЛИ(Партии;НужнаяПартия;Показатель)
То же, но для двух условий одновременно (можно и больше) - здесь должны совпадать номер счёта-фактуры и ставка НДС (фигурные скобки указывают, что это т.н. "формула массива", т.е. их нельзя вводить вручную, а надо Ctrl+Shift+Enter):
={СУММ(ЕСЛИ(СФ&ст=СчФ&Ставка;ндс90))}
Такие формулы очень крутые! Например, сводным таблицам нужно велеть пересчитываться "вручную", а формулы массива считаются сразу автоматом, т.е. при любых изменениях сразу виден (конечный) результат!

С помощью похожих формул массива собираем данные в сводный журнал операций:
={СУММ(ЕСЛИ(КассаКв&кассаДебет=Квартал&СчётДебета;кассаСумма))}Здесь надо заметить, что используется вспомогательная колонка "Квартал" (здесь - КассаКв), в которой квартал вычисляется по дате примерно так: =ОКРУГЛВВЕРХ(МЕСЯЦ(Дата)/3;0) (мы уже использовали функцию ОКРВВЕРХ, а здесь - ОКРУГЛВВЕРХ - это разные функции!)

После этого все операции собираются в оборотно-сальдовую ведомость, где в каждой ячейке написано что-то вроде:
=СУММЕСЛИ(жоДт;План;жо1кв)
А дальше уже всё так просто, что даже и неинтересно. Лучше (для слияния в Word'е или связи с книгой отчётов в Excel'е) создать отдельный блок нужных данных, которые и будут фигурировать в декларациях.

ГЕНИЙ!!!!! :) :) :)

Поэтический дар + талант программиста + знание БУ + понимание НУ= формула гениальности:D

Сегодня мы займёмся программированием в Excel. Это VBA.
Для разбора попалась под руку полезная процедура "ВставитьСтроку" из "программы для налогового учёта" (именно так позиционирует автор - которого уж не буду называть - своё творчество в Excel). Мы попробуем сделать её более простой, элегантной, понятной и эффективной. Для целостности впечатления привожу текст процедуры полностью, а затем уж разберём по косточкам...

Sub ВставитьСтроку()
' Сочетание клавиш: Ctrl+q
Dim c, d As Range
Dim a1, a2, a3, a4, a5, a6, x, y, z
Dim stroka
stroka = Format(Selection.Row)
x = stroka + ":" + stroka
Set c = Range(x)
c.Select
Selection.Insert Shift:=xlDown
stroka = Format(Selection.Row)
y = "A" + stroka
Set d = Range(y)
d.Select
z = Range("A3")
x = Now()
a1 = Right(Year(x), 2)
If Month(x) < 10 Then a2 = "0" & Month(x) Else: a2 = Month(x)
If Day(x) < 10 Then a3 = "0" & Day(x) Else: a3 = Day(x)
If Hour(x) < 10 Then a4 = "0" & Hour(x) Else: a4 = Hour(x)
If Minute(x) < 10 Then a5 = "0" & Minute(x) Else: a5 = Minute(x)
If Second(x) < 10 Then a6 = "0" & Second(x) Else: a6 = Second(x)
y = z & a1 & a2 & a3 & a4 & a5 & a6
ActiveCell.FormulaR1C1 = y
End Sub

Вот что, оказывается, можно писануть!!! Давайте вдумаемся.
Sub ВставитьСтроку()
' Сочетание клавиш: Ctrl+q
Это название процедуры и комментарий. Единственное (правда, есть ещё End Sub), с чем трудно спорить.
Dim c, d As Range
Dim a1, a2, a3, a4, a5, a6, x, y, z
Dim stroka
Это объявление переменных. Из них только d типизирована как Range (диапазон), остальные (в том числе и "с") - variant, а это лишнее время и лишняя память (для нашей задачи - вставка строки - это, конечно, будет незаметно "на глаз", но мы ведь говорим о принципах программирования в Excel). На самом деле для выполнения поставленной задачи нужна одна переменная типа long (пусть это будет "l"). Пишем:
Dim l As Long 'номер первой строки выделенного диапазона - одна переменная вместо двенадцати!
Дело в том, что VBA требует ресурсов для объявления/инициализации переменных больше (да-да!), чем для вычисления какого-нибудь логарифма! Кто не верит - проверьте! Чем меньше переменных, тем лучше, и ВСЕ они (бывают очень редкие исключения) ДОЛЖНЫ быть (с точки зрения хорошего стиля программирования, а не работоспособности) типизированы. Смотрим дальше.
stroka = Format(Selection.Row)
x = stroka + ":" + stroka
Set c = Range(x)
c.Select
Здесь автор оригинальным образом (через текстовое определение) выделяет первую строку активного диапазона (если он состоит из нескольких строк).
На самом деле выделять её не нужно, а достаточно узнать номер. Это делается так:
l=Selection.Row 'одна строка вместо четырёх
Далее автор вставляет новую строку и запоминает её номер (что, естественно, излишне):
Selection.Insert Shift:=xlDown
stroka = Format(Selection.Row)
А мы пишем проще:
Cells(l,1).EntireRow.Insert
Выделить нужную ячейку можно так:
y = "A" + stroka
Set d = Range(y)
d.Select
А можно так:
Cells(l,1).select
На самом деле, чтобы записать в ячейку данные, её совсем не нужно предварительно выделять. Но мы это сделали, чтобы наша процедура была полностью адекватна "критикуемой" (когда строка вставится, выделенной окажется та же ячейка).
Наконец, чтобы записать в активную ячейку код операции, состоящий у него из содержимого ячейки А3, года, месяца, дня, часов, минут, секунд (по 2 цифры), автор потеет:
z = Range("A3")
x = Now()
a1 = Right(Year(x), 2)
If Month(x) < 10 Then a2 = "0" & Month(x) Else: a2 = Month(x)
If Day(x) < 10 Then a3 = "0" & Day(x) Else: a3 = Day(x)
If Hour(x) < 10 Then a4 = "0" & Hour(x) Else: a4 = Hour(x)
If Minute(x) < 10 Then a5 = "0" & Minute(x) Else: a5 = Minute(x)
If Second(x) < 10 Then a6 = "0" & Second(x) Else: a6 = Second(x)
y = z & a1 & a2 & a3 & a4 & a5 & a6
ActiveCell.FormulaR1C1 = y
А надо всего-то:
ActiveCell.value = Cells(3,1).value & Format(now,"YYMMDDhhmmss")
Конец процедуры:
End sub

Для чего же я понаписал весь этот сыр-бор? Дело в том, что если у Вас есть именованные диапазоны (а я верю, что они у Вас есть, и много! :), и если Вы регулярно добавляете туда новые данные, а я не сомневаюсь, что так оно и есть ;)), то, чтобы "старое" имя после добавки ссылалось на "новый" диапазон, есть два пути: переименовать диапазон (ручками или программно) или вставить строку внутри (обычно это в конце) диапазона. (Я уж не говорю о том, что справа/слева не должно быть данных! - это азы!) Естественно, второй путь и проще, и надёжней. Вот здесь-то и поможет процедура "ВставитьСтроку" с назначенной "горячей клавишей" (здесь у автора Ctrl+q - вполне подходяще!). Вместо добавления в новую строку вышеприведённого кода (который Вам вряд ли пригодится), Вы автоматически прибавите туда следующий номер позиции:
Cells(l, 1).Value = Cells(l - 1, 1).Value + 1
, а также скопируете в эту строку все нужные Вам формулы -
что-то вроде: Range(Cells(l, 2), Cells(l, 9)).FillDown
и, наконец, выделите ячейку для ввода новых данных. И получится хорошо!
В принципе, полноценный учёт в Excel можно организовать вообще без программирования (такая уж это замечательная вещь - Excel!), но удобный и лёгкий учёт поможет организовать только VBA. Те бухгалтеры, которые работают в Excel, должны хорошо знать свой главный инструмент. Вот поэтому я и надеюсь, наш "разбор полётов" окажется для многих полезным.
Удачи!

Еще бы знать, к чему все это применить :)
А вообщем, впечатляет :)
Кстати, уважаемый МММ,
замечал ли ты, что если стоит
Windows XP, то написанное на VBA под 98-ми
начинает как-то глючить?

Цитата:

---

Исходное сообщение Бакк
Ещё бы знать, к чему все это применить :)

---

А вот, в последнем абзаце и написано... Вообще, думается (всё с бОльшей уверенносью), что для МП (маленьких) Excel - лучший вариант!

Цитата:

---

Кстати, уважаемый МММ,
замечал ли ты, что если стоит
Windows XP, то написанное на VBA под 98-ми
начинает как-то глючить?

---

Пробовал XP, отказался - у меня всё глючит! Остался в 98.

Цитата:

---

Исходное сообщение MMM
А вот, в последнем абзаце и написано... Вообще, думается (всё с бОльшей уверенносью), что для МП (маленьких) Excel - лучший вариант!

---

И не тока для маленьких - у меня по 200 транзакций
в день по реализации товара (отгрузка или оплата),
веду в Excel-е :glasses:

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

Sub Перебор()
For Each Ячейка In Range("Таблица")
...
Debug.Print Ячейка;
...
Next
End Sub

Естественно, вместо строки Debug.Print Ячейка; вы вставите нужный вам блок обработки каждой ячейки. (Вывод значений ячеек в окно Debug - оно же Immediate Window - служит для отладочной проверки: так ли всё работает.)
Кроме того, если у вас задано обязательное определение переменных (в начале модуля написано: "Option Explicit"), то вы должны определить переменную "Ячейка" (Dim Ячейка, или Dim Ячейка As Variant, или Dim Ячейка As Range - последний вариант предпочтительней). Определение переменной должно предшествовать её первому использованию.
Указанный цикл перебирает сначала ячейки первой строки первого поддиапазона, затем второй строки - слева направа и т.д. сверху вниз.

Другой способ перебрать ячейки диапазона (сплошного):

For i = 1 To Range("Таблица").Rows.Count
For j = 1 To Range("Таблица").Columns.Count
...
Debug.Print Cells(i, j)
...
Next j
Next i

Здесь конструкция посложнее, но она даёт возможность управлять последовательностью перебора: справа налево (или слева направо), затем сверху вниз (или снизу вверх) и наоборот. Кроме того, Легче контролировать в теле цикла адрес ячейки при помощи переменных i и j (i номер строки, j - номер колонки).
Удачи!

А вот еще последствия ночного разговора: перебор ячеек в составном диапазоне Range (multiple-area ranges), например:
Range("C4:C100,F4:J100").Select
Первый синенький пример сверху - быстрый, но неудобный (не даёт возможности управлять перебором в каждом поддиапазане). Второй пример в составном диапазоне не работает (по крайней мере у меня перебирает только первый поддиапазон).
А вот еще одно решение: использование свойства Areas, которое даёт доступ в каждый поддиапазон -
Range.Areas(i)
Файл пример прилагается.

Не без ошибок, но стоит посмотреть.
Крэйзи - мастер!
Если будут вопросы - напишите!