Как сделать справку в excel

Developing.ru

Моя Справка к моему VBA

А как мне справку написать к своей программе (в надстройке есть, например, пользовательская функция, а Эксель позволяет делать при вводе функции «Справка по этой функции»).

Да и вообще — никогда не работ с hlp (или chm?)? Как это делать? 😯

Набираем в поисковике «создание chm» и становиься плохо от объема inf-ы 😯
сам пробовал бесплатный HTML Help Workshop — нудно.
последнее, что читал .
http://dk.compulenta.ru/offline/2004/103/168015/
Касса «справок» не дает

Предложение.
Cамая лучшая и простая справка — в виде файла Word с относительными гиперссылками на любые типы документов, располагающихся в рядом лежащей папке Docs.
. ну можно еще в PDF перегнать этот Wordo-вский файл.

Я использовал Help & Manual, пробовал Help Developing Studio (обе программы Shareware) — впечатление приятное (только денег нет на их покупку)!
Пробовал бесплатные (Shalom Help, и ещё чего-то там, написанное на Delphях) — впечатление не приятное (зато денег не нужно).

Присоединять файл справки (hlp) к VBA проекту (особенно надстройке) получается не очень хорошо. Есть два варианта:
1. Должен быть прописан путь к этому файлу (в свойствах проекта — через редактор VBA) — абсолютный путь это плохо;
2. Можно программно менять путь к файлу справки по событию открытия книги (например, так ThisWorkbook.VBProject.HelpFile = ThisWorkbook.Path & «my.hlp»), НО! В этом случае для Экселя 11.0 должен быть установлен флажок «Доверять доступ к Visual Basic Project» (безопасность макросов)!

Вот такой пока у меня тупик!

Вот вопрос:
ObjectBrowser — выбрать свой проект — в Classes выбрать свой модуль — в Members выбрать свою функцию — правая кнопка на своей функции — Properties. — Задать Description и Help Context ID — нажать Ок — опять правой кнопкой — опять Properties. — В Description нет ничего и в Help Context ID нет ничего!

Почему? Кто-нибудь знает? Как сделать, чтобы было и оставалось?

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

1. Не получается. 😕 Описание функции сделать могу, а описание каждого из аргументов не получается.

Источник:
http://www.developing.ru/viewtopic.php?t=5386

Developing.ru

Моя Справка к моему VBA

А как мне справку написать к своей программе (в надстройке есть, например, пользовательская функция, а Эксель позволяет делать при вводе функции «Справка по этой функции»).

Да и вообще — никогда не работ с hlp (или chm?)? Как это делать? 😯

Набираем в поисковике «создание chm» и становиься плохо от объема inf-ы 😯
сам пробовал бесплатный HTML Help Workshop — нудно.
последнее, что читал .
http://dk.compulenta.ru/offline/2004/103/168015/
Касса «справок» не дает

Предложение.
Cамая лучшая и простая справка — в виде файла Word с относительными гиперссылками на любые типы документов, располагающихся в рядом лежащей папке Docs.
. ну можно еще в PDF перегнать этот Wordo-вский файл.

Я использовал Help & Manual, пробовал Help Developing Studio (обе программы Shareware) — впечатление приятное (только денег нет на их покупку)!
Пробовал бесплатные (Shalom Help, и ещё чего-то там, написанное на Delphях) — впечатление не приятное (зато денег не нужно).

Присоединять файл справки (hlp) к VBA проекту (особенно надстройке) получается не очень хорошо. Есть два варианта:
1. Должен быть прописан путь к этому файлу (в свойствах проекта — через редактор VBA) — абсолютный путь это плохо;
2. Можно программно менять путь к файлу справки по событию открытия книги (например, так ThisWorkbook.VBProject.HelpFile = ThisWorkbook.Path & «my.hlp»), НО! В этом случае для Экселя 11.0 должен быть установлен флажок «Доверять доступ к Visual Basic Project» (безопасность макросов)!

Вот такой пока у меня тупик!

Вот вопрос:
ObjectBrowser — выбрать свой проект — в Classes выбрать свой модуль — в Members выбрать свою функцию — правая кнопка на своей функции — Properties. — Задать Description и Help Context ID — нажать Ок — опять правой кнопкой — опять Properties. — В Description нет ничего и в Help Context ID нет ничего!

Почему? Кто-нибудь знает? Как сделать, чтобы было и оставалось?

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

1. Не получается. 😕 Описание функции сделать могу, а описание каждого из аргументов не получается.

Источник:
http://www.developing.ru/viewtopic.php?t=5386

Excel-лайфхаки для тех, кто занимается отчётностью и обработкой данных

В этом посте Ренат Шагабутдинов, ассистент генерального директора издательства «Манн, Иванов и Фербер», делится классными Excel-лайфхаками. Приведённые советы будут полезны для всех, кто занимается различной отчётностью, обработкой данных и созданием презентаций.

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

В этой статье собраны несложные приёмы, позволяющие упростить работу в Excel. Особенно они пригодятся тем, кто занимается управленческой отчётностью, готовит разнообразные аналитические отчёты, основанные на выгрузках из 1С и других отчётах, формирует из них презентации и диаграммы для руководства. Не претендую на абсолютную новизну — в том или ином виде эти приёмы наверняка обсуждались на форумах или упоминались в статьях.

Простые альтернативы ВПР и ГПР, если искомые значения не в первом столбце таблицы: ПРОСМОТР, ИНДЕКС+ПОИСКПОЗ

Функции ВПР (VLOOKUP) и ГПР (HLOOKUP) работают только в том случае, если искомые значения находятся в первом столбце или строке той таблицы, из которой вы планируете получить данные.

В остальных случаях есть два варианта:

  1. Использовать функцию ПРОСМОТР (LOOKUP).
    У неё следующий синтаксис: ПРОСМОТР (искомое_значение; вектор_просмотра; вектор_результата). Но для её корректной работы нужно, чтобы значения диапазона вектор_просмотра были отсортированы по возрастанию:
  2. Использовать сочетание функций ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX).
    Функция ПОИСКПОЗ возвращает порядковый номер элемента в массиве (с её помощью вы можете найти, в какой строке таблицы искомый элемент), а функция ИНДЕКС возвращает элемент массива с заданным номером (который мы и узнаем с помощью функции ПОИСКПОЗ).Синтаксис функций:
    • ПОИСКПОЗ (искомое_значение; массив_поиска; тип_сопоставления) — для нашего случая нам нужен тип сопоставления «точное сопоставление», ему соответствует цифра 0.
    • ИНДЕКС (массив; номер_строки; [номер_столбца]). В данном случае номер столбца указывать не нужно, так как массив состоит из одной строки.

Как быстро заполнить пустые ячейки в списке

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

Выделяем столбец «Тематика», нажимаем на ленте в группе «Главная» кнопку «Найти и выделить» → «Выделить группу ячеек» → «Пустые ячейки» и начинаем ввод формулы (то есть ставим знак равно) и ссылаемся на ячейку сверху, просто нажимая стрелку вверх на клавиатуре. После этого нажимаем Ctrl + Enter. После этого можно сохранить полученные данные как значения, так как формулы больше не нужны:

Как найти ошибки в формуле

Вычисление отдельной части формулы

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

  1. Чтобы вычислить часть формулы прямо в строке формул, выделите эту часть и нажмите F9:

    В данном примере была проблема с функцией ПОИСК (SEARCH) — в ней были перепутаны местами аргументы. Важно помнить, что если вы не отмените вычисление части функции и нажмёте Enter, то вычисленная часть так и останется числом.
  2. Нажмите на кнопку «Вычислить формулу» в группе «Формулы» на ленте:


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

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

Чтобы определить, от каких ячеек зависит формула, в группе «Формулы» на ленте нажмите на кнопку «Влияющие ячейки»:

Появляются стрелки, указывающие, от чего зависит результат вычислений.

Если отображается символ, выделенный на картинке красным цветом, то формула зависит от ячеек, находящихся на других листах или в других книгах:

Щёлкнув на него, мы увидим, где именно находятся влияющие ячейки или диапазоны:

Рядом с кнопкой «Влияющие ячейки» находится кнопка «Зависимые ячейки», работающая аналогично: она отображает стрелки от активной ячейки с формулой к ячейкам, которые зависят от неё.

Кнопка «Убрать стрелки», расположенная в том же блоке, позволяет убрать стрелки к влияющим ячейкам, стрелки к зависимым ячейкам или же оба типа стрелок сразу:

Как найти сумму (количество, среднее) значений ячеек с нескольких листов

Допустим, у вас есть несколько однотипных листов с данными, которые вы хотите сложить, посчитать или обработать как-то иначе:

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

Вы получите сумму ячеек с адресом B3 с листов «Данные1», «Данные2», «Данные3»:

Такая адресация работает для листов, расположенных последовательно. Синтаксис следующий: =ФУНКЦИЯ (первый_лист:последний_лист!ссылка на диапазон).

Как автоматически строить шаблонные фразы

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

  • Объединяем текст с помощью знака & (можете заменить его функцией СЦЕПИТЬ (CONCATENATE), но в этом нет особого смысла).
  • Текст всегда записывается в кавычках, ссылки на ячейки с текстом — всегда без.
  • Чтобы получить служебный символ «кавычки», используем функцию СИМВОЛ (CHAR) с аргументом 32.

Пример создания шаблонной фразы с помощью формул:

В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:

ТЕКСТ (значение; формат)

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

Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать — стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.

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

При объединении ячеек сохраняется только одно значение. Excel предупреждает об этом при попытке объединить ячейки:

Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):

Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:

Как построить сводную из нескольких источников данных

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

Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:

После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:

При щелчке на неё появляется диалоговое окно:

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

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

После этого в последнем диалоговом окне укажите, где будет размещаться отчёт сводной таблицы — на существующем или новом листе:

Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо:

Как рассчитать количество вхождений текста A в текст B («МТС тариф СуперМТС» — два вхождения аббревиатуры МТС)

В данном примере в столбце A есть несколько текстовых строк, и наша задача — выяснить, сколько раз в каждой из них встречается искомый текст, расположенный в ячейке E1:

Для решения этой задачи можно воспользоваться сложной формулой, состоящей из следующих функций:

  1. ДЛСТР (LEN) — вычисляет длину текста, единственный аргумент — текст. Пример: ДЛСТР (“машина”) = 6.
  2. ПОДСТАВИТЬ (SUBSTITUTE) — заменяет в текстовой строке определённый текст другим. Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст). Пример: ПОДСТАВИТЬ (“автомобиль”;“авто”;“”)= “мобиль”.
  3. ПРОПИСН (UPPER) — заменяет все символы в строке на прописные. Единственный аргумент — текст. Пример: ПРОПИСН (“машина”) = “МАШИНА”. Эта функция понадобится нам, чтобы делать поиск без учёта регистра. Ведь ПРОПИСН(“машина”)=ПРОПИСН(“Машина”)

Чтобы найти вхождение определённой текстовой строки в другую, нужно удалить все её вхождения в исходную и сравнить длину полученной строки с исходной:

ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6

А затем разделить эту разницу на длину той строки, которую мы искали:

6 / ДЛСТР (“МТС”) = 2

Именно два раза строка «МТС» входит в исходную.

Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» — тот, число вхождений которого нас интересует):

В нашем примере формула выглядит следующим образом:

Источник:
http://lifehacker.ru/8-priyomov-raboty-v-excel/

Как создать реестр документов в Excel: пошаговая инструкция с фото

Здравствуйте, друзья сайта itswat.ru . Людям определённых профессий приходится работать с большим количеством всевозможных документов – внутренних, входящих, исходящих и других. В крупных организациях, где число документов гигантское, система их учёта чётко отлажена. Для создания электронных реестров используются профессиональные программы, лицензия на которые стоит прилично. Но что делать небольшой, только родившейся и ещё не раскрученной компании, или скромному предпринимателю? Документов не слишком много, но они есть и все очень важные. Их потеря может выйти боком, а платить ежемесячно за профессиональное ПО – лишние и пока неуместные траты. Мой ответ – используйте Excel и с помощью нескольких функций систематизируйте и учитывайте свои «важные бумажки». Итак, тема данного материала — как создать реестр документов в Excel. Дело не сложное, но муторное и с множеством нюансов, поэтому расскажу пошагово с картинками.

Что можно поместить в реестр?

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

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

Я же буду создавать реестр текстовых документов, содержащих описания товаров, которые я когда-то составлял по просьбе одной бизнесвумен, по чистой случайности, завалявшиеся на моём ноутбуке. Делаю я это не по необходимости, а для того, чтобы наглядно продемонстрировать вам весь процесс. Это и будет пошаговая инструкция. Уяснив принципы, вы легко сможете адаптировать её под себя, изменив шапку таблицы и выбрав только те действия, которые понадобятся именно вам. Я использую Excel 2007, но версия не принципиальна. Вы можете работать в Excel 2010 или более поздних годов выпуска, ничего не потеряете, так как во всех редакциях используемые мной функции также присутствуют, а разделы меню и команды находятся там же, где и раньше.

Пошаговая инструкция

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

  1. Создаём новый документ, задаём ему имя и сохраняем как «Книга с поддержкой макросов».

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

  1. Заполняем названия заголовков. В моём случае это «Номер», «Заказчик», «Папка», «Название», «Документ» и «Директория».

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

  1. Столбец «Заказчик» можно заполнить вручную или способом, описанным для столбца «Название» при условии, что имена заказчиков – это по совместительству названия папок в общей директории.

  1. В столбце «Папка» я хочу видеть адрес папки, в которой располагается конкретный документ и при этом сделать его ссылкой, при нажатии на которую Excel будет перебрасывать меня в эту папку. Для этого я воспользуюсь функцией «ГИПЕРССЫЛКА». Подробнее опишу ниже.
  2. В столбце «Название» мне нужно отобразить имена документов, но вводить их все вручную очень долго. В Excel существует небольшая хитрость, позволяющая в разы ускорить процесс и автоматически заполнить ячейки названиями всех документов, находящихся в указанной папке. Это возможно благодаря функции «Файлы», ищите алгоритм чуть ниже.
  3. Столбец «Документ» будет содержать рабочую ссылку на нужный файл, нажатие на которую спровоцирует его открытие. Как это сделать, ищите чуть ниже.
  4. Столбец «Директория» — это всё тот же адрес папки, но без гиперссылки. Он нужен для того, чтобы заполнить предыдущие столбцы.

Чтобы получить активный адрес папки, где расположен нужный документ, воспользуемся функцией «Гиперссылка»:

  1. Ставим курсор в первую ячейку соответствующего столбца.
  2. Копируем адрес нужной папки.

  1. Вставляем его сразу в «Директорию», он пригодится в дальнейшем.

  1. Нажимаем в строке значений на fx и находим нужную функцию или вписываем вручную =ГИПЕРССЫЛКА(«адрес папки»).

  1. Ещё один способ, на мой взгляд, самый удобный – щёлкаем в нужной ячейке ПКМ (правой кнопкой мыши), в меню выбираем команду «Гиперссылка», в открывшемся мастере отыскиваем нужную папку и подтверждаем действие.

  1. Так придётся делать для каждого документа, но если учесть, что большое количество их расположено в одной и той же папке, то процесс не должен отнять много времени. Для последующих документов, являющихся соседями первого по директории, адрес можно просто скопировать.

С названием немного сложнее:

  1. Добавляем к адресу, помещённому нами в «Директорию» ещё один обратный слеш и звёздочку, то есть *.

  1. Выбираем раздел меню «Формулы», функцию «Диспетчер имён».

  1. А в её мастере команду «Создать».

  1. Задаём имя, в моём случае это будет «Название».

  1. В строке «Диапазон» пишем =ФАЙЛЫ( и щёлкаем курсором по соответствующей ячейке в столбце «Директория», закрываем скобку).

  1. Нажимаем ОК и «Закрыть».
  2. Ставим курсор в первую ячейку столбца.
  3. Вписываем туда формулу =ИНДЕКС(Название;СТРОКА()-1), где Название – это имя, заданное нами на четвёртом шаге данного алгоритма, а -1 обозначает, что данная ячейка стоит второй в столбце.
  4. Нажимаем «Энтер» и видим, что в столбце появились имена всех файлов, расположенных в указанной папке.

  1. Таким методом можно поместить в реестр названия не всех файлов, а выборочно в зависимости от формата. Для этого предварительно в адресе папке, указываемом в столбце «Директория», после звёздочки следует написать расширение файла, например, *docx, xls или jpeg. Это актуально, когда в одной папке находятся вперемешку файлы разных форматов – таблицы, картинки, текстовые документы и любые другие.

Если что-то вам по моим картинкам и описанию не понятно, то посмотрите следующую подробную инструкцию в формате видео.

Друзья, столбец «Документ» содержит то же название, но являющееся ссылкой. Чтобы этого добиться, следует проделать все те же действия, что и в предыдущем алгоритме до шага 8. Мы их опустим, так как повторно делать одно и то же не нужно. Теперь немного усложняем формулу. Она примет вид =ГИПЕРССЫЛКА(ИНДЕКС(Название;СТРОКА()-1)). После её размещения и ввода строки заполнятся теми же названиями, но являющими собой активные ссылки.

Вот, кажется, и всё. Старался описать алгоритм наиболее подробно. Меня ещё недавно спросили, как создать реестр сведений о доходах физических лиц, то есть формировать справки 2-НДФЛ и вести их учёт в программе «Налогоплательщик ЮЛ». Тема, в общем, та же, но программа другая, поэтому я решил посвятить ей отдельный материал. До скорых встреч.

Источник:
http://itswat.ru/reestr-dokumentov-excel/

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

19 апреля 2013 г.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник:
http://excel2.ru/articles/spravochnik-v-ms-excel

Excel 3. Введение в Excel

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

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

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

Excel освобождает вас от проведения этих вычислений вручную. Я часто использую Excel для создания графика выполненных работ по проектам.

Файл программы Excel называется рабочая Книга, которая содержит рабочие листы. По умолчанию каждая новая книга содержит 1 лист (в других версиях Excel – 3 листа, которые нумеруются по порядку). Любая книг может включать до 255 листов. Каждый рабочий лист в Excel имеет 1048576 строк и 16384 столбцов. Так что разгуляться есть где.

По окончании урока вы сможете:

  1. Назвать элементы рабочего окна
  2. Ориентироваться в командных лентах Excel
  3. Перечислить виды курсоров.

1. Элементы рабочего окна

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

  1. Командная лента. Каждая лента имеем своё имя. Лента разделяется темно-серыми линиями. Между двумя линиями находятся группы команд. Группа команд объединяет команды одной тематики. При работе с объектами появляются дополнительные ленты. Но их мы будем рассматривать позже.
  2. Панель быстрого доступа. Посмотрите уроки 18 и 19 рубрики «Тонкости Word» и разместите панель быстрого доступа под лентой. Я не буду предлагать вам установить мою панель потому, что у меня их несколько. В зависимости от смысла выполняемой работы, я оперативно добавляю и удаляю кнопки.
  3. Строка формул. В этой строчке будет отражено всё содержимое ячейки – текст, формулы.
  4. Имя выделенной ячейки. Состоит из имени столбца и имени строчки.
  5. Имя столбца. Может быть буквенным (28 столбец будет носить имя «АА» и т.д., так как в английском алфавите 27 букв), а может быть нумерованным. В некоторых случаях, особенно при написании макроса удобно нумерованное имя столбца.
  6. Имя строчки. Всегда только нумерованное. Делаем вывод, что имя ячейки состоит из имени столбца и имени строчки. Если вы знаете, какая именно ячейка вам нужна, в поле «Имя ячейки» наберите, например, «SX40», и ваш курсор немедленно отправиться в эту ячейку.
  7. Ярлык листа. Это имя листа, которое можно переименовать. Очень удобно, когда у вас несколько листов в работе, дать каждому листу значимое имя
  8. Кнопка добавления нового листа.
  9. Кнопки переключения между различными режимами отображения книги
  10. Панель масштаба. Нажмите на клавишу Ctrl и покрутите колесико мыши – и вы поймёте, почему я практически никогда не пользуюсь панелью масштаба

2. Командные ленты и их описание

Лента Главная (Ноте)

  1. Работа с буфером обмена (возможности просто поразительные)
  2. Выбор и изменение шрифтов
  3. Выравнивание содержимого ячеек
  4. Возможность объединения нескольких смежных ячеек в одну
  5. Определение формата как содержимого ячейки, так и внешнего вида самой ячейки
  6. Правка, сортировка и поиск содержимого ячейки.

Лента Вставка (Insert)

  1. Сводная таблица (возможность создания сводной таблица на базе одной или нескольких таблиц, согласно вашим критериям)
  2. Добавление различных изображений
  3. Добавление диаграмм различного вида
  4. Добавление спарклайнов (это такие маленькие диаграммочки, которые помещаются непосредственно в ячейке)
  5. Добавление гиперссылки (например, вы делаете ссылку на документ, который хранится на сервере или в облаке, и в дальнейшем открываете документ непосредственно при нажатии этой гиперссылки на листе книги)
  6. Добавление текста (колонтитулы, номера страниц и т.д.)
  7. Добавление формул и символов.

Команда символов бывает очень нужна. Например, знак умножить «×» или «±», поэтому седлайте следующее:

  1. Нажмите ПМ по команде «Символ»
  2. Из контекстного меню выберите команду «Добавить на панель быстрого доступа»

Очень подробная инструкция на в уроке 18. Так потихоньку будем набирать команды, а потом отсортируем.

Лента Разметка страницы (Page Layout)

  1. Настройка оформления таблицы. Выбор цветового оформления, шрифтов и эффектов оформления диаграмм, Smar-art’ов, рисунков. Я подробно говорила о выборе тем в статьях Секрет 3 и Урок 36
  2. Задание параметров страницы (поля, колонтитулы), области печати
  3. Задание параметров страницы
  4. Управление размером объектов
  5. Задание отображения сетки и заголовков. По умолчанию сетка видна, но она невидима. После оформления таблицы вы можете отключить видимость сетки и увидеть вашу таблицу в первозданной красоте. Тоже относится к заголовкам, по умолчанию мы видим заголовки с именами строчек и столбцов на листе. Бывают редкие случаи, когда надо вывести на печать заголовки с именами. В таких редких случаях мы отмечаем галочкой режим «Печать»под словом «Заголовки» и в на отпечатанном листе видим такую картину:

  1. Задание опций упорядочивания элементов на листе (почему команды работы с объектами находятся на ленте Разметкастраницы – для меня загадка)

Лента Формулы (Formulas)

  1. Вставка функции (здесь – функция, дальше – формула, но мы-то знаем, что это одно и тоже)
  2. Вставка формул, которые удобным образом размещены по категориям
  3. Задание определенного имени группе ячеек и возможность обработки именованных ячеек
  4. Проверка формул под разными углами и разными способами
  5. Проверка параметров вычисления.

Лента Данные (Data)

  1. Работа с базами данных. Содержит команды для получения внешних данных
  2. Управление внешними соединениями
  3. Возможность сортировки и фильтрации данных
  4. Распределения сложного текста в ячейке по столбцам (Excel 1), устранение дубликатов
  5. Проверка и консолидация данных
  6. Создание структурированной таблицы

Лента Рецензирование (Review)

  1. Проверка нашей грамотности
  2. Команды, необходимые для рецензирования, комментирования
  3. Защита листа или всей книги от несанкционированного редактирования

Лента Вид (View)

  1. Выбор различных представлений рабочей книги
  2. Скрытие или отображение элементов рабочего листа (сетки, линейки, строки формул и т.д.)
  3. Масштабирование (я практически не пользуюсь этой группой команд, мне достаточно Сtrl+колёсико мыши)
  4. Работа с несколькими окнами..

При добавлении на рабочий лист какого-либо объекта (например, диаграммы, рисунка и т.п.) появится Лент (Ribbon), , имеющая ряд вкладок, связанные с объектом панель инструментов.

Например, на рабочий лист добавили диаграмму, тогда на ленте появляется ленты Конструктор и Формат.

Лента Конструктор

  1. Макеты диаграмм
  2. Стили диаграмм
  3. Данные. Выбор данные для построения диаграмм
  4. Изменить тип диаграммы
  5. Размещение. Выбор места размещения диаграммы

Эти группы команд служат для:

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

3. Виды курсоров

Теперь вы сможете:

  1. Назвать элементы рабочего окна
  2. Ориентироваться в командных лентах Excel
  3. Перечислить виды курсоров.

Источник:
http://prooffice24.ru/introduction-to-excel/