Лига помощи Excel

Здесь мы видим что 01.10.2010 на складе было некое перемещение материалов, которые кладовщик добросовестно отразил в отчете. Однако искушенный пользователь заметит некоторые ошибки введении отчетной деятельности. А именно:

- не все ячейки заполнены

-единицы измерения указаны в каждом случае

-материалы разных категорий находятся в одной группе

-единицы измерения разнятся

-неверно написана дата составления отчета

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

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

Эти небольшие изменения уже экономят кучу времени на написании единиц измерения в каждой ячейки. Кроме этого, эти изменения позволяют нам применить функции Excel для автоматического расчета остатка. Для этого нужно выбрать  первую ячейку столбца "!Конечный остаток" (I4) и перейти на строку формул.Нажав на строку мы можем вводить данные в ячейку.

Но сейчас мы будем вводить не данные а формулу, которая сама будет рассчитывать конечный остаток, и нам не придется каждый раз считать и записывать его вручную. В данном случае мы обойдемся простой арифметикой, не используя функции. Все формулы в Excel начинаются с =, после того как вы нажали = выберите первую ячейку столбца "Начальный остаток" и адрес этой ячейки автоматически добавится в формулу, далее нажимаете + чтобы прибавить приход, и нажимаете на первую ячейку столбца "Приход". Следующем шагом будет вычитание расхода, для этого нажмите - и выберите первую ячейку столбца "Расход".  В итоге у вас должна получится вот такая формула

Если вы сделали все верно то нажав Enter вы получите конечный остаток, как видите ошибка допущенная при ручном расчете исключается, при использование формулы.

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

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

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

Синтаксис:

Application.Match(<что ищем>, <где ищем>, <как ищем>)

<Что ищем> может быть:

• одно значение - переменная или константа, например «aaa», 123 или ValueToFind, причем различных типов - строка, число, дата, логическое.

  • текстовые значения могут содержать подстановочные знаки * (0+ любых символов),? (один любой символ). Их можно экранировать тильдой ~, если нужно найти именно * или ?

• одномерный массив Array(«aaa», 123)

• диапазон листа Excel (строка или столбец) Range("A1:A10"), Range("A1:F1")

<Где ищем> может быть:

• одномерный массив, содержащий значения различных типов, например, Array(«aaa», 123)

• диапазон листа Excel (строка или столбец) Range("A1:A10"), Range("A1:F1")

<Как ищем>:

• 0 - точно. Массив <Где ищем> может быть неупорядоченным (неотсортированным)

• -1 - поиск наименьшего значения. Массив <Где ищем> должен быть отсортирован по убыванию

• 1 - поиск наибольшего значения. Массив <Где ищем> должен быть отсортирован по возрастанию

Application.Match() возвращает:

• если <что ищем> – одно значение, то возвращается одно значение типа Double (индекс найденного значения в массиве, начиная с 1) или значение типа Error (если значение не найдено);

• если <что ищем> – массив из N элементов, то возвращается массив, содержащий N значений типа Double (индекс найденного значения в массиве, начиная с 1) или значений типа Error (если значение не найдено).

Особенности:

• если значение не найдено, возвращается значение ошибки #N/A [Error 2042] (метод WorksheetFunction.Match() в этом случае генерирует исключение)

• поиск текста производится всегда регистронезависимо, т.е. прописные и строчные буквы А и а считаются одинаковыми, и не зависит от установки Option Compare { Binary | Text }

• при успешном поиске Application.Match() возвращает для каждого искомого значения только первый индекс найденного элемента в <где ищем>. Т.е. если <что ищем> = 2,2 а в <где ищем> находится 2,1,2,2, то результатом будет массив 1,1 - обе двойки найдется в первом индексе.

• Индексы нумеруются с 1 независимо от установки Option Base { 0 | 1 }.

Примеры применения Application.Match()

1. Ищем несколько текстов, в т.ч. по маске, в массиве:

Данный код ищет строки «qqq» (три q или Q подряд) и «a?b» (строка из трех символов, начинающаяся на a или A, заканчивающаяся на b или B с любым символом посередине) в массиве arr, содержащем строки «AAA», «A5A», «Abb».

В результате работы кода в окне Immediate будет напечатано:

Этот результат означает, что строка "qqq" не найдена в массиве arr, а "a?b" соответствует третьему элементу "Abb".

2. Ищем значения из столбца A в столбце C:

Этот код ищет значения из ячеек колонки A листа Excel в колонке C листа Excel. Результат выводится в цикле с предварительной проверкой, найден ли очередной элемент или нет. Эта проверка производится с помощью функции IsNumeric(), которая возвращает Истину в том случае, если аргумент - число. В том случае, если значение не найдено, в результирующем массиве будет находиться значение ошибки (Error), и IsNumeric() вернет Ложь. Также это можно проверить с помощью функции IsError(), которая возвращает Истину, если аргумент является значением ошибки.