도토리엑셀

엑셀에서 유효자리수 맞추기

=ROUND(value,sigfigs-(1+INT(LOG10(ABS(value)))))

value :: the number you wish to round.
sigfigs :: the number of significant figures you want to round to.

https://www.vertex42.com/ExcelTips/significant-figures.html

엑셀 빼기 연산 오류

엑셀2016버전을 사용하고 있는데 빼기 연산에 문제가 있다.

A1 : 106.9

A2 : 100

A3 : =A1-A2 를 각각 입력하였다.

A3셀의 수식에서 A1을 선택하고 F9, A2를 선택하고 F9를 누르면 위 그림과 같이 =106.9-100이 나온다.

이 값은 6.9가 나와야 정상인데..

=6.90000000000001

소수점 14자리에서 1이 뜬금없이 나타났다.

이는 부동소수점과 관련된 오류라고 한다.

모든 컴퓨터 기기에서 발생되는 증상으로 IEEE754를 이용하는 기기에서 나타나는 증상

근데 이건 좀 심하지..

Excel 사양 및 제한

열너비와 행높이를 같게 만드는 방법에 대해 알아보자.

엑셀에서 열너비와 행높이를 같은 숫자를 입력하면 정사각형이 되지 않는다.

엑셀 도움말에서 열 너비 및 행 높이 변경을 확인해보니

열너비는 최대 255point(2045pixel), 행높이는 최대409point(546pixel)까지 지정할 수 있다.

기본설정은 열너비 8.43point, 행높이 15.00point

◾페이지 레이아웃 보기(보기 탭, 통합 문서 보기 그룹, 페이지 레이아웃 단추)에서 작업하는 경우 열 너비 또는 행 높이를 인치, 센티미터 및 밀리미터 단위로 지정할 수 있습니다. 측정 단위는 기본적으로 인치 단위입니다. 파일 > 옵션 > 고급 > 디스플레이로 이동하여 > 눈금자 단위 목록에서 옵션을 선택합니다. 기본 보기로 전환하면 열 너비와 행 높이가 포인트로 표시됩니다.

1. 픽셀단위로 맞추는 방법

열너비를 조정하려고 마우스를 열사이에 놓고 드래그하면 너비:12.00(101 픽셀)이 나온다.

너비와 높이를 100픽셀로 조정하면

아래 그림과 같이 정사각형이된다.

2. 눈금자를 이용하는 방법

2-1. 파일 > 옵션 > 고급 > 디스플레이로 이동하여 > 눈금자 단위

mm단위가 편하니 mm로 변경한다.

2-2. 보기 > 통합문서보기 > 페이지 레이아웃 클릭

열너비와 행높이를 mm 단위로 맞출 수 있다.

위 2방법중 1번 방법을 추천한다.

2번 방법은 입력하고 나면 픽셀값이 입력한 값과 조금 달라지는 경향이 있다.

3. VBA를 이용하는 방법

Sub makeSquares()
Cells.RowHeight = 20
With Cells(1, 1)
W = .ColumnWidth
HWratio = .RowHeight / .Width
Cells.ColumnWidth = W * HWratio
End With
End Sub

행높이가 20으로 설정하고 열너비를 행높이와 같게 만든다.

수량의 합을 만족하는 해 찾기

데이터 \ 분석 \  해찾기를 통해 찾을 수 있다.

1.  파일 \ 옵션 \ 추가 기능 \ 해찾기 추가기능 추가

2. 해 찾기 매개 변수 설정

 구하고 셀을 지정한다. 목표 설정 $C$10

 대상은 지정값으로 구하고자 하는 값 10을 입력한다.

 $B$2:$B$7 영역의 값은 0 또는 1만 입력되도록 제한 조건을 설정한다.

3. 구하고자 하는 값 10이 되는 합은 다음과 같다.

A4, A5, A7을 더하면 10이 된다.

2022.02.03 - [엑셀] - Newton-Raphson method로 해 찾기

2022.01.04 - [엑셀] - 2차방정식의 해찾기

유효성검사 목록 정렬하기

1. 원본 데이터를 선택한 다음 수식 > 이름 정의 를 클릭

2. 그런 다음 새 이름 대화 상자에서 선택한 범위의 이름을 이름 텍스트 상자에 입력하고 이 수식을 입력.

 =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A)) 

3. C1셀을 선택하여 다음 수식을 입력합니다.

=IF(COUNTA(List)>=ROWS($A$1:A1), INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(A1)), COUNTIF(List, "<"&List), 0)), "")를 입력하고 Shift + Ctrl + Enter 키를 누른 다음 빈 셀이 나올 때까지 자동 채우기 핸들을 아래로 드래그 한다.

4.  수식 셀이 있는 열을 선택하고 수식 > 이름 정의를 클릭한 다음 이름 텍스트 상자에 SortedValues를 새 이름 대화 상자 에 입력합니다

5. 동적 드롭다운 목록을 만드는 데 필요한 셀 또는 범위를 선택하고 데이터 > 데이터 유효성 검사를 클릭

https://www.extendoffice.com/documents/excel/2652-excel-drop-down-list-alphabetical-order.html

SUM: 셀 범위의 값의 합 계산
AVERAGE: 셀 범위의 값의 평균 계산
COUNT: 셀 범위의 셀 수 계산
MIN: 셀 범위에서 가장 작은 값 계산
MAX: 셀 범위에서 가장 큰 값 계산
IF: 지정된 조건에 따라 두 값 중 하나 반환
VLOOKUP: 값이 주어진 범위에서 검색
INDEX: 배열 내의 값을 반환
MATCH: 배열 내에서 값의 위치를 검색
LEFT: 문자열의 왼쪽에서 지정된 길이의 문자 반환
RIGHT: 문자열의 오른쪽에서 지정된 길이의 문자 반환
MID: 문자열의 중간에서 지정된 길이의 문자 반환
CONCATENATE: 여러 문자열을 결합
DATE: 날짜를 반환
NOW: 현재 날짜와 시간을 반환
TODAY: 현재 날짜만 반환
TIME: 현재 시간만 반환
TEXT: 숫자를 문자열로 변환
ROUND: 숫자를 반올림하여 지정된 소수 자리에서 반올림
ABS
SQRT

Excel 수식을 사용하여 1에서 45 사이의 6개의 고유 난수를 생성하려면 셀 A1에서 다음 수식을 사용할 수 있습니다.

=RANDBETWEEN(1,45)

그런 다음 셀 A2에서 A7까지 다음 수식을 사용하여 나머지 숫자를 생성할 수 있습니다.

=IF(COUNTIF($A$1:A1,RANDBETWEEN(1,45))=0,RANDBETWEEN(1,45),"")

이 수식은 COUNTIF 함수를 사용하여 임의로 생성된 숫자가 이미 열 A에 있는지 확인하고 없으면 숫자를 반환합니다. 그렇다면 수식은 고유한 번호를 찾을 때까지 새 번호를 계속 생성합니다. 그런 다음 F9 키를 눌러 시트를 다시 계산하고 새로운 난수 집합을 생성할 수 있습니다.

새 숫자 세트를 생성하려면 F9를 눌러 시트를 다시 계산해야 합니다. 시트를 다시 계산할 때마다 숫자가 변경됩니다.

선택한 영역에서 0보다 큰 수 중 가장 작은 값 찾기

=MIN(IF(A1:A10>0,A1:A10))

배열 함수이므로 Ctrl+Shift+Enter

위의 수식은 A1에서 A10까지의 범위에서 0보다 큰 수만 고려하여 가장 작은 값을 찾습니다.

MIN 함수와 IF 함수의 조합을 사용할 때 #Value 오류가 발생하는 경우가 있습니다. 이는 MIN 함수가 빈 값을 처리하지 못하는 문제 때문입니다. 

이를 해결하기 위해서는 아래의 수식을 사용하세요:
=AGGREGATE(15,6,A1:A10/(A1:A10>0),1)

AGGREGATE 함수는 MIN 함수와 동일하게 작동하지만, 빈 값을 처리할 수 있습니다. 위의 수식은 A1에서 A10까지의 범위에서 0보다 큰 수만 고려하여 가장 작은 값을 찾습니다.

 AGGREGATE 함수는 숨겨진 행과 오류 값을 무시할 수 있는 옵션을 통해 목록이나 데이터베이스에 여러 가지 집계 함수를 적용할 수 있다.

엑셀에서 위 첨자 및 아래 첨자 입력하기

1. 셀서식(Ctrl + 1)

2. 삽입 > 기호 

3. 아래 첨자 및 위 첨자 만들어 주는 사이트

You can find subscript font files for various platforms and operating systems by searching online for "subscript font download". Some popular font websites that offer subscript fonts include Google Fonts, Font Squirrel, and Adobe Fonts. 

You can also download subscript fonts as part of font packages from many software companies and graphic design tools. Keep in mind that you'll need to install the font on your device before you can use it in your documents or designs.

도형 서식 네온

도형의 배경에 하이라이트 같은 기능을 넣고 싶으면 네온 기능을 이용하자.

도형 서식 > 네온