Уроки Excel Joomla! - Open Source Content Management Функции ВПР2 ГПР2 2013-12-10T18:38:06+04:00 2013-12-10T18:38:06+04:00 /index.php/uroki-excel/17-nadstrojka-ot-office-menu/52-funktsiya-vpr2 Super User andrey.morin88@gmail.com <div class="feed-description"><p>Является модифицированной версией стандартной функции ВПР Excel. Она разработана таким образом, чтобы позволять:</p> <ul> <li>Возвращать для ключа все его совпавшие значения в отличии от встроенной функции, которая возвращает только одно значение;</li> <li>Производить поиск по ключу вне зависимости от его расположения в таблице;</li> <li>Производить поиск по 2 и более условиям (составной ключ).</li> </ul> <p>В надстройке также имеется функция <strong>ГПР2</strong>, ее описание аналогично данному, но последняя осуществляет горизонтальный поиск.</p> <p><strong>Синтаксис:</strong> =<strong>ВПР2</strong>(Ключ; Таблица; Столбец_поиска; [Столбец_Ключа]), где:</p> <ul> <li>Ключ – обязательный аргумент. Значение либо ссылка на ячейку, содержащую значение для поиска. Когда необходимо определить несколько ключей, они должны быть записаны через символ амперсанда – &amp;;</li> <li>Таблица – обязательный аргумент. Диапазон, содержащий искомые значения и ключи;</li> <li>Столбец_поиска – обязательный аргумент. Целое число, указывающее на порядковый номер столбца в таблице, из которого необходимо возвратить значение;</li> <li>Столбец_Ключа – необязательный аргумент. Значение по умолчанию 1. Целое число, указывающее на порядковый номер столбца в таблице, в котором находится значение ключа. В случаях использования нескольких ключей, номера их столбцов записываются в виде массива (внутри фигурных скобок) через точку с запятой – {1; 3; 7}. Имейте в виду, что порядок указания столбцов должен совпадать с порядком задания ключей.</li> </ul> <p><strong>Пример использования:</strong></p> <p>На приведенном скриншоте функция <strong>ВПР2</strong> возвращает для ключей 1 и 4 по два значения, разделенных точкой с запятой. При включенном переносе текста в ячейках, каждое последующее значение начинается с новой строки.</p> <p><img style="border: 1px solid black;" title="Все значения ключа" src="images/addin/vlookup2.png" alt="Все значения ключа" /></p> <p>В следующей примере осуществляется поиск значений для составного ключа. Если значение не найдено, то возвращается ошибка #Н/Д.</p> <p><img style="border: 1px solid black;" title="Использование составного ключа" src="images/addin/vlookup2Ks.png" alt="Использование составного ключа" /></p> {loadmodule mod_custom, download_xlam}</div> <div class="feed-description"><p>Является модифицированной версией стандартной функции ВПР Excel. Она разработана таким образом, чтобы позволять:</p> <ul> <li>Возвращать для ключа все его совпавшие значения в отличии от встроенной функции, которая возвращает только одно значение;</li> <li>Производить поиск по ключу вне зависимости от его расположения в таблице;</li> <li>Производить поиск по 2 и более условиям (составной ключ).</li> </ul> <p>В надстройке также имеется функция <strong>ГПР2</strong>, ее описание аналогично данному, но последняя осуществляет горизонтальный поиск.</p> <p><strong>Синтаксис:</strong> =<strong>ВПР2</strong>(Ключ; Таблица; Столбец_поиска; [Столбец_Ключа]), где:</p> <ul> <li>Ключ – обязательный аргумент. Значение либо ссылка на ячейку, содержащую значение для поиска. Когда необходимо определить несколько ключей, они должны быть записаны через символ амперсанда – &amp;;</li> <li>Таблица – обязательный аргумент. Диапазон, содержащий искомые значения и ключи;</li> <li>Столбец_поиска – обязательный аргумент. Целое число, указывающее на порядковый номер столбца в таблице, из которого необходимо возвратить значение;</li> <li>Столбец_Ключа – необязательный аргумент. Значение по умолчанию 1. Целое число, указывающее на порядковый номер столбца в таблице, в котором находится значение ключа. В случаях использования нескольких ключей, номера их столбцов записываются в виде массива (внутри фигурных скобок) через точку с запятой – {1; 3; 7}. Имейте в виду, что порядок указания столбцов должен совпадать с порядком задания ключей.</li> </ul> <p><strong>Пример использования:</strong></p> <p>На приведенном скриншоте функция <strong>ВПР2</strong> возвращает для ключей 1 и 4 по два значения, разделенных точкой с запятой. При включенном переносе текста в ячейках, каждое последующее значение начинается с новой строки.</p> <p><img style="border: 1px solid black;" title="Все значения ключа" src="images/addin/vlookup2.png" alt="Все значения ключа" /></p> <p>В следующей примере осуществляется поиск значений для составного ключа. Если значение не найдено, то возвращается ошибка #Н/Д.</p> <p><img style="border: 1px solid black;" title="Использование составного ключа" src="images/addin/vlookup2Ks.png" alt="Использование составного ключа" /></p> {loadmodule mod_custom, download_xlam}</div> Функции МАКСЕСЛИ МИНЕСЛИ 2013-12-11T18:01:38+04:00 2013-12-11T18:01:38+04:00 /index.php/uroki-excel/17-nadstrojka-ot-office-menu/53-funktsii-maksesli-minesli Super User andrey.morin88@gmail.com <div class="feed-description"></div> <div class="feed-description"></div> Функция ЗнаковПослеЗапятой 2014-09-06T11:03:03+04:00 2014-09-06T11:03:03+04:00 /index.php/uroki-excel/17-nadstrojka-ot-office-menu/73-funktsiya-znakovposlezapyatoj Super User andrey.morin88@gmail.com <div class="feed-description"></div> <div class="feed-description"></div> Функция ЧислоПрописью 2014-09-06T12:25:37+04:00 2014-09-06T12:25:37+04:00 /index.php/uroki-excel/17-nadstrojka-ot-office-menu/74-funktsiya-chislopropisyu Super User andrey.morin88@gmail.com <div class="feed-description"><p>Переводить число в текст прописью.<br /> Максимальный разряд – квадриллион, минимальный разряд - миллионные доли. В случае превышения максимального разряда, функция вернет ошибку. Максимальное количество знаков после запятой, которое может быть обработано, не превышает 6-ти, все остальные знаки округляются по принципу минимальной разницы по модулю.</p> <p><strong>Синтаксис:</strong><br /><br /> =<strong>ЧислоПрописью</strong>(Число), где</p> <ul> <li>Число – обязательный аргумент. Число или ссылка на число, которое необходимо перевести в текст.</li> </ul> <p>Пример использования:</p> <ul> <li>=ЧислоПрописью(12345,678901) – функция вернет строку «двенадцать тысяч триста сорок пять целых шестьсот семьдесят восемь тысяч девятьсот одна миллионная»;</li> <li>=ЧислоПрописью(2014) – результатом будет строка «две тысячи четырнадцать».</li></ul> {loadmodule mod_custom, download_xlam}</div> <div class="feed-description"><p>Переводить число в текст прописью.<br /> Максимальный разряд – квадриллион, минимальный разряд - миллионные доли. В случае превышения максимального разряда, функция вернет ошибку. Максимальное количество знаков после запятой, которое может быть обработано, не превышает 6-ти, все остальные знаки округляются по принципу минимальной разницы по модулю.</p> <p><strong>Синтаксис:</strong><br /><br /> =<strong>ЧислоПрописью</strong>(Число), где</p> <ul> <li>Число – обязательный аргумент. Число или ссылка на число, которое необходимо перевести в текст.</li> </ul> <p>Пример использования:</p> <ul> <li>=ЧислоПрописью(12345,678901) – функция вернет строку «двенадцать тысяч триста сорок пять целых шестьсот семьдесят восемь тысяч девятьсот одна миллионная»;</li> <li>=ЧислоПрописью(2014) – результатом будет строка «две тысячи четырнадцать».</li></ul> {loadmodule mod_custom, download_xlam}</div> Функции ТекстСуммы ТекстРазницы ТекстУмножения ТекстДеления 2014-09-06T19:41:04+04:00 2014-09-06T19:41:04+04:00 /index.php/uroki-excel/17-nadstrojka-ot-office-menu/75-funktsii-tekstsummy-tekstraznitsy-tekstumnozheniya-tekstdeleniya Super User andrey.morin88@gmail.com <div class="feed-description"><p>Данные математические функции предоставляют возможность произвести вычисления и вывести результат вместе с числами, участвующими в арифметическом действии, и знаком математической операции (см. примеры ниже).</p> <p>Синтаксис функций ТекстСуммы, ТекстРазницы, ТекстУмножения и ТекстДеления ничем не отличается, поэтому рассмотрим синтаксис только одной из них.</p> <p><strong>Синтаксис:</strong><br /><br /> =<strong>ТекстСуммы</strong>(Число1; Число2; [Скобки]), где</p> <ul> <li>Число1 – обязательный аргумент. Число, выступающее в качестве первого слагаемого;</li> <li>Число2 – обязательный аргумент. Число, выступающее в качестве второго слагаемого;</li> <li>Скобки – необязательный аргумент. Логическое значение, указывающее: <ul> <li>ИСТИНА – заключить выражение в скобки;</li> <li>ЛОЖЬ – не заключать выражение в скобки (значение по умолчанию).</li> </ul></li></ul> <p>В качестве первого и второго аргумента может быть использована любая из функций: ТекстСуммы, ТекстРазницы, ТекстУмножения или ТекстДеления. Такая возможность позволяет выводить выражения, состоящие более чем из 2-x операций.</p> <p>Примеры использования:</p> <ul> <li>=ТекстСуммы(2;5) – выведет строку «2+5=7»;</li> <li>=ТекстСуммы(ТекстСуммы(2;5);11) – применение вложенной функций создает следующую строку «2+5+11=18»;</li> <li>=ТекстУмножения(ТекстСуммы(2;5;ИСТИНА);11) – пример использования скобок, для верного отображения порядка вычислений. Результатом формулы будет «(2+5)•11=77».</li> </ul> {loadmodule mod_custom, download_xlam}</div> <div class="feed-description"><p>Данные математические функции предоставляют возможность произвести вычисления и вывести результат вместе с числами, участвующими в арифметическом действии, и знаком математической операции (см. примеры ниже).</p> <p>Синтаксис функций ТекстСуммы, ТекстРазницы, ТекстУмножения и ТекстДеления ничем не отличается, поэтому рассмотрим синтаксис только одной из них.</p> <p><strong>Синтаксис:</strong><br /><br /> =<strong>ТекстСуммы</strong>(Число1; Число2; [Скобки]), где</p> <ul> <li>Число1 – обязательный аргумент. Число, выступающее в качестве первого слагаемого;</li> <li>Число2 – обязательный аргумент. Число, выступающее в качестве второго слагаемого;</li> <li>Скобки – необязательный аргумент. Логическое значение, указывающее: <ul> <li>ИСТИНА – заключить выражение в скобки;</li> <li>ЛОЖЬ – не заключать выражение в скобки (значение по умолчанию).</li> </ul></li></ul> <p>В качестве первого и второго аргумента может быть использована любая из функций: ТекстСуммы, ТекстРазницы, ТекстУмножения или ТекстДеления. Такая возможность позволяет выводить выражения, состоящие более чем из 2-x операций.</p> <p>Примеры использования:</p> <ul> <li>=ТекстСуммы(2;5) – выведет строку «2+5=7»;</li> <li>=ТекстСуммы(ТекстСуммы(2;5);11) – применение вложенной функций создает следующую строку «2+5+11=18»;</li> <li>=ТекстУмножения(ТекстСуммы(2;5;ИСТИНА);11) – пример использования скобок, для верного отображения порядка вычислений. Результатом формулы будет «(2+5)•11=77».</li> </ul> {loadmodule mod_custom, download_xlam}</div> Функция ПЕРТЕКСТ 2014-10-02T09:56:11+04:00 2014-10-02T09:56:11+04:00 /index.php/uroki-excel/17-nadstrojka-ot-office-menu/76-funktsiya-pertekst Super User andrey.morin88@gmail.com <div class="feed-description"><p>Данная функция надстройки для Excel переворачивает текст, переданный ей в качестве аргумента.</p> <p><strong>Синтаксис</strong>:<br /><br /> =<strong>ПЕРТЕКСТ</strong>(Текст), где</p> <ul><li>Текст – обязательный аргумент. Строка или ссылка на ячейку с текстом, которые необходимо перевернуть.</li></ul> <p>Пример использования:<br /><br /> =ПЕРТЕКСТ("Привет") – функция вернет перевернутую строку «тевирП».</p> {loadmodule mod_custom, download_xlam}</div> <div class="feed-description"><p>Данная функция надстройки для Excel переворачивает текст, переданный ей в качестве аргумента.</p> <p><strong>Синтаксис</strong>:<br /><br /> =<strong>ПЕРТЕКСТ</strong>(Текст), где</p> <ul><li>Текст – обязательный аргумент. Строка или ссылка на ячейку с текстом, которые необходимо перевернуть.</li></ul> <p>Пример использования:<br /><br /> =ПЕРТЕКСТ("Привет") – функция вернет перевернутую строку «тевирП».</p> {loadmodule mod_custom, download_xlam}</div> Объединение ячеек Excel без потери данных 2014-10-02T16:59:44+04:00 2014-10-02T16:59:44+04:00 /index.php/uroki-excel/17-nadstrojka-ot-office-menu/77-ob-edinenie-yacheek-excel-bez-poteri-dannykh Super User andrey.morin88@gmail.com <div class="feed-description"><p>У стандартного объединения в Excel имеется недостаток, который заметен только при объединении нескольких ячеек с данными, когда сохраняется значение только верхней левой ячейки выделенного диапазона, а остальные затираются. Но часто возникает необходимость сохранить все данные во время объединения.</p> <p>Настройка office-menu включает в себя возможность объединения ячеек без потери данных с возможностью их разделения специальными символами.</p> <p>Установив надстройку (ссылка для ее скачивания расположена в конце статьи), на ленте появится новая вкладка «office-menu», на которой располагаются кнопки объединения без потерь и 2 поля для указания разделителей.</p> <img src="images/addin/mergeGroupOF.png" alt="Вид группы кнопок объединения без потерь на ленте" title="Вид группы кнопок объединения без потерь на ленте" /> <h2>Кнопка «Объединить без потерь»</h2> <p>Данная кнопка предназначена для объединения всех ячеек внутри выделенного диапазона. Объединяя их, надстройка применяет символы-разделители, которые Вы можете задать самостоятельно (подробнее ниже в этой статье). По умолчанию для разделения значений, расположенных на одной строке листа Excel, используются квадратных скобки, а для разделения разных строк применяется символ переноса строки. Для лучшего понимания на рисунке показан пример:</p> <img src="images/addin/mergeDef.png" alt="Объединение без потерь по умолчанию" title="Объединение без потерь по умолчанию" /> <h2>Кнопка «Объединить строки»</h2> <p>В отличие от кнопки, описанной выше, объединение строк создает внутри выделенного диапазона ячеек несколько объединенных областей, разбитых по строкам. В качестве разделителя используется только разделитель значений. Пример:</p> <img src="images/addin/mergeRow.png" alt="Объединение без потери по строкам" title="Объединение без потери по строкам" /> <h2>Кнопка «Объединить столбцы»</h2> <p>Действие этой кнопки создает объединенные области в диапазоне, разделенные по столбцам. Для разделителя используется только разделитель строк. Пример:</p> <img src="images/addin/mergeCol.png" alt="Объединение без потери по столбцам" title="Объединение без потери по столбцам" /> <h2>Кнопка «Разбить по шаблону»</h2> <p>Программа, выполняемая данной кнопкой, производит обратные объединению без потерь действия, т.е. отменяет объединение ячеек, разделяя строку по указанным разделителям значений и строк и размещает полученный массив данных в соответствующие ячейки.</p> <h2>Разделители значений и строк</h2> <p>Как уже было сказано выше, по умолчанию для разделения значений в строке используются квадратные скобки, а для разделения строк – символ переноса строки. Но Вы можете самостоятельно указать разделители, которыми могут являться одиночные символы или их набор.</p> <p>Для того, чтобы расположить символы-разделители в конце значений и строк, впишите их в соответствующие поля на ленте. В ситуациях, когда Вы хотите окружить значение или строку начальным или конечным разделителями, Вам потребуется записать их через символ тильда (~).</p> <p>Например, объединим ячейки таким образом, чтобы их значения были окружены html-тегами ячейки таблицы (&lt;td&gt; и &lt;/td&gt;), а строки html-тегами строки таблицы (&lt;tr&gt; и &lt;/tr&gt;). Для этого запишем в поле «Разделитель значений:» строку «&lt;td&gt;~&lt;/td&gt;», а в поле «Разделитель строк:» – «&lt;tr&gt;~&lt;/tr&gt;». После объединения без потерь в области появится такой текст:</p> <pre class="brush:xml">&lt;tr&gt;&lt;td&gt;Знач.1&lt;/td&gt;&lt;td&gt;Знач.2&lt;/td&gt;&lt;td&gt;Знач.3&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td&gt;Знач.4&lt;/td&gt;&lt;td&gt;Знач.5&lt;/td&gt;&lt;td&gt;Знач.6&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td&gt;Знач.7&lt;/td&gt;&lt;td&gt;Знач.8&lt;/td&gt;&lt;td&gt;Знач.9&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td&gt;Знач.10&lt;/td&gt;&lt;td&gt;Знач.11&lt;/td&gt;&lt;td&gt;Знач.12&lt;/td&gt;&lt;/tr&gt;</pre> <p>Для указания разделителем символа тильда запишите его 2 раза подряд (~~).</p> {loadmodule mod_custom, download_xlam}</div> <div class="feed-description"><p>У стандартного объединения в Excel имеется недостаток, который заметен только при объединении нескольких ячеек с данными, когда сохраняется значение только верхней левой ячейки выделенного диапазона, а остальные затираются. Но часто возникает необходимость сохранить все данные во время объединения.</p> <p>Настройка office-menu включает в себя возможность объединения ячеек без потери данных с возможностью их разделения специальными символами.</p> <p>Установив надстройку (ссылка для ее скачивания расположена в конце статьи), на ленте появится новая вкладка «office-menu», на которой располагаются кнопки объединения без потерь и 2 поля для указания разделителей.</p> <img src="images/addin/mergeGroupOF.png" alt="Вид группы кнопок объединения без потерь на ленте" title="Вид группы кнопок объединения без потерь на ленте" /> <h2>Кнопка «Объединить без потерь»</h2> <p>Данная кнопка предназначена для объединения всех ячеек внутри выделенного диапазона. Объединяя их, надстройка применяет символы-разделители, которые Вы можете задать самостоятельно (подробнее ниже в этой статье). По умолчанию для разделения значений, расположенных на одной строке листа Excel, используются квадратных скобки, а для разделения разных строк применяется символ переноса строки. Для лучшего понимания на рисунке показан пример:</p> <img src="images/addin/mergeDef.png" alt="Объединение без потерь по умолчанию" title="Объединение без потерь по умолчанию" /> <h2>Кнопка «Объединить строки»</h2> <p>В отличие от кнопки, описанной выше, объединение строк создает внутри выделенного диапазона ячеек несколько объединенных областей, разбитых по строкам. В качестве разделителя используется только разделитель значений. Пример:</p> <img src="images/addin/mergeRow.png" alt="Объединение без потери по строкам" title="Объединение без потери по строкам" /> <h2>Кнопка «Объединить столбцы»</h2> <p>Действие этой кнопки создает объединенные области в диапазоне, разделенные по столбцам. Для разделителя используется только разделитель строк. Пример:</p> <img src="images/addin/mergeCol.png" alt="Объединение без потери по столбцам" title="Объединение без потери по столбцам" /> <h2>Кнопка «Разбить по шаблону»</h2> <p>Программа, выполняемая данной кнопкой, производит обратные объединению без потерь действия, т.е. отменяет объединение ячеек, разделяя строку по указанным разделителям значений и строк и размещает полученный массив данных в соответствующие ячейки.</p> <h2>Разделители значений и строк</h2> <p>Как уже было сказано выше, по умолчанию для разделения значений в строке используются квадратные скобки, а для разделения строк – символ переноса строки. Но Вы можете самостоятельно указать разделители, которыми могут являться одиночные символы или их набор.</p> <p>Для того, чтобы расположить символы-разделители в конце значений и строк, впишите их в соответствующие поля на ленте. В ситуациях, когда Вы хотите окружить значение или строку начальным или конечным разделителями, Вам потребуется записать их через символ тильда (~).</p> <p>Например, объединим ячейки таким образом, чтобы их значения были окружены html-тегами ячейки таблицы (&lt;td&gt; и &lt;/td&gt;), а строки html-тегами строки таблицы (&lt;tr&gt; и &lt;/tr&gt;). Для этого запишем в поле «Разделитель значений:» строку «&lt;td&gt;~&lt;/td&gt;», а в поле «Разделитель строк:» – «&lt;tr&gt;~&lt;/tr&gt;». После объединения без потерь в области появится такой текст:</p> <pre class="brush:xml">&lt;tr&gt;&lt;td&gt;Знач.1&lt;/td&gt;&lt;td&gt;Знач.2&lt;/td&gt;&lt;td&gt;Знач.3&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td&gt;Знач.4&lt;/td&gt;&lt;td&gt;Знач.5&lt;/td&gt;&lt;td&gt;Знач.6&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td&gt;Знач.7&lt;/td&gt;&lt;td&gt;Знач.8&lt;/td&gt;&lt;td&gt;Знач.9&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td&gt;Знач.10&lt;/td&gt;&lt;td&gt;Знач.11&lt;/td&gt;&lt;td&gt;Знач.12&lt;/td&gt;&lt;/tr&gt;</pre> <p>Для указания разделителем символа тильда запишите его 2 раза подряд (~~).</p> {loadmodule mod_custom, download_xlam}</div>