Справочник excel

Справочник excel


Список всех функций Excel

(включая макрофункции) в алфавитном порядке.
Соответствие русских названий функций их англоязычным аналогам.

Справочник по макрофункциям
с подробной справкой.

Список всех функций листа Excel
на русском и английском языках с разбиением по категориям и кратким описанием каждой функции.
Аналог встроенной справки Excel.

С писок всех существующих функций Excel на основных европейских языках
В список входят все существующие функции Excel (функции листа, макрофункции, функции надстройки Analysis ToolPack и другие) на 12 европейских языках: Русский (RUS), Английский(ENG), Немецкий(GER), Французский(FRA), Итальянский(ITA), Испанский(ESP), Португальский(POR), Голландский(NED), Шведский(SWE), Норвежский(NOR), Датский(DEN), Финский(FIN).

Список всех сочетаний клавиш Excel
Полный список всех «Горячих клавиш» Excel

Справка по VBA на русском языке
Справка по Visual Basic for Application для Excel и Access на русском языке.

Справка по функциям VBA на русском языке
Справка по функциям Visual Basic for Application на русском языке.

Описание полезных приёмов работы в MS Excel
Этого нет в стандартной справке Excel или на официальном сайте Microsoft.

Внимание! На компьютерах с операционной системой Windows Vista, Windows 7, Windows Server 2008 или Windows Server 2008 R2 файлы справки открываться не будут!
Как открыть файл справки? Инструкция по этой ссылке.

Как создать справочник в Excel. Массивы. Функции ИНДЕКС и ПОИСКПОЗ&nbsp&nbsp

&nbsp&nbsp&nbsp&nbsp&nbspБывают домовые, а я — ОФИСНЫЙ!

&nbsp&nbsp&nbsp&nbsp&nbspОблегчаю трудовые будни!

EXCEL ДЛЯ «ЧАЙНИКОВ» И НЕ ТОЛЬКО

Как создать справочник в Excel. Массивы. Функции ИНДЕКС и ПОИСКПОЗ

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

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

Функция «ИНДЕКС»

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

Например, у нас есть список людей с телефонами:

Мы хотим, чтобы в выбранной ячейке появлялось значение, которое находится в определенном месте массива (выбранного диапазона), т.е. мы задаем координаты ячейки: номер строки и столбца.

Выведем телефон Иванова (22222) в выбранную ячейку. Для этого выделяем ячейку, куда хотим вывести искомое значение и через функции (обведено красным) находим функцию ИНДЕКС:

Выбираем в качестве массива все заполненные ячейки, кроме шапки и выбираем номер столбца (2) и строки (2) диапазона, который хотим вывести в выделенную ячейку:

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

Функция «ПОИСКПОЗ»

дает возможность найти номер строки, в которой находится искомое значение:

Как видим «Смирнов» находится в 3-ей строке массива, что и выдает функция «ПОИСКПОЗ».

Для вывода функции выбираем ячейку, где будет отражаться номер строки, вызываем функцию «ПОИСКПОЗ» через функции (обведено красным) :

Выбираем в качестве искомого значения ячейку, где будет задаваться фамилия человека, которого мы хотим найти в списке, в качестве массива – ячейки со всеми фамилиями. Тип сопоставления поставим «0».

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

Как мы знаем, функция ИНДЕКС предполагает указание строки и столбца искомого значения, в то время как функция ПОИСКПОЗ выдает только номер строки.

Таким образом мы можем подставить функцию ПОИСКПОЗ в качестве координаты, которая указывает номер строки, в функцию ИНДЕКС.

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

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

Справочник в MS EXCEL

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

Создадим Справочник на примере заполнения накладной.

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

Таблица Товары

Эту таблицу создадим на листе Товары с помощью меню Вставка/ Таблицы/ Таблица , т.е. в формате EXCEL 2007(см. файл примера ). По умолчанию новой таблице EXCEL присвоит стандартное имя Таблица1. Измените его на имя Товары, например, через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен )

К таблице Товары, как к справочной таблице, предъявляется одно жесткое требование: наличие поля с неповторяющимися значениями. Это поле называется ключевым. В нашем случае, ключевым будет поле, содержащее наименования Товара. Именно по этому полю будут выбираться остальные значения из справочной таблицы для подстановки в накладную.

Для гарантированного обеспечения уникальности наименований товаров используем Проверку данных ( Данные/ Работа с данными/ Проверка данных ):

  • выделим диапазон А2:А9на листе Товары;
  • вызовем Проверку данных;
  • в поле Тип данных выберем Другой и введем формулу, проверяющую вводимое значение на уникальность:
Читать еще:  Выделение повторяющихся значений в excel

При создании новых записей о товарах (например, в ячейке А10), EXCEL автоматически скопирует правило Проверки данных из ячейки А9 – в этом проявляется одно преимуществ таблиц, созданных в формате Excel 2007, по сравнению с обычными диапазонами ячеек.
Проверка данныхсрабатывает, если после ввода значения в ячейку нажата клавиша ENTER. Если значение скопировано из Буфера обмена или скопировано через Маркер заполнения, то Проверка данных не срабатывает, а лишь помечает ячейку маленьким зеленым треугольником в левом верхнем углу ячейке.

Через меню Данные/ Работа с данными/ Проверка данных/ Обвести неверные данные можно получить информацию о наличии данных, которые были введены с нарушением требований Проверки данных.

Для контроля уникальности также можно использовать Условное форматирование (см. статью Выделение повторяющихся значений).

Теперь, создадим Именованный диапазон Список_Товаров, содержащий все наименования товаров:

  • выделите диапазон А2:А9;
  • вызовите меню Формулы/ Определенные имена/ Присвоить имя
  • в поле Имя введите Список_Товаров;
  • убедитесь, что в поле Диапазон введена формула =Товары[Наименование]
  • нажмите ОК.

Таблица Накладная

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

  • выделите диапазон C4:C14;
  • вызовите Проверку данных;
  • в поле Тип данных выберите Список;
  • в качестве формулы введите ссылку на ранее созданный Именованный диапазон Список_товаров, т.е. =Список_Товаров .

Теперь товары в накладной можно будет вводить только из таблицы Товары.

Теперь заполним формулами столбцы накладной Ед.изм., Цена и НДС. Для этого используем функцию ВПР() :

или аналогичную ей формулу

Преимущество этой формулы перед функцией ВПР() состоит в том, что ключевой столбец Наименование в таблице Товары не обязан быть самым левым в таблице, как в случае использования ВПР() .

В столбцах Цена и НДС введите соответственно формулы:
=ЕСЛИОШИБКА(ВПР(C4;Товары;3;ЛОЖЬ);»»)
=ЕСЛИОШИБКА(ВПР(C4;Товары;4;ЛОЖЬ);»»)

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

БЛОГ

Только качественные посты

Практический справочник функций Microsoft Excel с примерами их использования

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

Отсюда вытекает вопрос: Сколько нужно знать функций Excel, чтобы решать практически любую задачу в Excel?

Могу с уверенностью, опираясь на свой 17 летний профессиональный опыт работы в Excel, сказать, что достаточно освоить всего около 100 функций…

Представляю Вам ТОП-50 самых главных функций в Microsoft Excel с примерами их использования

– изучив данные Excel функции, у Вас будет достаточно теоретических знаний, чтобы решать практически любую задачу в Excel

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

1. СУММ / СРЗНАЧ / СЧЁТ / МАКС / МИН (SUM / AVERAGE / COUNT / MAX / MIN)[Базовые формулы Excel]
2. ВПР (VLOOKUP)[Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце]
3. ИНДЕКС (INDEX)[По индексу получает значение из ссылки или массива]
4. ПОИСКПОЗ (MATCH)[Ищет значения в ссылке или массиве]
5. СУММПРОИЗВ (SUMPRODUCT)[Вычисляет сумму произведений соответствующих элементов массивов (позволяет работать с массивами без формул массива)]
6. АГРЕГАТ / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (AGGREGATE / SUBTOTALS)[Возвращает общий итог или промежуточный итог в списке или базе данных с учетом фильтров или без учета фильтров]
7. ЕСЛИ (IF)[Выполняет проверку условия]
8. И / ИЛИ / НЕ (AND / OR / NOT)[Логические условия, как правило для функции ЕСЛИ]
9. ЕСЛИОШИБКА (IFERROR)[Если формула возвращает ошибку то что]
10. СУММЕСЛИМН (SUMIFS)[Суммирует ячейки, удовлетворяющие заданным критериям. Допускается указывать более одного условия]
11. СРЗНАЧЕСЛИМН (AVERAGEIFS)[Возвращает среднее арифметическое значение всех ячеек, которые соответствуют нескольким условиям]
12. СЧЁТЕСЛИМН (COUNTIFS)[Подсчитывает количество ячеек, которые соответствуют нескольким условиям]
13. МИНЕСЛИ / МАКСЕСЛИ (MINIFS / MAXIFS)[Возвращает минимальное/максимальное значение всех ячеек, которые соответствуют нескольким условиям]
14. НАИБОЛЬШИЙ / НАИМЕНЬШИЙ (LARGE / SMALL)[Возвращает k-ое наибольшее/наименьшее значение в множестве данных]
15. ДВССЫЛ (INDIRECT)[Определяет ссылку, заданную текстовым значением]
16. ВЫБОР (CHOOSE)[Выбирает значение из списка значений по индексу]
17. ПРОСМОТР (LOOKUP)[Ищет значения в массиве]
18. СМЕЩ (OFFSET)[Определяет смещение ссылки относительно заданной ссылки]
19. СТРОКА / СТОЛБЕЦ (ROW / COLUMN)[Возвращает номер строки/столбца, на который указывает ссылка]
20. ЧИСЛСТОЛБ / ЧСТРОК (COLUMNS / ROWS)[Возвращает количество столбцов/строк в ссылке]
21. ОКРУГЛ / ОКРУГЛТ / ОКРУГЛВНИЗ / ОКРУГЛВВЕРХ (ROUND / MROUND / ROUNDDOWN / ROUNDUP)[Округляет число до указанного количества десятичных разрядов]
22. СЛЧИС / СЛУЧМЕЖДУ / РАНГ (RAND / RANDBETWEEN / RANK)[Возвращает случайное число]
23. Ч (N)[Возвращает значение, преобразованное в число]
24. ЧАСТОТА (FREQUENCY)[Находит распределение частот в виде вертикального массива]
25. СЦЕПИТЬ / СЦЕП / ОБЪЕДИНИТЬ / & (CONCATENATE / CONCAT / TEXTJOIN / &)[Объединения двух или нескольких текстовых строк в одну]
26. ПСТР (MID)[Выдает определенное число знаков из строки текста, начиная с указанной позиции]
27. ЛЕВСИМВ / ПРАВСИМВ (LEFT / RIGHT)[Возвращает заданное количество символов текстовой строки слева / права]
28. ДЛСТР (LEN)[Определяет количество знаков в текстовой строке]
29. НАЙТИ / ПОИСК (FIND / SEARCH)[Поиск текста в ячейке с учетом / без учета регистр]
30. ПОДСТАВИТЬ / ЗАМЕНИТЬ (SUBSTITUTE / REPLACE)[Заменяет в текстовой строке старый текст новым]
31. СТРОЧН / ПРОПИСН / ПРОПНАЧ (LOWER / UPPER)[Преобразует все буквы текста в строчные/прописные/ или первую букву в каждом слове текста в прописную]
32. ГИПЕРССЫЛКА (HYPERLINK)[Создает ссылку, открывающую документ, находящийся на жестком диске, сервере сети или в Интернете]
33. СЖПРОБЕЛЫ (TRIM)[Удаляет из текста все пробелы, за исключением одиночных пробелов между словами]
34. ПЕЧСИМВ (CLEAN)[Удаляет все непечатаемые знаки из текста]
35. СОВПАД (EXACT)[Проверяет идентичность двух текстов]
36. СИМВОЛ / ПОВТОР (CHAR / REPT)[Возвращает знак с заданным кодом/Повторяет текст заданное число раз]
37. СЕГОДНЯ / ТДАТА (TODAY / NOW)[Возвращает текущую дату в числовом формате / Возвращает текущую дату и время в числовом формате]
38. МЕСЯЦ / ГОД (MONTH / YEAR)[Вычисляет год / месяц от заданной даты]
39. НОМНЕДЕЛИ (WEEKNUM)[Преобразует дату в числовом формате в число, которое указывает, на какую неделю года приходится дата]
40. ДАТАЗНАЧ (DATEVALUE)[Преобразует дату из текстового формата в числовой]
41. РАЗНДАТ (DATEDIF)[Вычисляет количество дней, месяцев или лет между двумя датами]
42. РАБДЕНЬ (WORKDAY)[Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней]
43. ЯЧЕЙКА (CELL)[Возвращает сведения о формате, расположении или содержимом ячейки]
44. ТРАНСП (TRANSPOSE)[Выдает транспонированный массив]
45. ПРЕОБР (CONVERT)[Преобразует число из одной системы мер в другую]
46. ПРЕДСКАЗ (FORECAST)[Вычисляет или предсказывает будущее значение по существующим значениям линейным трендом]
47. ТИП.ОШИБКИ (ERROR.TYPE)[Возвращает числовой код, соответствующий типу ошибки]
48. ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA)[Возвращает данные, хранящиеся в сводной таблице]
49. БДСУММ (DSUM)[Суммирует числа в поле (столбце) записей списка или базы данных, которые удовлетворяют заданным условиям]
50. В качестве бонуса рекомендую изучить Пользовательские форматы в Excel.

Читать еще:  Работа в программе excel

После освоения данных функций, следующим этапом рекомендую осваивать инструменты Бизнес- аналитики Business Intelligence (BI)

В Excel к инструментам бизнес-аналитики уровня Self-Service BI относятся бесплатные надстройки «Power»:

  • Power Query — это технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять и уточнять данные из различных источников для последующего анализа.
  • Power Pivot — это технология моделирования данных, которая позволяет создавать аналитические модели данных, устанавливать отношения и добавлять аналитические вычисления.
  • Power View — это технология визуализации данных, с помощью которой можно создавать интерактивные диаграммы, графики, карты и другие наглядные элементы, позволяющие визуализировать различную информацию.

Ну и если Вы со временем поймете, что возможностей Excel для решения ваших аналитических задач недостаточно, то вам пора переходить к изучению промышленных решений уровня Business Intelligence (BI)

Пользовательские справочники в MS Excel

За время работы я зачастую сталкиваюсь с тем, что при работе с различными книгами MS Excel (полученных из разных баз, от разных пользователей и проч. и проч.) возникает проблема: как сделать ту или иную информацию однородной. В то же время перейти на единый классификатор возможности есть не всегда.

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

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

Итак, теперь обо всем по порядку.

1. Справочник с целью унифицирования информации

Проблема: Есть данные из разных баз, от разных пользователей. Номенклатура, по сути, одна, а наименования написаны по разному. Цель данного справочника — унифицировать номенклатуру, чтобы информация стала однородной и можно было применять знакомые всем функции (СУММЕСЛИ, СЧЁТЕСЛИ, ВПР, ГПР и др.).

Например, есть данные из реестра приемо-сдаточных актов (Таблица № 1)

Из таблицы № 1 сразу видна проблема — одна и та же номенклатура записана по-разному. А значит, функции СУММЕСЛИ, СЧЁТЕСЛИ корректно применить не удасться.

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

Краткий порядок действий:

— копируем лист с исходными данными в отдельную книгу;

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

— на листе с исходными данными правее всех исходных данных создаем столбец, шапка которого называется «Номенклатура общая» (где будет отображаться «правильное название»), а под шапкой пишем и растягиваем вниз до конца таблицы с исходными данными формулу =ВПР (B4;справочник!A:B;2;0) (B4 — в данном примере ячейка, содержащая исходную номенклатуру). После растягивания формул до конца получим, что все значения столбца «Номенклатура общая» содержат ошибку Н/Д (мы ведь еще не заполняли справочник!);

— ставим автофильтр на столбец «Номенклатура общая» с условием Н/Д.

— начинаем заполнять лист справочник, копируя с листа с исходными данными значения столбца «Номенклатура», а напротив в ручную проставляя «правильные значения» до тех пор, пока все ошибки Н/Д не уберутся. Если на лист справочник скопировать только значения столбца «Номенклатура» (в столбец A), не проставляя «правильных значений» (в столбец B), то значение функции ВПР в данном случае стареет равно 0. Здесь есть небольшая хитрость — каждый раз при добавлении в справочник «Номенклатуры» (особенно при создании первого справочника), удобно каждый раз, заходя на лист с исходными данными обновлять автофильтр на условие Н/Д, хотя и не обязательно, т. к. значения Н/Д по мере заполнения справочника будут изменяться на «правильные значения номенклатуры», а Excel автоматически автофильтр не обновляет.

Читать еще:  В excel найти дубли

Для нашего примера, справочник может выглядеть следующим образом (Таблица № 2).

Из таблицы № 2 видим, что в 1 столбце стоят наименования сырья (по сути, одного вида), полученные из разных источников. В столбце 2 объединяем эти виды сырья в один.

Создание первого справочника обычно занятие трудоемкое. Дальше (по мере обновления рабочей книги) проще, т. к. базы и пользователи меняются не часто и количество «неправильно» введенных данных резко уменьшается.

В результате редактированный лист «Исходные данные» будет выглядеть так, как представлено в таблице № 3.

Реестр пса с добавлением столбца «Номенклатура общая»

Теперь, используя столбец «Номенклатура общая», можно корректно применять функции, подобные функциям СУММЕСЛИ и СЧЁТЕСЛИ, например, для расчета средневзвешенной цены за месяц.

2. Справочник с целью группировки

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

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

Решение: эту задачу также решаем с помощью «Пользовательского справочника»

Краткий порядок действий:

— на исходном листе правее столбца «Номенклатура общая» создаем столбец с шапкой «Вид общий», под шапкой пишем формулу =ВПР (B4;справочник!A:C;3;0) и растягиваем вниз до конца таблицы. В данном случае значения функций ВПР будут равны нулю (ведь столбец C — не заполнен);

— на уже созданном листе «справочник» в столбце C добавляем «Вид сырья», которую заполняем вручную (также удобно пользоваться автофильтром с условием ноль, как и в прошлом разделе с условием Н/Д.

В результате перечисленных действий получим следующий результат (таблица № 4).

Реестр пса с добавлением столбца «Вид общий»

Теперь, используя столбец «Номенклатура общая», можно корректно применять функции, подобные функциям СУММЕСЛИ и СЧЁТЕСЛИ, но уже группируя сырье по видам.

Небольшое замечание: в данном случае, при создании столбца «Вид общий» можно в функции ВПР опираться не на столбец B, а на столбец «Номенклатура общая», тогда в справочнике нужно правее создать связку Номенклатура общая — Вид общий. Это немного сэкономит время, т. к. разных значений в столбце «Номенклатура общая» меньше (а зачастую на порядок!), чем в столбце «Номенклатура». Эту связку нужно располагать на листе справочник ПРАВЕЕ (а не в коем случае не внизу) связки Номенклатура — Номенклатура общая (например, в столбце D (для удобства отображения оставляя столбец C пустым).

3. Справочник с целью отображения и учета информации

Проблема: нужны данные о поступлении сырья из реестра пса только за конкретный период, например, декаду. Опять же использование функции СУММЕСЛИ не возможно, т. к. хотя номенклатура у нас унифицирована, в случае ее применения, результат будет средний за месяц.

Решение: создаем справочник, который будет учитывать соотношение дата — декада с признаком учитывать — не учитывать.

Краткий порядок действий:

— на исходном листе правее столбца «Вид общий» создаем столбец с шапкой «Декада», под шапкой пишем формулу =ВПР (A4;справочник!E:F;2;0), где A4 — исходная дата из реестра пса и растягиваем вниз до конца таблицы. В данном случае значения функций ВПР будут равны ошибке Н/Д;

— на листе «справочник» создаем в столбцах E и F связку Дата — Декада. Такой справочник создается достаточно быстро, т. к. каждая дата элементарно привязывается к одной из 3-х декад. Можно, естественно, эту процедуру еще больше упростить, используя функцию ЕСЛИ и ДЕНЬ, хотя это и не обязательно.

— на исходном листе правее столбца «Декада» создаем столбец с шапкой «Отображать декаду», под шапкой пишем форулу =ВПР (значение декады; справочник!H:I;2;0), где значение декады — значение ячейки напротив в столбце «Декады»;

— на листе «справочник» создаем в столбцах H и I связку Декада — Отображать декаду. Такой справочник создается элементарно, т. к. имеет только 3 строки и 2 столбца. По умолчанию, ставим в столбце «Отображать декаду» везде 1.

— теперь на исходном листе в графе «Отображать декаду» везде стоят значение «1».

— правее столбца «отображать декаду» делаем графы «Отображать количество» (перемножение соответствующих ячеек столбца «количество» и столбца «отображать декаду») и «Отображать Всего с НДС, руб.» (перемножение соответствующих ячеек столбца «Всего с НДС, руб.» и столбца «отображать декаду»).

— в случае, если необходимы данные за определенную декаду, ставим на листе «справочник» напротив всех не нужных декад «0», а напротив нужной декады оставляем «1».

В результате выполнения вышеуказанных действий получаем следующие результаты (таблица № 5)

Реестр пса с подекадной разбивкой и параметрами отображения

Теперь, используя справочник отображения декады можно вывести средневзвешенную цену за определенную декаду с помощью все той же функции СУММЕСЛИ, или отследить количество поставок за декаду с помощью функции СЧЁТЕСЛИ.

Итоговый справочник на основании 3-х разделов будет выглядеть следующим образом (таблица № 6).

С указанным в разделах 1-3 примером можно ознакомиться в формате Excel, перейдя по ссылке.

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector